PostgreSQL Source Code  git master
timestamp.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * timestamp.c
4  * Functions for the built-in SQL types "timestamp" and "interval".
5  *
6  * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
7  * Portions Copyright (c) 1994, Regents of the University of California
8  *
9  *
10  * IDENTIFICATION
11  * src/backend/utils/adt/timestamp.c
12  *
13  *-------------------------------------------------------------------------
14  */
15 
16 #include "postgres.h"
17 
18 #include <ctype.h>
19 #include <math.h>
20 #include <limits.h>
21 #include <sys/time.h>
22 
23 #include "access/xact.h"
24 #include "catalog/pg_type.h"
25 #include "common/int.h"
26 #include "common/int128.h"
27 #include "funcapi.h"
28 #include "libpq/pqformat.h"
29 #include "miscadmin.h"
30 #include "nodes/makefuncs.h"
31 #include "nodes/nodeFuncs.h"
32 #include "nodes/supportnodes.h"
33 #include "parser/scansup.h"
34 #include "utils/array.h"
35 #include "utils/builtins.h"
36 #include "utils/date.h"
37 #include "utils/datetime.h"
38 #include "utils/float.h"
39 #include "utils/numeric.h"
40 
41 /*
42  * gcc's -ffast-math switch breaks routines that expect exact results from
43  * expressions like timeval / SECS_PER_HOUR, where timeval is double.
44  */
45 #ifdef __FAST_MATH__
46 #error -ffast-math is known to break this code
47 #endif
48 
49 #define SAMESIGN(a,b) (((a) < 0) == ((b) < 0))
50 
51 /* Set at postmaster start */
52 TimestampTz PgStartTime;
53 
54 /* Set at configuration reload */
55 TimestampTz PgReloadTime;
56 
57 typedef struct
58 {
59  Timestamp current;
60  Timestamp finish;
61  Interval step;
62  int step_sign;
63 } generate_series_timestamp_fctx;
64 
65 typedef struct
66 {
67  TimestampTz current;
68  TimestampTz finish;
69  Interval step;
70  int step_sign;
71 } generate_series_timestamptz_fctx;
72 
73 
74 static TimeOffset time2t(const int hour, const int min, const int sec, const fsec_t fsec);
75 static Timestamp dt2local(Timestamp dt, int timezone);
76 static void AdjustIntervalForTypmod(Interval *interval, int32 typmod);
77 static TimestampTz timestamp2timestamptz(Timestamp timestamp);
78 static Timestamp timestamptz2timestamp(TimestampTz timestamp);
79 
80 
81 /* common code for timestamptypmodin and timestamptztypmodin */
82 static int32
83 anytimestamp_typmodin(bool istz, ArrayType *ta)
84 {
85  int32 *tl;
86  int n;
87 
88  tl = ArrayGetIntegerTypmods(ta, &n);
89 
90  /*
91  * we're not too tense about good error message here because grammar
92  * shouldn't allow wrong number of modifiers for TIMESTAMP
93  */
94  if (n != 1)
95  ereport(ERROR,
96  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
97  errmsg("invalid type modifier")));
98 
99  return anytimestamp_typmod_check(istz, tl[0]);
100 }
101 
102 /* exported so parse_expr.c can use it */
103 int32
104 anytimestamp_typmod_check(bool istz, int32 typmod)
105 {
106  if (typmod < 0)
107  ereport(ERROR,
108  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
109  errmsg("TIMESTAMP(%d)%s precision must not be negative",
110  typmod, (istz ? " WITH TIME ZONE" : ""))));
111  if (typmod > MAX_TIMESTAMP_PRECISION)
112  {
113  ereport(WARNING,
114  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
115  errmsg("TIMESTAMP(%d)%s precision reduced to maximum allowed, %d",
116  typmod, (istz ? " WITH TIME ZONE" : ""),
117  MAX_TIMESTAMP_PRECISION)));
118  typmod = MAX_TIMESTAMP_PRECISION;
119  }
120 
121  return typmod;
122 }
123 
124 /* common code for timestamptypmodout and timestamptztypmodout */
125 static char *
126 anytimestamp_typmodout(bool istz, int32 typmod)
127 {
128  const char *tz = istz ? " with time zone" : " without time zone";
129 
130  if (typmod >= 0)
131  return psprintf("(%d)%s", (int) typmod, tz);
132  else
133  return psprintf("%s", tz);
134 }
135 
136 
137 /*****************************************************************************
138  * USER I/O ROUTINES *
139  *****************************************************************************/
140 
141 /* timestamp_in()
142  * Convert a string to internal form.
143  */
144 Datum
145 timestamp_in(PG_FUNCTION_ARGS)
146 {
147  char *str = PG_GETARG_CSTRING(0);
148 
149 #ifdef NOT_USED
150  Oid typelem = PG_GETARG_OID(1);
151 #endif
152  int32 typmod = PG_GETARG_INT32(2);
153  Timestamp result;
154  fsec_t fsec;
155  struct pg_tm tt,
156  *tm = &tt;
157  int tz;
158  int dtype;
159  int nf;
160  int dterr;
161  char *field[MAXDATEFIELDS];
162  int ftype[MAXDATEFIELDS];
163  char workbuf[MAXDATELEN + MAXDATEFIELDS];
164 
165  dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
166  field, ftype, MAXDATEFIELDS, &nf);
167  if (dterr == 0)
168  dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
169  if (dterr != 0)
170  DateTimeParseError(dterr, str, "timestamp");
171 
172  switch (dtype)
173  {
174  case DTK_DATE:
175  if (tm2timestamp(tm, fsec, NULL, &result) != 0)
176  ereport(ERROR,
177  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
178  errmsg("timestamp out of range: \"%s\"", str)));
179  break;
180 
181  case DTK_EPOCH:
182  result = SetEpochTimestamp();
183  break;
184 
185  case DTK_LATE:
186  TIMESTAMP_NOEND(result);
187  break;
188 
189  case DTK_EARLY:
190  TIMESTAMP_NOBEGIN(result);
191  break;
192 
193  default:
194  elog(ERROR, "unexpected dtype %d while parsing timestamp \"%s\"",
195  dtype, str);
196  TIMESTAMP_NOEND(result);
197  }
198 
199  AdjustTimestampForTypmod(&result, typmod);
200 
201  PG_RETURN_TIMESTAMP(result);
202 }
203 
204 /* timestamp_out()
205  * Convert a timestamp to external form.
206  */
207 Datum
208 timestamp_out(PG_FUNCTION_ARGS)
209 {
210  Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
211  char *result;
212  struct pg_tm tt,
213  *tm = &tt;
214  fsec_t fsec;
215  char buf[MAXDATELEN + 1];
216 
217  if (TIMESTAMP_NOT_FINITE(timestamp))
218  EncodeSpecialTimestamp(timestamp, buf);
219  else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
220  EncodeDateTime(tm, fsec, false, 0, NULL, DateStyle, buf);
221  else
222  ereport(ERROR,
223  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
224  errmsg("timestamp out of range")));
225 
226  result = pstrdup(buf);
227  PG_RETURN_CSTRING(result);
228 }
229 
230 /*
231  * timestamp_recv - converts external binary format to timestamp
232  */
233 Datum
234 timestamp_recv(PG_FUNCTION_ARGS)
235 {
236  StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
237 
238 #ifdef NOT_USED
239  Oid typelem = PG_GETARG_OID(1);
240 #endif
241  int32 typmod = PG_GETARG_INT32(2);
242  Timestamp timestamp;
243  struct pg_tm tt,
244  *tm = &tt;
245  fsec_t fsec;
246 
247  timestamp = (Timestamp) pq_getmsgint64(buf);
248 
249  /* range check: see if timestamp_out would like it */
250  if (TIMESTAMP_NOT_FINITE(timestamp))
251  /* ok */ ;
252  else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0 ||
253  !IS_VALID_TIMESTAMP(timestamp))
254  ereport(ERROR,
255  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
256  errmsg("timestamp out of range")));
257 
258  AdjustTimestampForTypmod(&timestamp, typmod);
259 
260  PG_RETURN_TIMESTAMP(timestamp);
261 }
262 
263 /*
264  * timestamp_send - converts timestamp to binary format
265  */
266 Datum
267 timestamp_send(PG_FUNCTION_ARGS)
268 {
269  Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
270  StringInfoData buf;
271 
272  pq_begintypsend(&buf);
273  pq_sendint64(&buf, timestamp);
274  PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
275 }
276 
277 Datum
278 timestamptypmodin(PG_FUNCTION_ARGS)
279 {
280  ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
281 
282  PG_RETURN_INT32(anytimestamp_typmodin(false, ta));
283 }
284 
285 Datum
286 timestamptypmodout(PG_FUNCTION_ARGS)
287 {
288  int32 typmod = PG_GETARG_INT32(0);
289 
290  PG_RETURN_CSTRING(anytimestamp_typmodout(false, typmod));
291 }
292 
293 
294 /*
295  * timestamp_support()
296  *
297  * Planner support function for the timestamp_scale() and timestamptz_scale()
298  * length coercion functions (we need not distinguish them here).
299  */
300 Datum
301 timestamp_support(PG_FUNCTION_ARGS)
302 {
303  Node *rawreq = (Node *) PG_GETARG_POINTER(0);
304  Node *ret = NULL;
305 
306  if (IsA(rawreq, SupportRequestSimplify))
307  {
308  SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
309 
310  ret = TemporalSimplify(MAX_TIMESTAMP_PRECISION, (Node *) req->fcall);
311  }
312 
313  PG_RETURN_POINTER(ret);
314 }
315 
316 /* timestamp_scale()
317  * Adjust time type for specified scale factor.
318  * Used by PostgreSQL type system to stuff columns.
319  */
320 Datum
321 timestamp_scale(PG_FUNCTION_ARGS)
322 {
323  Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
324  int32 typmod = PG_GETARG_INT32(1);
325  Timestamp result;
326 
327  result = timestamp;
328 
329  AdjustTimestampForTypmod(&result, typmod);
330 
331  PG_RETURN_TIMESTAMP(result);
332 }
333 
334 /*
335  * AdjustTimestampForTypmodError --- round off a timestamp to suit given typmod
336  * Works for either timestamp or timestamptz.
337  */
338 bool
339 AdjustTimestampForTypmodError(Timestamp *time, int32 typmod, bool *error)
340 {
341  static const int64 TimestampScales[MAX_TIMESTAMP_PRECISION + 1] = {
342  INT64CONST(1000000),
343  INT64CONST(100000),
344  INT64CONST(10000),
345  INT64CONST(1000),
346  INT64CONST(100),
347  INT64CONST(10),
348  INT64CONST(1)
349  };
350 
351  static const int64 TimestampOffsets[MAX_TIMESTAMP_PRECISION + 1] = {
352  INT64CONST(500000),
353  INT64CONST(50000),
354  INT64CONST(5000),
355  INT64CONST(500),
356  INT64CONST(50),
357  INT64CONST(5),
358  INT64CONST(0)
359  };
360 
361  if (!TIMESTAMP_NOT_FINITE(*time)
362  && (typmod != -1) && (typmod != MAX_TIMESTAMP_PRECISION))
363  {
364  if (typmod < 0 || typmod > MAX_TIMESTAMP_PRECISION)
365  {
366  if (error)
367  {
368  *error = true;
369  return false;
370  }
371 
372  ereport(ERROR,
373  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
374  errmsg("timestamp(%d) precision must be between %d and %d",
375  typmod, 0, MAX_TIMESTAMP_PRECISION)));
376  }
377 
378  if (*time >= INT64CONST(0))
379  {
380  *time = ((*time + TimestampOffsets[typmod]) / TimestampScales[typmod]) *
381  TimestampScales[typmod];
382  }
383  else
384  {
385  *time = -((((-*time) + TimestampOffsets[typmod]) / TimestampScales[typmod])
386  * TimestampScales[typmod]);
387  }
388  }
389 
390  return true;
391 }
392 
393 void
394 AdjustTimestampForTypmod(Timestamp *time, int32 typmod)
395 {
396  (void) AdjustTimestampForTypmodError(time, typmod, NULL);
397 }
398 
399 /* timestamptz_in()
400  * Convert a string to internal form.
401  */
402 Datum
403 timestamptz_in(PG_FUNCTION_ARGS)
404 {
405  char *str = PG_GETARG_CSTRING(0);
406 
407 #ifdef NOT_USED
408  Oid typelem = PG_GETARG_OID(1);
409 #endif
410  int32 typmod = PG_GETARG_INT32(2);
411  TimestampTz result;
412  fsec_t fsec;
413  struct pg_tm tt,
414  *tm = &tt;
415  int tz;
416  int dtype;
417  int nf;
418  int dterr;
419  char *field[MAXDATEFIELDS];
420  int ftype[MAXDATEFIELDS];
421  char workbuf[MAXDATELEN + MAXDATEFIELDS];
422 
423  dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
424  field, ftype, MAXDATEFIELDS, &nf);
425  if (dterr == 0)
426  dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
427  if (dterr != 0)
428  DateTimeParseError(dterr, str, "timestamp with time zone");
429 
430  switch (dtype)
431  {
432  case DTK_DATE:
433  if (tm2timestamp(tm, fsec, &tz, &result) != 0)
434  ereport(ERROR,
435  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
436  errmsg("timestamp out of range: \"%s\"", str)));
437  break;
438 
439  case DTK_EPOCH:
440  result = SetEpochTimestamp();
441  break;
442 
443  case DTK_LATE:
444  TIMESTAMP_NOEND(result);
445  break;
446 
447  case DTK_EARLY:
448  TIMESTAMP_NOBEGIN(result);
449  break;
450 
451  default:
452  elog(ERROR, "unexpected dtype %d while parsing timestamptz \"%s\"",
453  dtype, str);
454  TIMESTAMP_NOEND(result);
455  }
456 
457  AdjustTimestampForTypmod(&result, typmod);
458 
459  PG_RETURN_TIMESTAMPTZ(result);
460 }
461 
462 /*
463  * Try to parse a timezone specification, and return its timezone offset value
464  * if it's acceptable. Otherwise, an error is thrown.
465  *
466  * Note: some code paths update tm->tm_isdst, and some don't; current callers
467  * don't care, so we don't bother being consistent.
468  */
469 static int
470 parse_sane_timezone(struct pg_tm *tm, text *zone)
471 {
472  char tzname[TZ_STRLEN_MAX + 1];
473  int rt;
474  int tz;
475 
476  text_to_cstring_buffer(zone, tzname, sizeof(tzname));
477 
478  /*
479  * Look up the requested timezone. First we try to interpret it as a
480  * numeric timezone specification; if DecodeTimezone decides it doesn't
481  * like the format, we look in the timezone abbreviation table (to handle
482  * cases like "EST"), and if that also fails, we look in the timezone
483  * database (to handle cases like "America/New_York"). (This matches the
484  * order in which timestamp input checks the cases; it's important because
485  * the timezone database unwisely uses a few zone names that are identical
486  * to offset abbreviations.)
487  *
488  * Note pg_tzset happily parses numeric input that DecodeTimezone would
489  * reject. To avoid having it accept input that would otherwise be seen
490  * as invalid, it's enough to disallow having a digit in the first
491  * position of our input string.
492  */
493  if (isdigit((unsigned char) *tzname))
494  ereport(ERROR,
495  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
496  errmsg("invalid input syntax for type %s: \"%s\"",
497  "numeric time zone", tzname),
498  errhint("Numeric time zones must have \"-\" or \"+\" as first character.")));
499 
500  rt = DecodeTimezone(tzname, &tz);
501  if (rt != 0)
502  {
503  char *lowzone;
504  int type,
505  val;
506  pg_tz *tzp;
507 
508  if (rt == DTERR_TZDISP_OVERFLOW)
509  ereport(ERROR,
510  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
511  errmsg("numeric time zone \"%s\" out of range", tzname)));
512  else if (rt != DTERR_BAD_FORMAT)
513  ereport(ERROR,
514  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
515  errmsg("time zone \"%s\" not recognized", tzname)));
516 
517  /* DecodeTimezoneAbbrev requires lowercase input */
518  lowzone = downcase_truncate_identifier(tzname,
519  strlen(tzname),
520  false);
521  type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
522 
523  if (type == TZ || type == DTZ)
524  {
525  /* fixed-offset abbreviation */
526  tz = -val;
527  }
528  else if (type == DYNTZ)
529  {
530  /* dynamic-offset abbreviation, resolve using specified time */
531  tz = DetermineTimeZoneAbbrevOffset(tm, tzname, tzp);
532  }
533  else
534  {
535  /* try it as a full zone name */
536  tzp = pg_tzset(tzname);
537  if (tzp)
538  tz = DetermineTimeZoneOffset(tm, tzp);
539  else
540  ereport(ERROR,
541  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
542  errmsg("time zone \"%s\" not recognized", tzname)));
543  }
544  }
545 
546  return tz;
547 }
548 
549 /*
550  * make_timestamp_internal
551  * workhorse for make_timestamp and make_timestamptz
552  */
553 static Timestamp
554 make_timestamp_internal(int year, int month, int day,
555  int hour, int min, double sec)
556 {
557  struct pg_tm tm;
558  TimeOffset date;
559  TimeOffset time;
560  int dterr;
561  bool bc = false;
562  Timestamp result;
563 
564  tm.tm_year = year;
565  tm.tm_mon = month;
566  tm.tm_mday = day;
567 
568  /* Handle negative years as BC */
569  if (tm.tm_year < 0)
570  {
571  bc = true;
572  tm.tm_year = -tm.tm_year;
573  }
574 
575  dterr = ValidateDate(DTK_DATE_M, false, false, bc, &tm);
576 
577  if (dterr != 0)
578  ereport(ERROR,
579  (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
580  errmsg("date field value out of range: %d-%02d-%02d",
581  year, month, day)));
582 
583  if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
584  ereport(ERROR,
585  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
586  errmsg("date out of range: %d-%02d-%02d",
587  year, month, day)));
588 
589  date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
590 
591  /* Check for time overflow */
592  if (float_time_overflows(hour, min, sec))
593  ereport(ERROR,
594  (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
595  errmsg("time field value out of range: %d:%02d:%02g",
596  hour, min, sec)));
597 
598  /* This should match tm2time */
599  time = (((hour * MINS_PER_HOUR + min) * SECS_PER_MINUTE)
600  * USECS_PER_SEC) + (int64) rint(sec * USECS_PER_SEC);
601 
602  result = date * USECS_PER_DAY + time;
603  /* check for major overflow */
604  if ((result - time) / USECS_PER_DAY != date)
605  ereport(ERROR,
606  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
607  errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
608  year, month, day,
609  hour, min, sec)));
610 
611  /* check for just-barely overflow (okay except time-of-day wraps) */
612  /* caution: we want to allow 1999-12-31 24:00:00 */
613  if ((result < 0 && date > 0) ||
614  (result > 0 && date < -1))
615  ereport(ERROR,
616  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
617  errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
618  year, month, day,
619  hour, min, sec)));
620 
621  /* final range check catches just-out-of-range timestamps */
622  if (!IS_VALID_TIMESTAMP(result))
623  ereport(ERROR,
624  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
625  errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
626  year, month, day,
627  hour, min, sec)));
628 
629  return result;
630 }
631 
632 /*
633  * make_timestamp() - timestamp constructor
634  */
635 Datum
636 make_timestamp(PG_FUNCTION_ARGS)
637 {
638  int32 year = PG_GETARG_INT32(0);
639  int32 month = PG_GETARG_INT32(1);
640  int32 mday = PG_GETARG_INT32(2);
641  int32 hour = PG_GETARG_INT32(3);
642  int32 min = PG_GETARG_INT32(4);
643  float8 sec = PG_GETARG_FLOAT8(5);
644  Timestamp result;
645 
646  result = make_timestamp_internal(year, month, mday,
647  hour, min, sec);
648 
649  PG_RETURN_TIMESTAMP(result);
650 }
651 
652 /*
653  * make_timestamptz() - timestamp with time zone constructor
654  */
655 Datum
656 make_timestamptz(PG_FUNCTION_ARGS)
657 {
658  int32 year = PG_GETARG_INT32(0);
659  int32 month = PG_GETARG_INT32(1);
660  int32 mday = PG_GETARG_INT32(2);
661  int32 hour = PG_GETARG_INT32(3);
662  int32 min = PG_GETARG_INT32(4);
663  float8 sec = PG_GETARG_FLOAT8(5);
664  Timestamp result;
665 
666  result = make_timestamp_internal(year, month, mday,
667  hour, min, sec);
668 
669  PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(result));
670 }
671 
672 /*
673  * Construct a timestamp with time zone.
674  * As above, but the time zone is specified as seventh argument.
675  */
676 Datum
677 make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
678 {
679  int32 year = PG_GETARG_INT32(0);
680  int32 month = PG_GETARG_INT32(1);
681  int32 mday = PG_GETARG_INT32(2);
682  int32 hour = PG_GETARG_INT32(3);
683  int32 min = PG_GETARG_INT32(4);
684  float8 sec = PG_GETARG_FLOAT8(5);
685  text *zone = PG_GETARG_TEXT_PP(6);
686  TimestampTz result;
687  Timestamp timestamp;
688  struct pg_tm tt;
689  int tz;
690  fsec_t fsec;
691 
692  timestamp = make_timestamp_internal(year, month, mday,
693  hour, min, sec);
694 
695  if (timestamp2tm(timestamp, NULL, &tt, &fsec, NULL, NULL) != 0)
696  ereport(ERROR,
697  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
698  errmsg("timestamp out of range")));
699 
700  tz = parse_sane_timezone(&tt, zone);
701 
702  result = dt2local(timestamp, -tz);
703 
704  if (!IS_VALID_TIMESTAMP(result))
705  ereport(ERROR,
706  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
707  errmsg("timestamp out of range")));
708 
709  PG_RETURN_TIMESTAMPTZ(result);
710 }
711 
712 /*
713  * to_timestamp(double precision)
714  * Convert UNIX epoch to timestamptz.
715  */
716 Datum
717 float8_timestamptz(PG_FUNCTION_ARGS)
718 {
719  float8 seconds = PG_GETARG_FLOAT8(0);
720  TimestampTz result;
721 
722  /* Deal with NaN and infinite inputs ... */
723  if (isnan(seconds))
724  ereport(ERROR,
725  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
726  errmsg("timestamp cannot be NaN")));
727 
728  if (isinf(seconds))
729  {
730  if (seconds < 0)
731  TIMESTAMP_NOBEGIN(result);
732  else
733  TIMESTAMP_NOEND(result);
734  }
735  else
736  {
737  /* Out of range? */
738  if (seconds <
739  (float8) SECS_PER_DAY * (DATETIME_MIN_JULIAN - UNIX_EPOCH_JDATE)
740  || seconds >=
741  (float8) SECS_PER_DAY * (TIMESTAMP_END_JULIAN - UNIX_EPOCH_JDATE))
742  ereport(ERROR,
743  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
744  errmsg("timestamp out of range: \"%g\"", seconds)));
745 
746  /* Convert UNIX epoch to Postgres epoch */
747  seconds -= ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
748 
749  seconds = rint(seconds * USECS_PER_SEC);
750  result = (int64) seconds;
751 
752  /* Recheck in case roundoff produces something just out of range */
753  if (!IS_VALID_TIMESTAMP(result))
754  ereport(ERROR,
755  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
756  errmsg("timestamp out of range: \"%g\"",
757  PG_GETARG_FLOAT8(0))));
758  }
759 
760  PG_RETURN_TIMESTAMP(result);
761 }
762 
763 /* timestamptz_out()
764  * Convert a timestamp to external form.
765  */
766 Datum
767 timestamptz_out(PG_FUNCTION_ARGS)
768 {
769  TimestampTz dt = PG_GETARG_TIMESTAMPTZ(0);
770  char *result;
771  int tz;
772  struct pg_tm tt,
773  *tm = &tt;
774  fsec_t fsec;
775  const char *tzn;
776  char buf[MAXDATELEN + 1];
777 
778  if (TIMESTAMP_NOT_FINITE(dt))
779  EncodeSpecialTimestamp(dt, buf);
780  else if (timestamp2tm(dt, &tz, tm, &fsec, &tzn, NULL) == 0)
781  EncodeDateTime(tm, fsec, true, tz, tzn, DateStyle, buf);
782  else
783  ereport(ERROR,
784  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
785  errmsg("timestamp out of range")));
786 
787  result = pstrdup(buf);
788  PG_RETURN_CSTRING(result);
789 }
790 
791 /*
792  * timestamptz_recv - converts external binary format to timestamptz
793  */
794 Datum
795 timestamptz_recv(PG_FUNCTION_ARGS)
796 {
797  StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
798 
799 #ifdef NOT_USED
800  Oid typelem = PG_GETARG_OID(1);
801 #endif
802  int32 typmod = PG_GETARG_INT32(2);
803  TimestampTz timestamp;
804  int tz;
805  struct pg_tm tt,
806  *tm = &tt;
807  fsec_t fsec;
808 
809  timestamp = (TimestampTz) pq_getmsgint64(buf);
810 
811  /* range check: see if timestamptz_out would like it */
812  if (TIMESTAMP_NOT_FINITE(timestamp))
813  /* ok */ ;
814  else if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0 ||
815  !IS_VALID_TIMESTAMP(timestamp))
816  ereport(ERROR,
817  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
818  errmsg("timestamp out of range")));
819 
820  AdjustTimestampForTypmod(&timestamp, typmod);
821 
822  PG_RETURN_TIMESTAMPTZ(timestamp);
823 }
824 
825 /*
826  * timestamptz_send - converts timestamptz to binary format
827  */
828 Datum
829 timestamptz_send(PG_FUNCTION_ARGS)
830 {
831  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
832  StringInfoData buf;
833 
834  pq_begintypsend(&buf);
835  pq_sendint64(&buf, timestamp);
836  PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
837 }
838 
839 Datum
840 timestamptztypmodin(PG_FUNCTION_ARGS)
841 {
842  ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
843 
844  PG_RETURN_INT32(anytimestamp_typmodin(true, ta));
845 }
846 
847 Datum
848 timestamptztypmodout(PG_FUNCTION_ARGS)
849 {
850  int32 typmod = PG_GETARG_INT32(0);
851 
852  PG_RETURN_CSTRING(anytimestamp_typmodout(true, typmod));
853 }
854 
855 
856 /* timestamptz_scale()
857  * Adjust time type for specified scale factor.
858  * Used by PostgreSQL type system to stuff columns.
859  */
860 Datum
861 timestamptz_scale(PG_FUNCTION_ARGS)
862 {
863  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
864  int32 typmod = PG_GETARG_INT32(1);
865  TimestampTz result;
866 
867  result = timestamp;
868 
869  AdjustTimestampForTypmod(&result, typmod);
870 
871  PG_RETURN_TIMESTAMPTZ(result);
872 }
873 
874 
875 /* interval_in()
876  * Convert a string to internal form.
877  *
878  * External format(s):
879  * Uses the generic date/time parsing and decoding routines.
880  */
881 Datum
882 interval_in(PG_FUNCTION_ARGS)
883 {
884  char *str = PG_GETARG_CSTRING(0);
885 
886 #ifdef NOT_USED
887  Oid typelem = PG_GETARG_OID(1);
888 #endif
889  int32 typmod = PG_GETARG_INT32(2);
890  Interval *result;
891  fsec_t fsec;
892  struct pg_tm tt,
893  *tm = &tt;
894  int dtype;
895  int nf;
896  int range;
897  int dterr;
898  char *field[MAXDATEFIELDS];
899  int ftype[MAXDATEFIELDS];
900  char workbuf[256];
901 
902  tm->tm_year = 0;
903  tm->tm_mon = 0;
904  tm->tm_mday = 0;
905  tm->tm_hour = 0;
906  tm->tm_min = 0;
907  tm->tm_sec = 0;
908  fsec = 0;
909 
910  if (typmod >= 0)
911  range = INTERVAL_RANGE(typmod);
912  else
913  range = INTERVAL_FULL_RANGE;
914 
915  dterr = ParseDateTime(str, workbuf, sizeof(workbuf), field,
916  ftype, MAXDATEFIELDS, &nf);
917  if (dterr == 0)
918  dterr = DecodeInterval(field, ftype, nf, range,
919  &dtype, tm, &fsec);
920 
921  /* if those functions think it's a bad format, try ISO8601 style */
922  if (dterr == DTERR_BAD_FORMAT)
923  dterr = DecodeISO8601Interval(str,
924  &dtype, tm, &fsec);
925 
926  if (dterr != 0)
927  {
928  if (dterr == DTERR_FIELD_OVERFLOW)
929  dterr = DTERR_INTERVAL_OVERFLOW;
930  DateTimeParseError(dterr, str, "interval");
931  }
932 
933  result = (Interval *) palloc(sizeof(Interval));
934 
935  switch (dtype)
936  {
937  case DTK_DELTA:
938  if (tm2interval(tm, fsec, result) != 0)
939  ereport(ERROR,
940  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
941  errmsg("interval out of range")));
942  break;
943 
944  default:
945  elog(ERROR, "unexpected dtype %d while parsing interval \"%s\"",
946  dtype, str);
947  }
948 
949  AdjustIntervalForTypmod(result, typmod);
950 
951  PG_RETURN_INTERVAL_P(result);
952 }
953 
954 /* interval_out()
955  * Convert a time span to external form.
956  */
957 Datum
958 interval_out(PG_FUNCTION_ARGS)
959 {
960  Interval *span = PG_GETARG_INTERVAL_P(0);
961  char *result;
962  struct pg_tm tt,
963  *tm = &tt;
964  fsec_t fsec;
965  char buf[MAXDATELEN + 1];
966 
967  if (interval2tm(*span, tm, &fsec) != 0)
968  elog(ERROR, "could not convert interval to tm");
969 
970  EncodeInterval(tm, fsec, IntervalStyle, buf);
971 
972  result = pstrdup(buf);
973  PG_RETURN_CSTRING(result);
974 }
975 
976 /*
977  * interval_recv - converts external binary format to interval
978  */
979 Datum
980 interval_recv(PG_FUNCTION_ARGS)
981 {
982  StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
983 
984 #ifdef NOT_USED
985  Oid typelem = PG_GETARG_OID(1);
986 #endif
987  int32 typmod = PG_GETARG_INT32(2);
988  Interval *interval;
989 
990  interval = (Interval *) palloc(sizeof(Interval));
991 
992  interval->time = pq_getmsgint64(buf);
993  interval->day = pq_getmsgint(buf, sizeof(interval->day));
994  interval->month = pq_getmsgint(buf, sizeof(interval->month));
995 
996  AdjustIntervalForTypmod(interval, typmod);
997 
998  PG_RETURN_INTERVAL_P(interval);
999 }
1000 
1001 /*
1002  * interval_send - converts interval to binary format
1003  */
1004 Datum
1005 interval_send(PG_FUNCTION_ARGS)
1006 {
1007  Interval *interval = PG_GETARG_INTERVAL_P(0);
1008  StringInfoData buf;
1009 
1010  pq_begintypsend(&buf);
1011  pq_sendint64(&buf, interval->time);
1012  pq_sendint32(&buf, interval->day);
1013  pq_sendint32(&buf, interval->month);
1014  PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
1015 }
1016 
1017 /*
1018  * The interval typmod stores a "range" in its high 16 bits and a "precision"
1019  * in its low 16 bits. Both contribute to defining the resolution of the
1020  * type. Range addresses resolution granules larger than one second, and
1021  * precision specifies resolution below one second. This representation can
1022  * express all SQL standard resolutions, but we implement them all in terms of
1023  * truncating rightward from some position. Range is a bitmap of permitted
1024  * fields, but only the temporally-smallest such field is significant to our
1025  * calculations. Precision is a count of sub-second decimal places to retain.
1026  * Setting all bits (INTERVAL_FULL_PRECISION) gives the same truncation
1027  * semantics as choosing MAX_INTERVAL_PRECISION.
1028  */
1029 Datum
1030 intervaltypmodin(PG_FUNCTION_ARGS)
1031 {
1032  ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
1033  int32 *tl;
1034  int n;
1035  int32 typmod;
1036 
1037  tl = ArrayGetIntegerTypmods(ta, &n);
1038 
1039  /*
1040  * tl[0] - interval range (fields bitmask) tl[1] - precision (optional)
1041  *
1042  * Note we must validate tl[0] even though it's normally guaranteed
1043  * correct by the grammar --- consider SELECT 'foo'::"interval"(1000).
1044  */
1045  if (n > 0)
1046  {
1047  switch (tl[0])
1048  {
1049  case INTERVAL_MASK(YEAR):
1050  case INTERVAL_MASK(MONTH):
1051  case INTERVAL_MASK(DAY):
1052  case INTERVAL_MASK(HOUR):
1053  case INTERVAL_MASK(MINUTE):
1054  case INTERVAL_MASK(SECOND):
1055  case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
1056  case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
1057  case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1058  case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1059  case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1060  case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1061  case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1062  case INTERVAL_FULL_RANGE:
1063  /* all OK */
1064  break;
1065  default:
1066  ereport(ERROR,
1067  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1068  errmsg("invalid INTERVAL type modifier")));
1069  }
1070  }
1071 
1072  if (n == 1)
1073  {
1074  if (tl[0] != INTERVAL_FULL_RANGE)
1075  typmod = INTERVAL_TYPMOD(INTERVAL_FULL_PRECISION, tl[0]);
1076  else
1077  typmod = -1;
1078  }
1079  else if (n == 2)
1080  {
1081  if (tl[1] < 0)
1082  ereport(ERROR,
1083  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1084  errmsg("INTERVAL(%d) precision must not be negative",
1085  tl[1])));
1086  if (tl[1] > MAX_INTERVAL_PRECISION)
1087  {
1088  ereport(WARNING,
1089  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1090  errmsg("INTERVAL(%d) precision reduced to maximum allowed, %d",
1091  tl[1], MAX_INTERVAL_PRECISION)));
1092  typmod = INTERVAL_TYPMOD(MAX_INTERVAL_PRECISION, tl[0]);
1093  }
1094  else
1095  typmod = INTERVAL_TYPMOD(tl[1], tl[0]);
1096  }
1097  else
1098  {
1099  ereport(ERROR,
1100  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1101  errmsg("invalid INTERVAL type modifier")));
1102  typmod = 0; /* keep compiler quiet */
1103  }
1104 
1105  PG_RETURN_INT32(typmod);
1106 }
1107 
1108 Datum
1109 intervaltypmodout(PG_FUNCTION_ARGS)
1110 {
1111  int32 typmod = PG_GETARG_INT32(0);
1112  char *res = (char *) palloc(64);
1113  int fields;
1114  int precision;
1115  const char *fieldstr;
1116 
1117  if (typmod < 0)
1118  {
1119  *res = '\0';
1120  PG_RETURN_CSTRING(res);
1121  }
1122 
1123  fields = INTERVAL_RANGE(typmod);
1124  precision = INTERVAL_PRECISION(typmod);
1125 
1126  switch (fields)
1127  {
1128  case INTERVAL_MASK(YEAR):
1129  fieldstr = " year";
1130  break;
1131  case INTERVAL_MASK(MONTH):
1132  fieldstr = " month";
1133  break;
1134  case INTERVAL_MASK(DAY):
1135  fieldstr = " day";
1136  break;
1137  case INTERVAL_MASK(HOUR):
1138  fieldstr = " hour";
1139  break;
1140  case INTERVAL_MASK(MINUTE):
1141  fieldstr = " minute";
1142  break;
1143  case INTERVAL_MASK(SECOND):
1144  fieldstr = " second";
1145  break;
1146  case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
1147  fieldstr = " year to month";
1148  break;
1149  case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
1150  fieldstr = " day to hour";
1151  break;
1152  case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1153  fieldstr = " day to minute";
1154  break;
1155  case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1156  fieldstr = " day to second";
1157  break;
1158  case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1159  fieldstr = " hour to minute";
1160  break;
1161  case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1162  fieldstr = " hour to second";
1163  break;
1164  case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1165  fieldstr = " minute to second";
1166  break;
1167  case INTERVAL_FULL_RANGE:
1168  fieldstr = "";
1169  break;
1170  default:
1171  elog(ERROR, "invalid INTERVAL typmod: 0x%x", typmod);
1172  fieldstr = "";
1173  break;
1174  }
1175 
1176  if (precision != INTERVAL_FULL_PRECISION)
1177  snprintf(res, 64, "%s(%d)", fieldstr, precision);
1178  else
1179  snprintf(res, 64, "%s", fieldstr);
1180 
1181  PG_RETURN_CSTRING(res);
1182 }
1183 
1184 /*
1185  * Given an interval typmod value, return a code for the least-significant
1186  * field that the typmod allows to be nonzero, for instance given
1187  * INTERVAL DAY TO HOUR we want to identify "hour".
1188  *
1189  * The results should be ordered by field significance, which means
1190  * we can't use the dt.h macros YEAR etc, because for some odd reason
1191  * they aren't ordered that way. Instead, arbitrarily represent
1192  * SECOND = 0, MINUTE = 1, HOUR = 2, DAY = 3, MONTH = 4, YEAR = 5.
1193  */
1194 static int
1195 intervaltypmodleastfield(int32 typmod)
1196 {
1197  if (typmod < 0)
1198  return 0; /* SECOND */
1199 
1200  switch (INTERVAL_RANGE(typmod))
1201  {
1202  case INTERVAL_MASK(YEAR):
1203  return 5; /* YEAR */
1204  case INTERVAL_MASK(MONTH):
1205  return 4; /* MONTH */
1206  case INTERVAL_MASK(DAY):
1207  return 3; /* DAY */
1208  case INTERVAL_MASK(HOUR):
1209  return 2; /* HOUR */
1210  case INTERVAL_MASK(MINUTE):
1211  return 1; /* MINUTE */
1212  case INTERVAL_MASK(SECOND):
1213  return 0; /* SECOND */
1214  case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
1215  return 4; /* MONTH */
1216  case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
1217  return 2; /* HOUR */
1218  case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1219  return 1; /* MINUTE */
1220  case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1221  return 0; /* SECOND */
1222  case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1223  return 1; /* MINUTE */
1224  case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1225  return 0; /* SECOND */
1226  case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1227  return 0; /* SECOND */
1228  case INTERVAL_FULL_RANGE:
1229  return 0; /* SECOND */
1230  default:
1231  elog(ERROR, "invalid INTERVAL typmod: 0x%x", typmod);
1232  break;
1233  }
1234  return 0; /* can't get here, but keep compiler quiet */
1235 }
1236 
1237 
1238 /*
1239  * interval_support()
1240  *
1241  * Planner support function for interval_scale().
1242  *
1243  * Flatten superfluous calls to interval_scale(). The interval typmod is
1244  * complex to permit accepting and regurgitating all SQL standard variations.
1245  * For truncation purposes, it boils down to a single, simple granularity.
1246  */
1247 Datum
1248 interval_support(PG_FUNCTION_ARGS)
1249 {
1250  Node *rawreq = (Node *) PG_GETARG_POINTER(0);
1251  Node *ret = NULL;
1252 
1253  if (IsA(rawreq, SupportRequestSimplify))
1254  {
1255  SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
1256  FuncExpr *expr = req->fcall;
1257  Node *typmod;
1258 
1259  Assert(list_length(expr->args) >= 2);
1260 
1261  typmod = (Node *) lsecond(expr->args);
1262 
1263  if (IsA(typmod, Const) && !((Const *) typmod)->constisnull)
1264  {
1265  Node *source = (Node *) linitial(expr->args);
1266  int32 new_typmod = DatumGetInt32(((Const *) typmod)->constvalue);
1267  bool noop;
1268 
1269  if (new_typmod < 0)
1270  noop = true;
1271  else
1272  {
1273  int32 old_typmod = exprTypmod(source);
1274  int old_least_field;
1275  int new_least_field;
1276  int old_precis;
1277  int new_precis;
1278 
1279  old_least_field = intervaltypmodleastfield(old_typmod);
1280  new_least_field = intervaltypmodleastfield(new_typmod);
1281  if (old_typmod < 0)
1282  old_precis = INTERVAL_FULL_PRECISION;
1283  else
1284  old_precis = INTERVAL_PRECISION(old_typmod);
1285  new_precis = INTERVAL_PRECISION(new_typmod);
1286 
1287  /*
1288  * Cast is a no-op if least field stays the same or decreases
1289  * while precision stays the same or increases. But
1290  * precision, which is to say, sub-second precision, only
1291  * affects ranges that include SECOND.
1292  */
1293  noop = (new_least_field <= old_least_field) &&
1294  (old_least_field > 0 /* SECOND */ ||
1295  new_precis >= MAX_INTERVAL_PRECISION ||
1296  new_precis >= old_precis);
1297  }
1298  if (noop)
1299  ret = relabel_to_typmod(source, new_typmod);
1300  }
1301  }
1302 
1303  PG_RETURN_POINTER(ret);
1304 }
1305 
1306 /* interval_scale()
1307  * Adjust interval type for specified fields.
1308  * Used by PostgreSQL type system to stuff columns.
1309  */
1310 Datum
1311 interval_scale(PG_FUNCTION_ARGS)
1312 {
1313  Interval *interval = PG_GETARG_INTERVAL_P(0);
1314  int32 typmod = PG_GETARG_INT32(1);
1315  Interval *result;
1316 
1317  result = palloc(sizeof(Interval));
1318  *result = *interval;
1319 
1320  AdjustIntervalForTypmod(result, typmod);
1321 
1322  PG_RETURN_INTERVAL_P(result);
1323 }
1324 
1325 /*
1326  * Adjust interval for specified precision, in both YEAR to SECOND
1327  * range and sub-second precision.
1328  */
1329 static void
1330 AdjustIntervalForTypmod(Interval *interval, int32 typmod)
1331 {
1332  static const int64 IntervalScales[MAX_INTERVAL_PRECISION + 1] = {
1333  INT64CONST(1000000),
1334  INT64CONST(100000),
1335  INT64CONST(10000),
1336  INT64CONST(1000),
1337  INT64CONST(100),
1338  INT64CONST(10),
1339  INT64CONST(1)
1340  };
1341 
1342  static const int64 IntervalOffsets[MAX_INTERVAL_PRECISION + 1] = {
1343  INT64CONST(500000),
1344  INT64CONST(50000),
1345  INT64CONST(5000),
1346  INT64CONST(500),
1347  INT64CONST(50),
1348  INT64CONST(5),
1349  INT64CONST(0)
1350  };
1351 
1352  /*
1353  * Unspecified range and precision? Then not necessary to adjust. Setting
1354  * typmod to -1 is the convention for all data types.
1355  */
1356  if (typmod >= 0)
1357  {
1358  int range = INTERVAL_RANGE(typmod);
1359  int precision = INTERVAL_PRECISION(typmod);
1360 
1361  /*
1362  * Our interpretation of intervals with a limited set of fields is
1363  * that fields to the right of the last one specified are zeroed out,
1364  * but those to the left of it remain valid. Thus for example there
1365  * is no operational difference between INTERVAL YEAR TO MONTH and
1366  * INTERVAL MONTH. In some cases we could meaningfully enforce that
1367  * higher-order fields are zero; for example INTERVAL DAY could reject
1368  * nonzero "month" field. However that seems a bit pointless when we
1369  * can't do it consistently. (We cannot enforce a range limit on the
1370  * highest expected field, since we do not have any equivalent of
1371  * SQL's <interval leading field precision>.) If we ever decide to
1372  * revisit this, interval_support will likely require adjusting.
1373  *
1374  * Note: before PG 8.4 we interpreted a limited set of fields as
1375  * actually causing a "modulo" operation on a given value, potentially
1376  * losing high-order as well as low-order information. But there is
1377  * no support for such behavior in the standard, and it seems fairly
1378  * undesirable on data consistency grounds anyway. Now we only
1379  * perform truncation or rounding of low-order fields.
1380  */
1381  if (range == INTERVAL_FULL_RANGE)
1382  {
1383  /* Do nothing... */
1384  }
1385  else if (range == INTERVAL_MASK(YEAR))
1386  {
1387  interval->month = (interval->month / MONTHS_PER_YEAR) * MONTHS_PER_YEAR;
1388  interval->day = 0;
1389  interval->time = 0;
1390  }
1391  else if (range == INTERVAL_MASK(MONTH))
1392  {
1393  interval->day = 0;
1394  interval->time = 0;
1395  }
1396  /* YEAR TO MONTH */
1397  else if (range == (INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH)))
1398  {
1399  interval->day = 0;
1400  interval->time = 0;
1401  }
1402  else if (range == INTERVAL_MASK(DAY))
1403  {
1404  interval->time = 0;
1405  }
1406  else if (range == INTERVAL_MASK(HOUR))
1407  {
1408  interval->time = (interval->time / USECS_PER_HOUR) *
1409  USECS_PER_HOUR;
1410  }
1411  else if (range == INTERVAL_MASK(MINUTE))
1412  {
1413  interval->time = (interval->time / USECS_PER_MINUTE) *
1414  USECS_PER_MINUTE;
1415  }
1416  else if (range == INTERVAL_MASK(SECOND))
1417  {
1418  /* fractional-second rounding will be dealt with below */
1419  }
1420  /* DAY TO HOUR */
1421  else if (range == (INTERVAL_MASK(DAY) |
1422  INTERVAL_MASK(HOUR)))
1423  {
1424  interval->time = (interval->time / USECS_PER_HOUR) *
1425  USECS_PER_HOUR;
1426  }
1427  /* DAY TO MINUTE */
1428  else if (range == (INTERVAL_MASK(DAY) |
1429  INTERVAL_MASK(HOUR) |
1430  INTERVAL_MASK(MINUTE)))
1431  {
1432  interval->time = (interval->time / USECS_PER_MINUTE) *
1433  USECS_PER_MINUTE;
1434  }
1435  /* DAY TO SECOND */
1436  else if (range == (INTERVAL_MASK(DAY) |
1437  INTERVAL_MASK(HOUR) |
1438  INTERVAL_MASK(MINUTE) |
1439  INTERVAL_MASK(SECOND)))
1440  {
1441  /* fractional-second rounding will be dealt with below */
1442  }
1443  /* HOUR TO MINUTE */
1444  else if (range == (INTERVAL_MASK(HOUR) |
1445  INTERVAL_MASK(MINUTE)))
1446  {
1447  interval->time = (interval->time / USECS_PER_MINUTE) *
1448  USECS_PER_MINUTE;
1449  }
1450  /* HOUR TO SECOND */
1451  else if (range == (INTERVAL_MASK(HOUR) |
1452  INTERVAL_MASK(MINUTE) |
1453  INTERVAL_MASK(SECOND)))
1454  {
1455  /* fractional-second rounding will be dealt with below */
1456  }
1457  /* MINUTE TO SECOND */
1458  else if (range == (INTERVAL_MASK(MINUTE) |
1459  INTERVAL_MASK(SECOND)))
1460  {
1461  /* fractional-second rounding will be dealt with below */
1462  }
1463  else
1464  elog(ERROR, "unrecognized interval typmod: %d", typmod);
1465 
1466  /* Need to adjust sub-second precision? */
1467  if (precision != INTERVAL_FULL_PRECISION)
1468  {
1469  if (precision < 0 || precision > MAX_INTERVAL_PRECISION)
1470  ereport(ERROR,
1471  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1472  errmsg("interval(%d) precision must be between %d and %d",
1473  precision, 0, MAX_INTERVAL_PRECISION)));
1474 
1475  if (interval->time >= INT64CONST(0))
1476  {
1477  interval->time = ((interval->time +
1478  IntervalOffsets[precision]) /
1479  IntervalScales[precision]) *
1480  IntervalScales[precision];
1481  }
1482  else
1483  {
1484  interval->time = -(((-interval->time +
1485  IntervalOffsets[precision]) /
1486  IntervalScales[precision]) *
1487  IntervalScales[precision]);
1488  }
1489  }
1490  }
1491 }
1492 
1493 /*
1494  * make_interval - numeric Interval constructor
1495  */
1496 Datum
1497 make_interval(PG_FUNCTION_ARGS)
1498 {
1499  int32 years = PG_GETARG_INT32(0);
1500  int32 months = PG_GETARG_INT32(1);
1501  int32 weeks = PG_GETARG_INT32(2);
1502  int32 days = PG_GETARG_INT32(3);
1503  int32 hours = PG_GETARG_INT32(4);
1504  int32 mins = PG_GETARG_INT32(5);
1505  double secs = PG_GETARG_FLOAT8(6);
1506  Interval *result;
1507 
1508  /*
1509  * Reject out-of-range inputs. We really ought to check the integer
1510  * inputs as well, but it's not entirely clear what limits to apply.
1511  */
1512  if (isinf(secs) || isnan(secs))
1513  ereport(ERROR,
1514  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1515  errmsg("interval out of range")));
1516 
1517  result = (Interval *) palloc(sizeof(Interval));
1518  result->month = years * MONTHS_PER_YEAR + months;
1519  result->day = weeks * 7 + days;
1520 
1521  secs = rint(secs * USECS_PER_SEC);
1522  result->time = hours * ((int64) SECS_PER_HOUR * USECS_PER_SEC) +
1523  mins * ((int64) SECS_PER_MINUTE * USECS_PER_SEC) +
1524  (int64) secs;
1525 
1526  PG_RETURN_INTERVAL_P(result);
1527 }
1528 
1529 /* EncodeSpecialTimestamp()
1530  * Convert reserved timestamp data type to string.
1531  */
1532 void
1533 EncodeSpecialTimestamp(Timestamp dt, char *str)
1534 {
1535  if (TIMESTAMP_IS_NOBEGIN(dt))
1536  strcpy(str, EARLY);
1537  else if (TIMESTAMP_IS_NOEND(dt))
1538  strcpy(str, LATE);
1539  else /* shouldn't happen */
1540  elog(ERROR, "invalid argument for EncodeSpecialTimestamp");
1541 }
1542 
1543 Datum
1544 now(PG_FUNCTION_ARGS)
1545 {
1546  PG_RETURN_TIMESTAMPTZ(GetCurrentTransactionStartTimestamp());
1547 }
1548 
1549 Datum
1550 statement_timestamp(PG_FUNCTION_ARGS)
1551 {
1552  PG_RETURN_TIMESTAMPTZ(GetCurrentStatementStartTimestamp());
1553 }
1554 
1555 Datum
1556 clock_timestamp(PG_FUNCTION_ARGS)
1557 {
1558  PG_RETURN_TIMESTAMPTZ(GetCurrentTimestamp());
1559 }
1560 
1561 Datum
1562 pg_postmaster_start_time(PG_FUNCTION_ARGS)
1563 {
1564  PG_RETURN_TIMESTAMPTZ(PgStartTime);
1565 }
1566 
1567 Datum
1568 pg_conf_load_time(PG_FUNCTION_ARGS)
1569 {
1570  PG_RETURN_TIMESTAMPTZ(PgReloadTime);
1571 }
1572 
1573 /*
1574  * GetCurrentTimestamp -- get the current operating system time
1575  *
1576  * Result is in the form of a TimestampTz value, and is expressed to the
1577  * full precision of the gettimeofday() syscall
1578  */
1579 TimestampTz
1580 GetCurrentTimestamp(void)
1581 {
1582  TimestampTz result;
1583  struct timeval tp;
1584 
1585  gettimeofday(&tp, NULL);
1586 
1587  result = (TimestampTz) tp.tv_sec -
1588  ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
1589  result = (result * USECS_PER_SEC) + tp.tv_usec;
1590 
1591  return result;
1592 }
1593 
1594 /*
1595  * GetSQLCurrentTimestamp -- implements CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(n)
1596  */
1597 TimestampTz
1598 GetSQLCurrentTimestamp(int32 typmod)
1599 {
1600  TimestampTz ts;
1601 
1602  ts = GetCurrentTransactionStartTimestamp();
1603  if (typmod >= 0)
1604  AdjustTimestampForTypmod(&ts, typmod);
1605  return ts;
1606 }
1607 
1608 /*
1609  * GetSQLLocalTimestamp -- implements LOCALTIMESTAMP, LOCALTIMESTAMP(n)
1610  */
1611 Timestamp
1612 GetSQLLocalTimestamp(int32 typmod)
1613 {
1614  Timestamp ts;
1615 
1616  ts = timestamptz2timestamp(GetCurrentTransactionStartTimestamp());
1617  if (typmod >= 0)
1618  AdjustTimestampForTypmod(&ts, typmod);
1619  return ts;
1620 }
1621 
1622 /*
1623  * timeofday(*) -- returns the current time as a text.
1624  */
1625 Datum
1626 timeofday(PG_FUNCTION_ARGS)
1627 {
1628  struct timeval tp;
1629  char templ[128];
1630  char buf[128];
1631  pg_time_t tt;
1632 
1633  gettimeofday(&tp, NULL);
1634  tt = (pg_time_t) tp.tv_sec;
1635  pg_strftime(templ, sizeof(templ), "%a %b %d %H:%M:%S.%%06d %Y %Z",
1636  pg_localtime(&tt, session_timezone));
1637  snprintf(buf, sizeof(buf), templ, tp.tv_usec);
1638 
1639  PG_RETURN_TEXT_P(cstring_to_text(buf));
1640 }
1641 
1642 /*
1643  * TimestampDifference -- convert the difference between two timestamps
1644  * into integer seconds and microseconds
1645  *
1646  * This is typically used to calculate a wait timeout for select(2),
1647  * which explains the otherwise-odd choice of output format.
1648  *
1649  * Both inputs must be ordinary finite timestamps (in current usage,
1650  * they'll be results from GetCurrentTimestamp()).
1651  *
1652  * We expect start_time <= stop_time. If not, we return zeros,
1653  * since then we're already past the previously determined stop_time.
1654  */
1655 void
1656 TimestampDifference(TimestampTz start_time, TimestampTz stop_time,
1657  long *secs, int *microsecs)
1658 {
1659  TimestampTz diff = stop_time - start_time;
1660 
1661  if (diff <= 0)
1662  {
1663  *secs = 0;
1664  *microsecs = 0;
1665  }
1666  else
1667  {
1668  *secs = (long) (diff / USECS_PER_SEC);
1669  *microsecs = (int) (diff % USECS_PER_SEC);
1670  }
1671 }
1672 
1673 /*
1674  * TimestampDifferenceMilliseconds -- convert the difference between two
1675  * timestamps into integer milliseconds
1676  *
1677  * This is typically used to calculate a wait timeout for WaitLatch()
1678  * or a related function. The choice of "long" as the result type
1679  * is to harmonize with that. It is caller's responsibility that the
1680  * input timestamps not be so far apart as to risk overflow of "long"
1681  * (which'd happen at about 25 days on machines with 32-bit "long").
1682  *
1683  * Both inputs must be ordinary finite timestamps (in current usage,
1684  * they'll be results from GetCurrentTimestamp()).
1685  *
1686  * We expect start_time <= stop_time. If not, we return zero,
1687  * since then we're already past the previously determined stop_time.
1688  *
1689  * Note we round up any fractional millisecond, since waiting for just
1690  * less than the intended timeout is undesirable.
1691  */
1692 long
1693 TimestampDifferenceMilliseconds(TimestampTz start_time, TimestampTz stop_time)
1694 {
1695  TimestampTz diff = stop_time - start_time;
1696 
1697  if (diff <= 0)
1698  return 0;
1699  else
1700  return (long) ((diff + 999) / 1000);
1701 }
1702 
1703 /*
1704  * TimestampDifferenceExceeds -- report whether the difference between two
1705  * timestamps is >= a threshold (expressed in milliseconds)
1706  *
1707  * Both inputs must be ordinary finite timestamps (in current usage,
1708  * they'll be results from GetCurrentTimestamp()).
1709  */
1710 bool
1711 TimestampDifferenceExceeds(TimestampTz start_time,
1712  TimestampTz stop_time,
1713  int msec)
1714 {
1715  TimestampTz diff = stop_time - start_time;
1716 
1717  return (diff >= msec * INT64CONST(1000));
1718 }
1719 
1720 /*
1721  * Convert a time_t to TimestampTz.
1722  *
1723  * We do not use time_t internally in Postgres, but this is provided for use
1724  * by functions that need to interpret, say, a stat(2) result.
1725  *
1726  * To avoid having the function's ABI vary depending on the width of time_t,
1727  * we declare the argument as pg_time_t, which is cast-compatible with
1728  * time_t but always 64 bits wide (unless the platform has no 64-bit type).
1729  * This detail should be invisible to callers, at least at source code level.
1730  */
1731 TimestampTz
1732 time_t_to_timestamptz(pg_time_t tm)
1733 {
1734  TimestampTz result;
1735 
1736  result = (TimestampTz) tm -
1737  ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
1738  result *= USECS_PER_SEC;
1739 
1740  return result;
1741 }
1742 
1743 /*
1744  * Convert a TimestampTz to time_t.
1745  *
1746  * This too is just marginally useful, but some places need it.
1747  *
1748  * To avoid having the function's ABI vary depending on the width of time_t,
1749  * we declare the result as pg_time_t, which is cast-compatible with
1750  * time_t but always 64 bits wide (unless the platform has no 64-bit type).
1751  * This detail should be invisible to callers, at least at source code level.
1752  */
1753 pg_time_t
1754 timestamptz_to_time_t(TimestampTz t)
1755 {
1756  pg_time_t result;
1757 
1758  result = (pg_time_t) (t / USECS_PER_SEC +
1759  ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY));
1760 
1761  return result;
1762 }
1763 
1764 /*
1765  * Produce a C-string representation of a TimestampTz.
1766  *
1767  * This is mostly for use in emitting messages. The primary difference
1768  * from timestamptz_out is that we force the output format to ISO. Note
1769  * also that the result is in a static buffer, not pstrdup'd.
1770  *
1771  * See also pg_strftime.
1772  */
1773 const char *
1774 timestamptz_to_str(TimestampTz t)
1775 {
1776  static char buf[MAXDATELEN + 1];
1777  int tz;
1778  struct pg_tm tt,
1779  *tm = &tt;
1780  fsec_t fsec;
1781  const char *tzn;
1782 
1783  if (TIMESTAMP_NOT_FINITE(t))
1784  EncodeSpecialTimestamp(t, buf);
1785  else if (timestamp2tm(t, &tz, tm, &fsec, &tzn, NULL) == 0)
1786  EncodeDateTime(tm, fsec, true, tz, tzn, USE_ISO_DATES, buf);
1787  else
1788  strlcpy(buf, "(timestamp out of range)", sizeof(buf));
1789 
1790  return buf;
1791 }
1792 
1793 
1794 void
1795 dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec)
1796 {
1797  TimeOffset time;
1798 
1799  time = jd;
1800 
1801  *hour = time / USECS_PER_HOUR;
1802  time -= (*hour) * USECS_PER_HOUR;
1803  *min = time / USECS_PER_MINUTE;
1804  time -= (*min) * USECS_PER_MINUTE;
1805  *sec = time / USECS_PER_SEC;
1806  *fsec = time - (*sec * USECS_PER_SEC);
1807 } /* dt2time() */
1808 
1809 
1810 /*
1811  * timestamp2tm() - Convert timestamp data type to POSIX time structure.
1812  *
1813  * Note that year is _not_ 1900-based, but is an explicit full value.
1814  * Also, month is one-based, _not_ zero-based.
1815  * Returns:
1816  * 0 on success
1817  * -1 on out of range
1818  *
1819  * If attimezone is NULL, the global timezone setting will be used.
1820  */
1821 int
1822 timestamp2tm(Timestamp dt, int *tzp, struct pg_tm *tm, fsec_t *fsec, const char **tzn, pg_tz *attimezone)
1823 {
1824  Timestamp date;
1825  Timestamp time;
1826  pg_time_t utime;
1827 
1828  /* Use session timezone if caller asks for default */
1829  if (attimezone == NULL)
1830  attimezone = session_timezone;
1831 
1832  time = dt;
1833  TMODULO(time, date, USECS_PER_DAY);
1834 
1835  if (time < INT64CONST(0))
1836  {
1837  time += USECS_PER_DAY;
1838  date -= 1;
1839  }
1840 
1841  /* add offset to go from J2000 back to standard Julian date */
1842  date += POSTGRES_EPOCH_JDATE;
1843 
1844  /* Julian day routine does not work for negative Julian days */
1845  if (date < 0 || date > (Timestamp) INT_MAX)
1846  return -1;
1847 
1848  j2date((int) date, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
1849  dt2time(time, &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
1850 
1851  /* Done if no TZ conversion wanted */
1852  if (tzp == NULL)
1853  {
1854  tm->tm_isdst = -1;
1855  tm->tm_gmtoff = 0;
1856  tm->tm_zone = NULL;
1857  if (tzn != NULL)
1858  *tzn = NULL;
1859  return 0;
1860  }
1861 
1862  /*
1863  * If the time falls within the range of pg_time_t, use pg_localtime() to
1864  * rotate to the local time zone.
1865  *
1866  * First, convert to an integral timestamp, avoiding possibly
1867  * platform-specific roundoff-in-wrong-direction errors, and adjust to
1868  * Unix epoch. Then see if we can convert to pg_time_t without loss. This
1869  * coding avoids hardwiring any assumptions about the width of pg_time_t,
1870  * so it should behave sanely on machines without int64.
1871  */
1872  dt = (dt - *fsec) / USECS_PER_SEC +
1873  (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY;
1874  utime = (pg_time_t) dt;
1875  if ((Timestamp) utime == dt)
1876  {
1877  struct pg_tm *tx = pg_localtime(&utime, attimezone);
1878 
1879  tm->tm_year = tx->tm_year + 1900;
1880  tm->tm_mon = tx->tm_mon + 1;
1881  tm->tm_mday = tx->tm_mday;
1882  tm->tm_hour = tx->tm_hour;
1883  tm->tm_min = tx->tm_min;
1884  tm->tm_sec = tx->tm_sec;
1885  tm->tm_isdst = tx->tm_isdst;
1886  tm->tm_gmtoff = tx->tm_gmtoff;
1887  tm->tm_zone = tx->tm_zone;
1888  *tzp = -tm->tm_gmtoff;
1889  if (tzn != NULL)
1890  *tzn = tm->tm_zone;
1891  }
1892  else
1893  {
1894  /*
1895  * When out of range of pg_time_t, treat as GMT
1896  */
1897  *tzp = 0;
1898  /* Mark this as *no* time zone available */
1899  tm->tm_isdst = -1;
1900  tm->tm_gmtoff = 0;
1901  tm->tm_zone = NULL;
1902  if (tzn != NULL)
1903  *tzn = NULL;
1904  }
1905 
1906  return 0;
1907 }
1908 
1909 
1910 /* tm2timestamp()
1911  * Convert a tm structure to a timestamp data type.
1912  * Note that year is _not_ 1900-based, but is an explicit full value.
1913  * Also, month is one-based, _not_ zero-based.
1914  *
1915  * Returns -1 on failure (value out of range).
1916  */
1917 int
1918 tm2timestamp(struct pg_tm *tm, fsec_t fsec, int *tzp, Timestamp *result)
1919 {
1920  TimeOffset date;
1921  TimeOffset time;
1922 
1923  /* Prevent overflow in Julian-day routines */
1924  if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
1925  {
1926  *result = 0; /* keep compiler quiet */
1927  return -1;
1928  }
1929 
1930  date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
1931  time = time2t(tm->tm_hour, tm->tm_min, tm->tm_sec, fsec);
1932 
1933  *result = date * USECS_PER_DAY + time;
1934  /* check for major overflow */
1935  if ((*result - time) / USECS_PER_DAY != date)
1936  {
1937  *result = 0; /* keep compiler quiet */
1938  return -1;
1939  }
1940  /* check for just-barely overflow (okay except time-of-day wraps) */
1941  /* caution: we want to allow 1999-12-31 24:00:00 */
1942  if ((*result < 0 && date > 0) ||
1943  (*result > 0 && date < -1))
1944  {
1945  *result = 0; /* keep compiler quiet */
1946  return -1;
1947  }
1948  if (tzp != NULL)
1949  *result = dt2local(*result, -(*tzp));
1950 
1951  /* final range check catches just-out-of-range timestamps */
1952  if (!IS_VALID_TIMESTAMP(*result))
1953  {
1954  *result = 0; /* keep compiler quiet */
1955  return -1;
1956  }
1957 
1958  return 0;
1959 }
1960 
1961 
1962 /* interval2tm()
1963  * Convert an interval data type to a tm structure.
1964  */
1965 int
1966 interval2tm(Interval span, struct pg_tm *tm, fsec_t *fsec)
1967 {
1968  TimeOffset time;
1969  TimeOffset tfrac;
1970 
1971  tm->tm_year = span.month / MONTHS_PER_YEAR;
1972  tm->tm_mon = span.month % MONTHS_PER_YEAR;
1973  tm->tm_mday = span.day;
1974  time = span.time;
1975 
1976  tfrac = time / USECS_PER_HOUR;
1977  time -= tfrac * USECS_PER_HOUR;
1978  tm->tm_hour = tfrac;
1979  if (!SAMESIGN(tm->tm_hour, tfrac))
1980  ereport(ERROR,
1981  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1982  errmsg("interval out of range")));
1983  tfrac = time / USECS_PER_MINUTE;
1984  time -= tfrac * USECS_PER_MINUTE;
1985  tm->tm_min = tfrac;
1986  tfrac = time / USECS_PER_SEC;
1987  *fsec = time - (tfrac * USECS_PER_SEC);
1988  tm->tm_sec = tfrac;
1989 
1990  return 0;
1991 }
1992 
1993 int
1994 tm2interval(struct pg_tm *tm, fsec_t fsec, Interval *span)
1995 {
1996  double total_months = (double) tm->tm_year * MONTHS_PER_YEAR + tm->tm_mon;
1997 
1998  if (total_months > INT_MAX || total_months < INT_MIN)
1999  return -1;
2000  span->month = total_months;
2001  span->day = tm->tm_mday;
2002  span->time = (((((tm->tm_hour * INT64CONST(60)) +
2003  tm->tm_min) * INT64CONST(60)) +
2004  tm->tm_sec) * USECS_PER_SEC) + fsec;
2005 
2006  return 0;
2007 }
2008 
2009 static TimeOffset
2010 time2t(const int hour, const int min, const int sec, const fsec_t fsec)
2011 {
2012  return (((((hour * MINS_PER_HOUR) + min) * SECS_PER_MINUTE) + sec) * USECS_PER_SEC) + fsec;
2013 }
2014 
2015 static Timestamp
2016 dt2local(Timestamp dt, int tz)
2017 {
2018  dt -= (tz * USECS_PER_SEC);
2019  return dt;
2020 }
2021 
2022 
2023 /*****************************************************************************
2024  * PUBLIC ROUTINES *
2025  *****************************************************************************/
2026 
2027 
2028 Datum
2029 timestamp_finite(PG_FUNCTION_ARGS)
2030 {
2031  Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
2032 
2033  PG_RETURN_BOOL(!TIMESTAMP_NOT_FINITE(timestamp));
2034 }
2035 
2036 Datum
2037 interval_finite(PG_FUNCTION_ARGS)
2038 {
2039  PG_RETURN_BOOL(true);
2040 }
2041 
2042 
2043 /*----------------------------------------------------------
2044  * Relational operators for timestamp.
2045  *---------------------------------------------------------*/
2046 
2047 void
2048 GetEpochTime(struct pg_tm *tm)
2049 {
2050  struct pg_tm *t0;
2051  pg_time_t epoch = 0;
2052 
2053  t0 = pg_gmtime(&epoch);
2054 
2055  if (t0 == NULL)
2056  elog(ERROR, "could not convert epoch to timestamp: %m");
2057 
2058  tm->tm_year = t0->tm_year;
2059  tm->tm_mon = t0->tm_mon;
2060  tm->tm_mday = t0->tm_mday;
2061  tm->tm_hour = t0->tm_hour;
2062  tm->tm_min = t0->tm_min;
2063  tm->tm_sec = t0->tm_sec;
2064 
2065  tm->tm_year += 1900;
2066  tm->tm_mon++;
2067 }
2068 
2069 Timestamp
2070 SetEpochTimestamp(void)
2071 {
2072  Timestamp dt;
2073  struct pg_tm tt,
2074  *tm = &tt;
2075 
2076  GetEpochTime(tm);
2077  /* we don't bother to test for failure ... */
2078  tm2timestamp(tm, 0, NULL, &dt);
2079 
2080  return dt;
2081 } /* SetEpochTimestamp() */
2082 
2083 /*
2084  * We are currently sharing some code between timestamp and timestamptz.
2085  * The comparison functions are among them. - thomas 2001-09-25
2086  *
2087  * timestamp_relop - is timestamp1 relop timestamp2
2088  */
2089 int
2090 timestamp_cmp_internal(Timestamp dt1, Timestamp dt2)
2091 {
2092  return (dt1 < dt2) ? -1 : ((dt1 > dt2) ? 1 : 0);
2093 }
2094 
2095 Datum
2096 timestamp_eq(PG_FUNCTION_ARGS)
2097 {
2098  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2099  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2100 
2101  PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
2102 }
2103 
2104 Datum
2105 timestamp_ne(PG_FUNCTION_ARGS)
2106 {
2107  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2108  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2109 
2110  PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
2111 }
2112 
2113 Datum
2114 timestamp_lt(PG_FUNCTION_ARGS)
2115 {
2116  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2117  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2118 
2119  PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
2120 }
2121 
2122 Datum
2123 timestamp_gt(PG_FUNCTION_ARGS)
2124 {
2125  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2126  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2127 
2128  PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
2129 }
2130 
2131 Datum
2132 timestamp_le(PG_FUNCTION_ARGS)
2133 {
2134  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2135  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2136 
2137  PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
2138 }
2139 
2140 Datum
2141 timestamp_ge(PG_FUNCTION_ARGS)
2142 {
2143  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2144  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2145 
2146  PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
2147 }
2148 
2149 Datum
2150 timestamp_cmp(PG_FUNCTION_ARGS)
2151 {
2152  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2153  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2154 
2155  PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
2156 }
2157 
2158 /* note: this is used for timestamptz also */
2159 static int
2160 timestamp_fastcmp(Datum x, Datum y, SortSupport ssup)
2161 {
2162  Timestamp a = DatumGetTimestamp(x);
2163  Timestamp b = DatumGetTimestamp(y);
2164 
2165  return timestamp_cmp_internal(a, b);
2166 }
2167 
2168 Datum
2169 timestamp_sortsupport(PG_FUNCTION_ARGS)
2170 {
2171  SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0);
2172 
2173  ssup->comparator = timestamp_fastcmp;
2174  PG_RETURN_VOID();
2175 }
2176 
2177 Datum
2178 timestamp_hash(PG_FUNCTION_ARGS)
2179 {
2180  return hashint8(fcinfo);
2181 }
2182 
2183 Datum
2184 timestamp_hash_extended(PG_FUNCTION_ARGS)
2185 {
2186  return hashint8extended(fcinfo);
2187 }
2188 
2189 /*
2190  * Cross-type comparison functions for timestamp vs timestamptz
2191  */
2192 
2193 int32
2194 timestamp_cmp_timestamptz_internal(Timestamp timestampVal, TimestampTz dt2)
2195 {
2196  TimestampTz dt1;
2197  int overflow;
2198 
2199  dt1 = timestamp2timestamptz_opt_overflow(timestampVal, &overflow);
2200  if (overflow > 0)
2201  {
2202  /* dt1 is larger than any finite timestamp, but less than infinity */
2203  return TIMESTAMP_IS_NOEND(dt2) ? -1 : +1;
2204  }
2205  if (overflow < 0)
2206  {
2207  /* dt1 is less than any finite timestamp, but more than -infinity */
2208  return TIMESTAMP_IS_NOBEGIN(dt2) ? +1 : -1;
2209  }
2210 
2211  return timestamptz_cmp_internal(dt1, dt2);
2212 }
2213 
2214 Datum
2215 timestamp_eq_timestamptz(PG_FUNCTION_ARGS)
2216 {
2217  Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2218  TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2219 
2220  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) == 0);
2221 }
2222 
2223 Datum
2224 timestamp_ne_timestamptz(PG_FUNCTION_ARGS)
2225 {
2226  Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2227  TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2228 
2229  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) != 0);
2230 }
2231 
2232 Datum
2233 timestamp_lt_timestamptz(PG_FUNCTION_ARGS)
2234 {
2235  Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2236  TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2237 
2238  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) < 0);
2239 }
2240 
2241 Datum
2242 timestamp_gt_timestamptz(PG_FUNCTION_ARGS)
2243 {
2244  Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2245  TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2246 
2247  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) > 0);
2248 }
2249 
2250 Datum
2251 timestamp_le_timestamptz(PG_FUNCTION_ARGS)
2252 {
2253  Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2254  TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2255 
2256  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) <= 0);
2257 }
2258 
2259 Datum
2260 timestamp_ge_timestamptz(PG_FUNCTION_ARGS)
2261 {
2262  Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2263  TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2264 
2265  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) >= 0);
2266 }
2267 
2268 Datum
2269 timestamp_cmp_timestamptz(PG_FUNCTION_ARGS)
2270 {
2271  Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2272  TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2273 
2274  PG_RETURN_INT32(timestamp_cmp_timestamptz_internal(timestampVal, dt2));
2275 }
2276 
2277 Datum
2278 timestamptz_eq_timestamp(PG_FUNCTION_ARGS)
2279 {
2280  TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2281  Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2282 
2283  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) == 0);
2284 }
2285 
2286 Datum
2287 timestamptz_ne_timestamp(PG_FUNCTION_ARGS)
2288 {
2289  TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2290  Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2291 
2292  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) != 0);
2293 }
2294 
2295 Datum
2296 timestamptz_lt_timestamp(PG_FUNCTION_ARGS)
2297 {
2298  TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2299  Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2300 
2301  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) > 0);
2302 }
2303 
2304 Datum
2305 timestamptz_gt_timestamp(PG_FUNCTION_ARGS)
2306 {
2307  TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2308  Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2309 
2310  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) < 0);
2311 }
2312 
2313 Datum
2314 timestamptz_le_timestamp(PG_FUNCTION_ARGS)
2315 {
2316  TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2317  Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2318 
2319  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) >= 0);
2320 }
2321 
2322 Datum
2323 timestamptz_ge_timestamp(PG_FUNCTION_ARGS)
2324 {
2325  TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2326  Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2327 
2328  PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) <= 0);
2329 }
2330 
2331 Datum
2332 timestamptz_cmp_timestamp(PG_FUNCTION_ARGS)
2333 {
2334  TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2335  Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2336 
2337  PG_RETURN_INT32(-timestamp_cmp_timestamptz_internal(timestampVal, dt1));
2338 }
2339 
2340 
2341 /*
2342  * interval_relop - is interval1 relop interval2
2343  *
2344  * Interval comparison is based on converting interval values to a linear
2345  * representation expressed in the units of the time field (microseconds,
2346  * in the case of integer timestamps) with days assumed to be always 24 hours
2347  * and months assumed to be always 30 days. To avoid overflow, we need a
2348  * wider-than-int64 datatype for the linear representation, so use INT128.
2349  */
2350 
2351 static inline INT128
2352 interval_cmp_value(const Interval *interval)
2353 {
2354  INT128 span;
2355  int64 dayfraction;
2356  int64 days;
2357 
2358  /*
2359  * Separate time field into days and dayfraction, then add the month and
2360  * day fields to the days part. We cannot overflow int64 days here.
2361  */
2362  dayfraction = interval->time % USECS_PER_DAY;
2363  days = interval->time / USECS_PER_DAY;
2364  days += interval->month * INT64CONST(30);
2365  days += interval->day;
2366 
2367  /* Widen dayfraction to 128 bits */
2368  span = int64_to_int128(dayfraction);
2369 
2370  /* Scale up days to microseconds, forming a 128-bit product */
2371  int128_add_int64_mul_int64(&span, days, USECS_PER_DAY);
2372 
2373  return span;
2374 }
2375 
2376 static int
2377 interval_cmp_internal(Interval *interval1, Interval *interval2)
2378 {
2379  INT128 span1 = interval_cmp_value(interval1);
2380  INT128 span2 = interval_cmp_value(interval2);
2381 
2382  return int128_compare(span1, span2);
2383 }
2384 
2385 Datum
2386 interval_eq(PG_FUNCTION_ARGS)
2387 {
2388  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2389  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2390 
2391  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) == 0);
2392 }
2393 
2394 Datum
2395 interval_ne(PG_FUNCTION_ARGS)
2396 {
2397  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2398  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2399 
2400  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) != 0);
2401 }
2402 
2403 Datum
2404 interval_lt(PG_FUNCTION_ARGS)
2405 {
2406  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2407  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2408 
2409  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) < 0);
2410 }
2411 
2412 Datum
2413 interval_gt(PG_FUNCTION_ARGS)
2414 {
2415  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2416  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2417 
2418  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) > 0);
2419 }
2420 
2421 Datum
2422 interval_le(PG_FUNCTION_ARGS)
2423 {
2424  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2425  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2426 
2427  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) <= 0);
2428 }
2429 
2430 Datum
2431 interval_ge(PG_FUNCTION_ARGS)
2432 {
2433  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2434  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2435 
2436  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) >= 0);
2437 }
2438 
2439 Datum
2440 interval_cmp(PG_FUNCTION_ARGS)
2441 {
2442  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2443  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2444 
2445  PG_RETURN_INT32(interval_cmp_internal(interval1, interval2));
2446 }
2447 
2448 /*
2449  * Hashing for intervals
2450  *
2451  * We must produce equal hashvals for values that interval_cmp_internal()
2452  * considers equal. So, compute the net span the same way it does,
2453  * and then hash that.
2454  */
2455 Datum
2456 interval_hash(PG_FUNCTION_ARGS)
2457 {
2458  Interval *interval = PG_GETARG_INTERVAL_P(0);
2459  INT128 span = interval_cmp_value(interval);
2460  int64 span64;
2461 
2462  /*
2463  * Use only the least significant 64 bits for hashing. The upper 64 bits
2464  * seldom add any useful information, and besides we must do it like this
2465  * for compatibility with hashes calculated before use of INT128 was
2466  * introduced.
2467  */
2468  span64 = int128_to_int64(span);
2469 
2470  return DirectFunctionCall1(hashint8, Int64GetDatumFast(span64));
2471 }
2472 
2473 Datum
2474 interval_hash_extended(PG_FUNCTION_ARGS)
2475 {
2476  Interval *interval = PG_GETARG_INTERVAL_P(0);
2477  INT128 span = interval_cmp_value(interval);
2478  int64 span64;
2479 
2480  /* Same approach as interval_hash */
2481  span64 = int128_to_int64(span);
2482 
2483  return DirectFunctionCall2(hashint8extended, Int64GetDatumFast(span64),
2484  PG_GETARG_DATUM(1));
2485 }
2486 
2487 /* overlaps_timestamp() --- implements the SQL OVERLAPS operator.
2488  *
2489  * Algorithm is per SQL spec. This is much harder than you'd think
2490  * because the spec requires us to deliver a non-null answer in some cases
2491  * where some of the inputs are null.
2492  */
2493 Datum
2494 overlaps_timestamp(PG_FUNCTION_ARGS)
2495 {
2496  /*
2497  * The arguments are Timestamps, but we leave them as generic Datums to
2498  * avoid unnecessary conversions between value and reference forms --- not
2499  * to mention possible dereferences of null pointers.
2500  */
2501  Datum ts1 = PG_GETARG_DATUM(0);
2502  Datum te1 = PG_GETARG_DATUM(1);
2503  Datum ts2 = PG_GETARG_DATUM(2);
2504  Datum te2 = PG_GETARG_DATUM(3);
2505  bool ts1IsNull = PG_ARGISNULL(0);
2506  bool te1IsNull = PG_ARGISNULL(1);
2507  bool ts2IsNull = PG_ARGISNULL(2);
2508  bool te2IsNull = PG_ARGISNULL(3);
2509 
2510 #define TIMESTAMP_GT(t1,t2) \
2511  DatumGetBool(DirectFunctionCall2(timestamp_gt,t1,t2))
2512 #define TIMESTAMP_LT(t1,t2) \
2513  DatumGetBool(DirectFunctionCall2(timestamp_lt,t1,t2))
2514 
2515  /*
2516  * If both endpoints of interval 1 are null, the result is null (unknown).
2517  * If just one endpoint is null, take ts1 as the non-null one. Otherwise,
2518  * take ts1 as the lesser endpoint.
2519  */
2520  if (ts1IsNull)
2521  {
2522  if (te1IsNull)
2523  PG_RETURN_NULL();
2524  /* swap null for non-null */
2525  ts1 = te1;
2526  te1IsNull = true;
2527  }
2528  else if (!te1IsNull)
2529  {
2530  if (TIMESTAMP_GT(ts1, te1))
2531  {
2532  Datum tt = ts1;
2533 
2534  ts1 = te1;
2535  te1 = tt;
2536  }
2537  }
2538 
2539  /* Likewise for interval 2. */
2540  if (ts2IsNull)
2541  {
2542  if (te2IsNull)
2543  PG_RETURN_NULL();
2544  /* swap null for non-null */
2545  ts2 = te2;
2546  te2IsNull = true;
2547  }
2548  else if (!te2IsNull)
2549  {
2550  if (TIMESTAMP_GT(ts2, te2))
2551  {
2552  Datum tt = ts2;
2553 
2554  ts2 = te2;
2555  te2 = tt;
2556  }
2557  }
2558 
2559  /*
2560  * At this point neither ts1 nor ts2 is null, so we can consider three
2561  * cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
2562  */
2563  if (TIMESTAMP_GT(ts1, ts2))
2564  {
2565  /*
2566  * This case is ts1 < te2 OR te1 < te2, which may look redundant but
2567  * in the presence of nulls it's not quite completely so.
2568  */
2569  if (te2IsNull)
2570  PG_RETURN_NULL();
2571  if (TIMESTAMP_LT(ts1, te2))
2572  PG_RETURN_BOOL(true);
2573  if (te1IsNull)
2574  PG_RETURN_NULL();
2575 
2576  /*
2577  * If te1 is not null then we had ts1 <= te1 above, and we just found
2578  * ts1 >= te2, hence te1 >= te2.
2579  */
2580  PG_RETURN_BOOL(false);
2581  }
2582  else if (TIMESTAMP_LT(ts1, ts2))
2583  {
2584  /* This case is ts2 < te1 OR te2 < te1 */
2585  if (te1IsNull)
2586  PG_RETURN_NULL();
2587  if (TIMESTAMP_LT(ts2, te1))
2588  PG_RETURN_BOOL(true);
2589  if (te2IsNull)
2590  PG_RETURN_NULL();
2591 
2592  /*
2593  * If te2 is not null then we had ts2 <= te2 above, and we just found
2594  * ts2 >= te1, hence te2 >= te1.
2595  */
2596  PG_RETURN_BOOL(false);
2597  }
2598  else
2599  {
2600  /*
2601  * For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
2602  * rather silly way of saying "true if both are non-null, else null".
2603  */
2604  if (te1IsNull || te2IsNull)
2605  PG_RETURN_NULL();
2606  PG_RETURN_BOOL(true);
2607  }
2608 
2609 #undef TIMESTAMP_GT
2610 #undef TIMESTAMP_LT
2611 }
2612 
2613 
2614 /*----------------------------------------------------------
2615  * "Arithmetic" operators on date/times.
2616  *---------------------------------------------------------*/
2617 
2618 Datum
2619 timestamp_smaller(PG_FUNCTION_ARGS)
2620 {
2621  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2622  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2623  Timestamp result;
2624 
2625  /* use timestamp_cmp_internal to be sure this agrees with comparisons */
2626  if (timestamp_cmp_internal(dt1, dt2) < 0)
2627  result = dt1;
2628  else
2629  result = dt2;
2630  PG_RETURN_TIMESTAMP(result);
2631 }
2632 
2633 Datum
2634 timestamp_larger(PG_FUNCTION_ARGS)
2635 {
2636  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2637  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2638  Timestamp result;
2639 
2640  if (timestamp_cmp_internal(dt1, dt2) > 0)
2641  result = dt1;
2642  else
2643  result = dt2;
2644  PG_RETURN_TIMESTAMP(result);
2645 }
2646 
2647 
2648 Datum
2649 timestamp_mi(PG_FUNCTION_ARGS)
2650 {
2651  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2652  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2653  Interval *result;
2654 
2655  result = (Interval *) palloc(sizeof(Interval));
2656 
2657  if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
2658  ereport(ERROR,
2659  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2660  errmsg("cannot subtract infinite timestamps")));
2661 
2662  result->time = dt1 - dt2;
2663 
2664  result->month = 0;
2665  result->day = 0;
2666 
2667  /*----------
2668  * This is wrong, but removing it breaks a lot of regression tests.
2669  * For example:
2670  *
2671  * test=> SET timezone = 'EST5EDT';
2672  * test=> SELECT
2673  * test-> ('2005-10-30 13:22:00-05'::timestamptz -
2674  * test(> '2005-10-29 13:22:00-04'::timestamptz);
2675  * ?column?
2676  * ----------------
2677  * 1 day 01:00:00
2678  * (1 row)
2679  *
2680  * so adding that to the first timestamp gets:
2681  *
2682  * test=> SELECT
2683  * test-> ('2005-10-29 13:22:00-04'::timestamptz +
2684  * test(> ('2005-10-30 13:22:00-05'::timestamptz -
2685  * test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
2686  * timezone
2687  * --------------------
2688  * 2005-10-30 14:22:00
2689  * (1 row)
2690  *----------
2691  */
2692  result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours,
2693  IntervalPGetDatum(result)));
2694 
2695  PG_RETURN_INTERVAL_P(result);
2696 }
2697 
2698 /*
2699  * interval_justify_interval()
2700  *
2701  * Adjust interval so 'month', 'day', and 'time' portions are within
2702  * customary bounds. Specifically:
2703  *
2704  * 0 <= abs(time) < 24 hours
2705  * 0 <= abs(day) < 30 days
2706  *
2707  * Also, the sign bit on all three fields is made equal, so either
2708  * all three fields are negative or all are positive.
2709  */
2710 Datum
2711 interval_justify_interval(PG_FUNCTION_ARGS)
2712 {
2713  Interval *span = PG_GETARG_INTERVAL_P(0);
2714  Interval *result;
2715  TimeOffset wholeday;
2716  int32 wholemonth;
2717 
2718  result = (Interval *) palloc(sizeof(Interval));
2719  result->month = span->month;
2720  result->day = span->day;
2721  result->time = span->time;
2722 
2723  TMODULO(result->time, wholeday, USECS_PER_DAY);
2724  result->day += wholeday; /* could overflow... */
2725 
2726  wholemonth = result->day / DAYS_PER_MONTH;
2727  result->day -= wholemonth * DAYS_PER_MONTH;
2728  result->month += wholemonth;
2729 
2730  if (result->month > 0 &&
2731  (result->day < 0 || (result->day == 0 && result->time < 0)))
2732  {
2733  result->day += DAYS_PER_MONTH;
2734  result->month--;
2735  }
2736  else if (result->month < 0 &&
2737  (result->day > 0 || (result->day == 0 && result->time > 0)))
2738  {
2739  result->day -= DAYS_PER_MONTH;
2740  result->month++;
2741  }
2742 
2743  if (result->day > 0 && result->time < 0)
2744  {
2745  result->time += USECS_PER_DAY;
2746  result->day--;
2747  }
2748  else if (result->day < 0 && result->time > 0)
2749  {
2750  result->time -= USECS_PER_DAY;
2751  result->day++;
2752  }
2753 
2754  PG_RETURN_INTERVAL_P(result);
2755 }
2756 
2757 /*
2758  * interval_justify_hours()
2759  *
2760  * Adjust interval so 'time' contains less than a whole day, adding
2761  * the excess to 'day'. This is useful for
2762  * situations (such as non-TZ) where '1 day' = '24 hours' is valid,
2763  * e.g. interval subtraction and division.
2764  */
2765 Datum
2766 interval_justify_hours(PG_FUNCTION_ARGS)
2767 {
2768  Interval *span = PG_GETARG_INTERVAL_P(0);
2769  Interval *result;
2770  TimeOffset wholeday;
2771 
2772  result = (Interval *) palloc(sizeof(Interval));
2773  result->month = span->month;
2774  result->day = span->day;
2775  result->time = span->time;
2776 
2777  TMODULO(result->time, wholeday, USECS_PER_DAY);
2778  result->day += wholeday; /* could overflow... */
2779 
2780  if (result->day > 0 && result->time < 0)
2781  {
2782  result->time += USECS_PER_DAY;
2783  result->day--;
2784  }
2785  else if (result->day < 0 && result->time > 0)
2786  {
2787  result->time -= USECS_PER_DAY;
2788  result->day++;
2789  }
2790 
2791  PG_RETURN_INTERVAL_P(result);
2792 }
2793 
2794 /*
2795  * interval_justify_days()
2796  *
2797  * Adjust interval so 'day' contains less than 30 days, adding
2798  * the excess to 'month'.
2799  */
2800 Datum
2801 interval_justify_days(PG_FUNCTION_ARGS)
2802 {
2803  Interval *span = PG_GETARG_INTERVAL_P(0);
2804  Interval *result;
2805  int32 wholemonth;
2806 
2807  result = (Interval *) palloc(sizeof(Interval));
2808  result->month = span->month;
2809  result->day = span->day;
2810  result->time = span->time;
2811 
2812  wholemonth = result->day / DAYS_PER_MONTH;
2813  result->day -= wholemonth * DAYS_PER_MONTH;
2814  result->month += wholemonth;
2815 
2816  if (result->month > 0 && result->day < 0)
2817  {
2818  result->day += DAYS_PER_MONTH;
2819  result->month--;
2820  }
2821  else if (result->month < 0 && result->day > 0)
2822  {
2823  result->day -= DAYS_PER_MONTH;
2824  result->month++;
2825  }
2826 
2827  PG_RETURN_INTERVAL_P(result);
2828 }
2829 
2830 /* timestamp_pl_interval()
2831  * Add an interval to a timestamp data type.
2832  * Note that interval has provisions for qualitative year/month and day
2833  * units, so try to do the right thing with them.
2834  * To add a month, increment the month, and use the same day of month.
2835  * Then, if the next month has fewer days, set the day of month
2836  * to the last day of month.
2837  * To add a day, increment the mday, and use the same time of day.
2838  * Lastly, add in the "quantitative time".
2839  */
2840 Datum
2841 timestamp_pl_interval(PG_FUNCTION_ARGS)
2842 {
2843  Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
2844  Interval *span = PG_GETARG_INTERVAL_P(1);
2845  Timestamp result;
2846 
2847  if (TIMESTAMP_NOT_FINITE(timestamp))
2848  result = timestamp;
2849  else
2850  {
2851  if (span->month != 0)
2852  {
2853  struct pg_tm tt,
2854  *tm = &tt;
2855  fsec_t fsec;
2856 
2857  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
2858  ereport(ERROR,
2859  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2860  errmsg("timestamp out of range")));
2861 
2862  tm->tm_mon += span->month;
2863  if (tm->tm_mon > MONTHS_PER_YEAR)
2864  {
2865  tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
2866  tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
2867  }
2868  else if (tm->tm_mon < 1)
2869  {
2870  tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
2871  tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR + MONTHS_PER_YEAR;
2872  }
2873 
2874  /* adjust for end of month boundary problems... */
2875  if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
2876  tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
2877 
2878  if (tm2timestamp(tm, fsec, NULL, &timestamp) != 0)
2879  ereport(ERROR,
2880  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2881  errmsg("timestamp out of range")));
2882  }
2883 
2884  if (span->day != 0)
2885  {
2886  struct pg_tm tt,
2887  *tm = &tt;
2888  fsec_t fsec;
2889  int julian;
2890 
2891  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
2892  ereport(ERROR,
2893  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2894  errmsg("timestamp out of range")));
2895 
2896  /* Add days by converting to and from Julian */
2897  julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
2898  j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
2899 
2900  if (tm2timestamp(tm, fsec, NULL, &timestamp) != 0)
2901  ereport(ERROR,
2902  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2903  errmsg("timestamp out of range")));
2904  }
2905 
2906  timestamp += span->time;
2907 
2908  if (!IS_VALID_TIMESTAMP(timestamp))
2909  ereport(ERROR,
2910  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2911  errmsg("timestamp out of range")));
2912 
2913  result = timestamp;
2914  }
2915 
2916  PG_RETURN_TIMESTAMP(result);
2917 }
2918 
2919 Datum
2920 timestamp_mi_interval(PG_FUNCTION_ARGS)
2921 {
2922  Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
2923  Interval *span = PG_GETARG_INTERVAL_P(1);
2924  Interval tspan;
2925 
2926  tspan.month = -span->month;
2927  tspan.day = -span->day;
2928  tspan.time = -span->time;
2929 
2930  return DirectFunctionCall2(timestamp_pl_interval,
2931  TimestampGetDatum(timestamp),
2932  PointerGetDatum(&tspan));
2933 }
2934 
2935 
2936 /* timestamptz_pl_interval()
2937  * Add an interval to a timestamp with time zone data type.
2938  * Note that interval has provisions for qualitative year/month
2939  * units, so try to do the right thing with them.
2940  * To add a month, increment the month, and use the same day of month.
2941  * Then, if the next month has fewer days, set the day of month
2942  * to the last day of month.
2943  * Lastly, add in the "quantitative time".
2944  */
2945 Datum
2946 timestamptz_pl_interval(PG_FUNCTION_ARGS)
2947 {
2948  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
2949  Interval *span = PG_GETARG_INTERVAL_P(1);
2950  TimestampTz result;
2951  int tz;
2952 
2953  if (TIMESTAMP_NOT_FINITE(timestamp))
2954  result = timestamp;
2955  else
2956  {
2957  if (span->month != 0)
2958  {
2959  struct pg_tm tt,
2960  *tm = &tt;
2961  fsec_t fsec;
2962 
2963  if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
2964  ereport(ERROR,
2965  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2966  errmsg("timestamp out of range")));
2967 
2968  tm->tm_mon += span->month;
2969  if (tm->tm_mon > MONTHS_PER_YEAR)
2970  {
2971  tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
2972  tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
2973  }
2974  else if (tm->tm_mon < 1)
2975  {
2976  tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
2977  tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR + MONTHS_PER_YEAR;
2978  }
2979 
2980  /* adjust for end of month boundary problems... */
2981  if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
2982  tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
2983 
2984  tz = DetermineTimeZoneOffset(tm, session_timezone);
2985 
2986  if (tm2timestamp(tm, fsec, &tz, &timestamp) != 0)
2987  ereport(ERROR,
2988  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2989  errmsg("timestamp out of range")));
2990  }
2991 
2992  if (span->day != 0)
2993  {
2994  struct pg_tm tt,
2995  *tm = &tt;
2996  fsec_t fsec;
2997  int julian;
2998 
2999  if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
3000  ereport(ERROR,
3001  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3002  errmsg("timestamp out of range")));
3003 
3004  /* Add days by converting to and from Julian */
3005  julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
3006  j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
3007 
3008  tz = DetermineTimeZoneOffset(tm, session_timezone);
3009 
3010  if (tm2timestamp(tm, fsec, &tz, &timestamp) != 0)
3011  ereport(ERROR,
3012  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3013  errmsg("timestamp out of range")));
3014  }
3015 
3016  timestamp += span->time;
3017 
3018  if (!IS_VALID_TIMESTAMP(timestamp))
3019  ereport(ERROR,
3020  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3021  errmsg("timestamp out of range")));
3022 
3023  result = timestamp;
3024  }
3025 
3026  PG_RETURN_TIMESTAMP(result);
3027 }
3028 
3029 Datum
3030 timestamptz_mi_interval(PG_FUNCTION_ARGS)
3031 {
3032  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
3033  Interval *span = PG_GETARG_INTERVAL_P(1);
3034  Interval tspan;
3035 
3036  tspan.month = -span->month;
3037  tspan.day = -span->day;
3038  tspan.time = -span->time;
3039 
3040  return DirectFunctionCall2(timestamptz_pl_interval,
3041  TimestampGetDatum(timestamp),
3042  PointerGetDatum(&tspan));
3043 }
3044 
3045 
3046 Datum
3047 interval_um(PG_FUNCTION_ARGS)
3048 {
3049  Interval *interval = PG_GETARG_INTERVAL_P(0);
3050  Interval *result;
3051 
3052  result = (Interval *) palloc(sizeof(Interval));
3053 
3054  result->time = -interval->time;
3055  /* overflow check copied from int4um */
3056  if (interval->time != 0 && SAMESIGN(result->time, interval->time))
3057  ereport(ERROR,
3058  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3059  errmsg("interval out of range")));
3060  result->day = -interval->day;
3061  if (interval->day != 0 && SAMESIGN(result->day, interval->day))
3062  ereport(ERROR,
3063  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3064  errmsg("interval out of range")));
3065  result->month = -interval->month;
3066  if (interval->month != 0 && SAMESIGN(result->month, interval->month))
3067  ereport(ERROR,
3068  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3069  errmsg("interval out of range")));
3070 
3071  PG_RETURN_INTERVAL_P(result);
3072 }
3073 
3074 
3075 Datum
3076 interval_smaller(PG_FUNCTION_ARGS)
3077 {
3078  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
3079  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
3080  Interval *result;
3081 
3082  /* use interval_cmp_internal to be sure this agrees with comparisons */
3083  if (interval_cmp_internal(interval1, interval2) < 0)
3084  result = interval1;
3085  else
3086  result = interval2;
3087  PG_RETURN_INTERVAL_P(result);
3088 }
3089 
3090 Datum
3091 interval_larger(PG_FUNCTION_ARGS)
3092 {
3093  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
3094  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
3095  Interval *result;
3096 
3097  if (interval_cmp_internal(interval1, interval2) > 0)
3098  result = interval1;
3099  else
3100  result = interval2;
3101  PG_RETURN_INTERVAL_P(result);
3102 }
3103 
3104 Datum
3105 interval_pl(PG_FUNCTION_ARGS)
3106 {
3107  Interval *span1 = PG_GETARG_INTERVAL_P(0);
3108  Interval *span2 = PG_GETARG_INTERVAL_P(1);
3109  Interval *result;
3110 
3111  result = (Interval *) palloc(sizeof(Interval));
3112 
3113  result->month = span1->month + span2->month;
3114  /* overflow check copied from int4pl */
3115  if (SAMESIGN(span1->month, span2->month) &&
3116  !SAMESIGN(result->month, span1->month))
3117  ereport(ERROR,
3118  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3119  errmsg("interval out of range")));
3120 
3121  result->day = span1->day + span2->day;
3122  if (SAMESIGN(span1->day, span2->day) &&
3123  !SAMESIGN(result->day, span1->day))
3124  ereport(ERROR,
3125  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3126  errmsg("interval out of range")));
3127 
3128  result->time = span1->time + span2->time;
3129  if (SAMESIGN(span1->time, span2->time) &&
3130  !SAMESIGN(result->time, span1->time))
3131  ereport(ERROR,
3132  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3133  errmsg("interval out of range")));
3134 
3135  PG_RETURN_INTERVAL_P(result);
3136 }
3137 
3138 Datum
3139 interval_mi(PG_FUNCTION_ARGS)
3140 {
3141  Interval *span1 = PG_GETARG_INTERVAL_P(0);
3142  Interval *span2 = PG_GETARG_INTERVAL_P(1);
3143  Interval *result;
3144 
3145  result = (Interval *) palloc(sizeof(Interval));
3146 
3147  result->month = span1->month - span2->month;
3148  /* overflow check copied from int4mi */
3149  if (!SAMESIGN(span1->month, span2->month) &&
3150  !SAMESIGN(result->month, span1->month))
3151  ereport(ERROR,
3152  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3153  errmsg("interval out of range")));
3154 
3155  result->day = span1->day - span2->day;
3156  if (!SAMESIGN(span1->day, span2->day) &&
3157  !SAMESIGN(result->day, span1->day))
3158  ereport(ERROR,
3159  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3160  errmsg("interval out of range")));
3161 
3162  result->time = span1->time - span2->time;
3163  if (!SAMESIGN(span1->time, span2->time) &&
3164  !SAMESIGN(result->time, span1->time))
3165  ereport(ERROR,
3166  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3167  errmsg("interval out of range")));
3168 
3169  PG_RETURN_INTERVAL_P(result);
3170 }
3171 
3172 /*
3173  * There is no interval_abs(): it is unclear what value to return:
3174  * http://archives.postgresql.org/pgsql-general/2009-10/msg01031.php
3175  * http://archives.postgresql.org/pgsql-general/2009-11/msg00041.php
3176  */
3177 
3178 Datum
3179 interval_mul(PG_FUNCTION_ARGS)
3180 {
3181  Interval *span = PG_GETARG_INTERVAL_P(0);
3182  float8 factor = PG_GETARG_FLOAT8(1);
3183  double month_remainder_days,
3184  sec_remainder,
3185  result_double;
3186  int32 orig_month = span->month,
3187  orig_day = span->day;
3188  Interval *result;
3189 
3190  result = (Interval *) palloc(sizeof(Interval));
3191 
3192  result_double = span->month * factor;
3193  if (isnan(result_double) ||
3194  result_double > INT_MAX || result_double < INT_MIN)
3195  ereport(ERROR,
3196  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3197  errmsg("interval out of range")));
3198  result->month = (int32) result_double;
3199 
3200  result_double = span->day * factor;
3201  if (isnan(result_double) ||
3202  result_double > INT_MAX || result_double < INT_MIN)
3203  ereport(ERROR,
3204  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3205  errmsg("interval out of range")));
3206  result->day = (int32) result_double;
3207 
3208  /*
3209  * The above correctly handles the whole-number part of the month and day
3210  * products, but we have to do something with any fractional part
3211  * resulting when the factor is non-integral. We cascade the fractions
3212  * down to lower units using the conversion factors DAYS_PER_MONTH and
3213  * SECS_PER_DAY. Note we do NOT cascade up, since we are not forced to do
3214  * so by the representation. The user can choose to cascade up later,
3215  * using justify_hours and/or justify_days.
3216  */
3217 
3218  /*
3219  * Fractional months full days into days.
3220  *
3221  * Floating point calculation are inherently imprecise, so these
3222  * calculations are crafted to produce the most reliable result possible.
3223  * TSROUND() is needed to more accurately produce whole numbers where
3224  * appropriate.
3225  */
3226  month_remainder_days = (orig_month * factor - result->month) * DAYS_PER_MONTH;
3227  month_remainder_days = TSROUND(month_remainder_days);
3228  sec_remainder = (orig_day * factor - result->day +
3229  month_remainder_days - (int) month_remainder_days) * SECS_PER_DAY;
3230  sec_remainder = TSROUND(sec_remainder);
3231 
3232  /*
3233  * Might have 24:00:00 hours due to rounding, or >24 hours because of time
3234  * cascade from months and days. It might still be >24 if the combination
3235  * of cascade and the seconds factor operation itself.
3236  */
3237  if (Abs(sec_remainder) >= SECS_PER_DAY)
3238  {
3239  result->day += (int) (sec_remainder / SECS_PER_DAY);
3240  sec_remainder -= (int) (sec_remainder / SECS_PER_DAY) * SECS_PER_DAY;
3241  }
3242 
3243  /* cascade units down */
3244  result->day += (int32) month_remainder_days;
3245  result_double = rint(span->time * factor + sec_remainder * USECS_PER_SEC);
3246  if (isnan(result_double) || !FLOAT8_FITS_IN_INT64(result_double))
3247  ereport(ERROR,
3248  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3249  errmsg("interval out of range")));
3250  result->time = (int64) result_double;
3251 
3252  PG_RETURN_INTERVAL_P(result);
3253 }
3254 
3255 Datum
3256 mul_d_interval(PG_FUNCTION_ARGS)
3257 {
3258  /* Args are float8 and Interval *, but leave them as generic Datum */
3259  Datum factor = PG_GETARG_DATUM(0);
3260  Datum span = PG_GETARG_DATUM(1);
3261 
3262  return DirectFunctionCall2(interval_mul, span, factor);
3263 }
3264 
3265 Datum
3266 interval_div(PG_FUNCTION_ARGS)
3267 {
3268  Interval *span = PG_GETARG_INTERVAL_P(0);
3269  float8 factor = PG_GETARG_FLOAT8(1);
3270  double month_remainder_days,
3271  sec_remainder;
3272  int32 orig_month = span->month,
3273  orig_day = span->day;
3274  Interval *result;
3275 
3276  result = (Interval *) palloc(sizeof(Interval));
3277 
3278  if (factor == 0.0)
3279  ereport(ERROR,
3280  (errcode(ERRCODE_DIVISION_BY_ZERO),
3281  errmsg("division by zero")));
3282 
3283  result->month = (int32) (span->month / factor);
3284  result->day = (int32) (span->day / factor);
3285 
3286  /*
3287  * Fractional months full days into days. See comment in interval_mul().
3288  */
3289  month_remainder_days = (orig_month / factor - result->month) * DAYS_PER_MONTH;
3290  month_remainder_days = TSROUND(month_remainder_days);
3291  sec_remainder = (orig_day / factor - result->day +
3292  month_remainder_days - (int) month_remainder_days) * SECS_PER_DAY;
3293  sec_remainder = TSROUND(sec_remainder);
3294  if (Abs(sec_remainder) >= SECS_PER_DAY)
3295  {
3296  result->day += (int) (sec_remainder / SECS_PER_DAY);
3297  sec_remainder -= (int) (sec_remainder / SECS_PER_DAY) * SECS_PER_DAY;
3298  }
3299 
3300  /* cascade units down */
3301  result->day += (int32) month_remainder_days;
3302  result->time = rint(span->time / factor + sec_remainder * USECS_PER_SEC);
3303 
3304  PG_RETURN_INTERVAL_P(result);
3305 }
3306 
3307 
3308 /*
3309  * in_range support functions for timestamps and intervals.
3310  *
3311  * Per SQL spec, we support these with interval as the offset type.
3312  * The spec's restriction that the offset not be negative is a bit hard to
3313  * decipher for intervals, but we choose to interpret it the same as our
3314  * interval comparison operators would.
3315  */
3316 
3317 Datum
3318 in_range_timestamptz_interval(PG_FUNCTION_ARGS)
3319 {
3320  TimestampTz val = PG_GETARG_TIMESTAMPTZ(0);
3321  TimestampTz base = PG_GETARG_TIMESTAMPTZ(1);
3322  Interval *offset = PG_GETARG_INTERVAL_P(2);
3323  bool sub = PG_GETARG_BOOL(3);
3324  bool less = PG_GETARG_BOOL(4);
3325  TimestampTz sum;
3326 
3327  if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0)
3328  ereport(ERROR,
3329  (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3330  errmsg("invalid preceding or following size in window function")));
3331 
3332  /* We don't currently bother to avoid overflow hazards here */
3333  if (sub)
3334  sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_mi_interval,
3335  TimestampTzGetDatum(base),
3336  IntervalPGetDatum(offset)));
3337  else
3338  sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval,
3339  TimestampTzGetDatum(base),
3340  IntervalPGetDatum(offset)));
3341 
3342  if (less)
3343  PG_RETURN_BOOL(val <= sum);
3344  else
3345  PG_RETURN_BOOL(val >= sum);
3346 }
3347 
3348 Datum
3349 in_range_timestamp_interval(PG_FUNCTION_ARGS)
3350 {
3351  Timestamp val = PG_GETARG_TIMESTAMP(0);
3352  Timestamp base = PG_GETARG_TIMESTAMP(1);
3353  Interval *offset = PG_GETARG_INTERVAL_P(2);
3354  bool sub = PG_GETARG_BOOL(3);
3355  bool less = PG_GETARG_BOOL(4);
3356  Timestamp sum;
3357 
3358  if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0)
3359  ereport(ERROR,
3360  (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3361  errmsg("invalid preceding or following size in window function")));
3362 
3363  /* We don't currently bother to avoid overflow hazards here */
3364  if (sub)
3365  sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_mi_interval,
3366  TimestampGetDatum(base),
3367  IntervalPGetDatum(offset)));
3368  else
3369  sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_pl_interval,
3370  TimestampGetDatum(base),
3371  IntervalPGetDatum(offset)));
3372 
3373  if (less)
3374  PG_RETURN_BOOL(val <= sum);
3375  else
3376  PG_RETURN_BOOL(val >= sum);
3377 }
3378 
3379 Datum
3380 in_range_interval_interval(PG_FUNCTION_ARGS)
3381 {
3382  Interval *val = PG_GETARG_INTERVAL_P(0);
3383  Interval *base = PG_GETARG_INTERVAL_P(1);
3384  Interval *offset = PG_GETARG_INTERVAL_P(2);
3385  bool sub = PG_GETARG_BOOL(3);
3386  bool less = PG_GETARG_BOOL(4);
3387  Interval *sum;
3388 
3389  if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0)
3390  ereport(ERROR,
3391  (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3392  errmsg("invalid preceding or following size in window function")));
3393 
3394  /* We don't currently bother to avoid overflow hazards here */
3395  if (sub)
3396  sum = DatumGetIntervalP(DirectFunctionCall2(interval_mi,
3397  IntervalPGetDatum(base),
3398  IntervalPGetDatum(offset)));
3399  else
3400  sum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3401  IntervalPGetDatum(base),
3402  IntervalPGetDatum(offset)));
3403 
3404  if (less)
3405  PG_RETURN_BOOL(interval_cmp_internal(val, sum) <= 0);
3406  else
3407  PG_RETURN_BOOL(interval_cmp_internal(val, sum) >= 0);
3408 }
3409 
3410 
3411 /*
3412  * interval_accum, interval_accum_inv, and interval_avg implement the
3413  * AVG(interval) aggregate.
3414  *
3415  * The transition datatype for this aggregate is a 2-element array of
3416  * intervals, where the first is the running sum and the second contains
3417  * the number of values so far in its 'time' field. This is a bit ugly
3418  * but it beats inventing a specialized datatype for the purpose.
3419  */
3420 
3421 Datum
3422 interval_accum(PG_FUNCTION_ARGS)
3423 {
3424  ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
3425  Interval *newval = PG_GETARG_INTERVAL_P(1);
3426  Datum *transdatums;
3427  int ndatums;
3428  Interval sumX,
3429  N;
3430  Interval *newsum;
3431  ArrayType *result;
3432 
3433  deconstruct_array(transarray,
3434  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE,
3435  &transdatums, NULL, &ndatums);
3436  if (ndatums != 2)
3437  elog(ERROR, "expected 2-element interval array");
3438 
3439  sumX = *(DatumGetIntervalP(transdatums[0]));
3440  N = *(DatumGetIntervalP(transdatums[1]));
3441 
3442  newsum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3443  IntervalPGetDatum(&sumX),
3444  IntervalPGetDatum(newval)));
3445  N.time += 1;
3446 
3447  transdatums[0] = IntervalPGetDatum(newsum);
3448  transdatums[1] = IntervalPGetDatum(&N);
3449 
3450  result = construct_array(transdatums, 2,
3451  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE);
3452 
3453  PG_RETURN_ARRAYTYPE_P(result);
3454 }
3455 
3456 Datum
3457 interval_combine(PG_FUNCTION_ARGS)
3458 {
3459  ArrayType *transarray1 = PG_GETARG_ARRAYTYPE_P(0);
3460  ArrayType *transarray2 = PG_GETARG_ARRAYTYPE_P(1);
3461  Datum *transdatums1;
3462  Datum *transdatums2;
3463  int ndatums1;
3464  int ndatums2;
3465  Interval sum1,
3466  N1;
3467  Interval sum2,
3468  N2;
3469 
3470  Interval *newsum;
3471  ArrayType *result;
3472 
3473  deconstruct_array(transarray1,
3474  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE,
3475  &transdatums1, NULL, &ndatums1);
3476  if (ndatums1 != 2)
3477  elog(ERROR, "expected 2-element interval array");
3478 
3479  sum1 = *(DatumGetIntervalP(transdatums1[0]));
3480  N1 = *(DatumGetIntervalP(transdatums1[1]));
3481 
3482  deconstruct_array(transarray2,
3483  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE,
3484  &transdatums2, NULL, &ndatums2);
3485  if (ndatums2 != 2)
3486  elog(ERROR, "expected 2-element interval array");
3487 
3488  sum2 = *(DatumGetIntervalP(transdatums2[0]));
3489  N2 = *(DatumGetIntervalP(transdatums2[1]));
3490 
3491  newsum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3492  IntervalPGetDatum(&sum1),
3493  IntervalPGetDatum(&sum2)));
3494  N1.time += N2.time;
3495 
3496  transdatums1[0] = IntervalPGetDatum(newsum);
3497  transdatums1[1] = IntervalPGetDatum(&N1);
3498 
3499  result = construct_array(transdatums1, 2,
3500  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE);
3501 
3502  PG_RETURN_ARRAYTYPE_P(result);
3503 }
3504 
3505 Datum
3506 interval_accum_inv(PG_FUNCTION_ARGS)
3507 {
3508  ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
3509  Interval *newval = PG_GETARG_INTERVAL_P(1);
3510  Datum *transdatums;
3511  int ndatums;
3512  Interval sumX,
3513  N;
3514  Interval *newsum;
3515  ArrayType *result;
3516 
3517  deconstruct_array(transarray,
3518  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE,
3519  &transdatums, NULL, &ndatums);
3520  if (ndatums != 2)
3521  elog(ERROR, "expected 2-element interval array");
3522 
3523  sumX = *(DatumGetIntervalP(transdatums[0]));
3524  N = *(DatumGetIntervalP(transdatums[1]));
3525 
3526  newsum = DatumGetIntervalP(DirectFunctionCall2(interval_mi,
3527  IntervalPGetDatum(&sumX),
3528  IntervalPGetDatum(newval)));
3529  N.time -= 1;
3530 
3531  transdatums[0] = IntervalPGetDatum(newsum);
3532  transdatums[1] = IntervalPGetDatum(&N);
3533 
3534  result = construct_array(transdatums, 2,
3535  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE);
3536 
3537  PG_RETURN_ARRAYTYPE_P(result);
3538 }
3539 
3540 Datum
3541 interval_avg(PG_FUNCTION_ARGS)
3542 {
3543  ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
3544  Datum *transdatums;
3545  int ndatums;
3546  Interval sumX,
3547  N;
3548 
3549  deconstruct_array(transarray,
3550  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE,
3551  &transdatums, NULL, &ndatums);
3552  if (ndatums != 2)
3553  elog(ERROR, "expected 2-element interval array");
3554 
3555  sumX = *(DatumGetIntervalP(transdatums[0]));
3556  N = *(DatumGetIntervalP(transdatums[1]));
3557 
3558  /* SQL defines AVG of no values to be NULL */
3559  if (N.time == 0)
3560  PG_RETURN_NULL();
3561 
3562  return DirectFunctionCall2(interval_div,
3563  IntervalPGetDatum(&sumX),
3564  Float8GetDatum((double) N.time));
3565 }
3566 
3567 
3568 /* timestamp_age()
3569  * Calculate time difference while retaining year/month fields.
3570  * Note that this does not result in an accurate absolute time span
3571  * since year and month are out of context once the arithmetic
3572  * is done.
3573  */
3574 Datum
3575 timestamp_age(PG_FUNCTION_ARGS)
3576 {
3577  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
3578  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
3579  Interval *result;
3580  fsec_t fsec,
3581  fsec1,
3582  fsec2;
3583  struct pg_tm tt,
3584  *tm = &tt;
3585  struct pg_tm tt1,
3586  *tm1 = &tt1;
3587  struct pg_tm tt2,
3588  *tm2 = &tt2;
3589 
3590  result = (Interval *) palloc(sizeof(Interval));
3591 
3592  if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 &&
3593  timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
3594  {
3595  /* form the symbolic difference */
3596  fsec = fsec1 - fsec2;
3597  tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
3598  tm->tm_min = tm1->tm_min - tm2->tm_min;
3599  tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
3600  tm->tm_mday = tm1->tm_mday - tm2->tm_mday;
3601  tm->tm_mon = tm1->tm_mon - tm2->tm_mon;
3602  tm->tm_year = tm1->tm_year - tm2->tm_year;
3603 
3604  /* flip sign if necessary... */
3605  if (dt1 < dt2)
3606  {
3607  fsec = -fsec;
3608  tm->tm_sec = -tm->tm_sec;
3609  tm->tm_min = -tm->tm_min;
3610  tm->tm_hour = -tm->tm_hour;
3611  tm->tm_mday = -tm->tm_mday;
3612  tm->tm_mon = -tm->tm_mon;
3613  tm->tm_year = -tm->tm_year;
3614  }
3615 
3616  /* propagate any negative fields into the next higher field */
3617  while (fsec < 0)
3618  {
3619  fsec += USECS_PER_SEC;
3620  tm->tm_sec--;
3621  }
3622 
3623  while (tm->tm_sec < 0)
3624  {
3625  tm->tm_sec += SECS_PER_MINUTE;
3626  tm->tm_min--;
3627  }
3628 
3629  while (tm->tm_min < 0)
3630  {
3631  tm->tm_min += MINS_PER_HOUR;
3632  tm->tm_hour--;
3633  }
3634 
3635  while (tm->tm_hour < 0)
3636  {
3637  tm->tm_hour += HOURS_PER_DAY;
3638  tm->tm_mday--;
3639  }
3640 
3641  while (tm->tm_mday < 0)
3642  {
3643  if (dt1 < dt2)
3644  {
3645  tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
3646  tm->tm_mon--;
3647  }
3648  else
3649  {
3650  tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
3651  tm->tm_mon--;
3652  }
3653  }
3654 
3655  while (tm->tm_mon < 0)
3656  {
3657  tm->tm_mon += MONTHS_PER_YEAR;
3658  tm->tm_year--;
3659  }
3660 
3661  /* recover sign if necessary... */
3662  if (dt1 < dt2)
3663  {
3664  fsec = -fsec;
3665  tm->tm_sec = -tm->tm_sec;
3666  tm->tm_min = -tm->tm_min;
3667  tm->tm_hour = -tm->tm_hour;
3668  tm->tm_mday = -tm->tm_mday;
3669  tm->tm_mon = -tm->tm_mon;
3670  tm->tm_year = -tm->tm_year;
3671  }
3672 
3673  if (tm2interval(tm, fsec, result) != 0)
3674  ereport(ERROR,
3675  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3676  errmsg("interval out of range")));
3677  }
3678  else
3679  ereport(ERROR,
3680  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3681  errmsg("timestamp out of range")));
3682 
3683  PG_RETURN_INTERVAL_P(result);
3684 }
3685 
3686 
3687 /* timestamptz_age()
3688  * Calculate time difference while retaining year/month fields.
3689  * Note that this does not result in an accurate absolute time span
3690  * since year and month are out of context once the arithmetic
3691  * is done.
3692  */
3693 Datum
3694 timestamptz_age(PG_FUNCTION_ARGS)
3695 {
3696  TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
3697  TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
3698  Interval *result;
3699  fsec_t fsec,
3700  fsec1,
3701  fsec2;
3702  struct pg_tm tt,
3703  *tm = &tt;
3704  struct pg_tm tt1,
3705  *tm1 = &tt1;
3706  struct pg_tm tt2,
3707  *tm2 = &tt2;
3708  int tz1;
3709  int tz2;
3710 
3711  result = (Interval *) palloc(sizeof(Interval));
3712 
3713  if (timestamp2tm(dt1, &tz1, tm1, &fsec1, NULL, NULL) == 0 &&
3714  timestamp2tm(dt2, &tz2, tm2, &fsec2, NULL, NULL) == 0)
3715  {
3716  /* form the symbolic difference */
3717  fsec = fsec1 - fsec2;
3718  tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
3719  tm->tm_min = tm1->tm_min - tm2->tm_min;
3720  tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
3721  tm->tm_mday = tm1->tm_mday - tm2->tm_mday;
3722  tm->tm_mon = tm1->tm_mon - tm2->tm_mon;
3723  tm->tm_year = tm1->tm_year - tm2->tm_year;
3724 
3725  /* flip sign if necessary... */
3726  if (dt1 < dt2)
3727  {
3728  fsec = -fsec;
3729  tm->tm_sec = -tm->tm_sec;
3730  tm->tm_min = -tm->tm_min;
3731  tm->tm_hour = -tm->tm_hour;
3732  tm->tm_mday = -tm->tm_mday;
3733  tm->tm_mon = -tm->tm_mon;
3734  tm->tm_year = -tm->tm_year;
3735  }
3736 
3737  /* propagate any negative fields into the next higher field */
3738  while (fsec < 0)
3739  {
3740  fsec += USECS_PER_SEC;
3741  tm->tm_sec--;
3742  }
3743 
3744  while (tm->tm_sec < 0)
3745  {
3746  tm->tm_sec += SECS_PER_MINUTE;
3747  tm->tm_min--;
3748  }
3749 
3750  while (tm->tm_min < 0)
3751  {
3752  tm->tm_min += MINS_PER_HOUR;
3753  tm->tm_hour--;
3754  }
3755 
3756  while (tm->tm_hour < 0)
3757  {
3758  tm->tm_hour += HOURS_PER_DAY;
3759  tm->tm_mday--;
3760  }
3761 
3762  while (tm->tm_mday < 0)
3763  {
3764  if (dt1 < dt2)
3765  {
3766  tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
3767  tm->tm_mon--;
3768  }
3769  else
3770  {
3771  tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
3772  tm->tm_mon--;
3773  }
3774  }
3775 
3776  while (tm->tm_mon < 0)
3777  {
3778  tm->tm_mon += MONTHS_PER_YEAR;
3779  tm->tm_year--;
3780  }
3781 
3782  /*
3783  * Note: we deliberately ignore any difference between tz1 and tz2.
3784  */
3785 
3786  /* recover sign if necessary... */
3787  if (dt1 < dt2)
3788  {
3789  fsec = -fsec;
3790  tm->tm_sec = -tm->tm_sec;
3791  tm->tm_min = -tm->tm_min;
3792  tm->tm_hour = -tm->tm_hour;
3793  tm->tm_mday = -tm->tm_mday;
3794  tm->tm_mon = -tm->tm_mon;
3795  tm->tm_year = -tm->tm_year;
3796  }
3797 
3798  if (tm2interval(tm, fsec, result) != 0)
3799  ereport(ERROR,
3800  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3801  errmsg("interval out of range")));
3802  }
3803  else
3804  ereport(ERROR,
3805  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3806  errmsg("timestamp out of range")));
3807 
3808  PG_RETURN_INTERVAL_P(result);
3809 }
3810 
3811 
3812 /*----------------------------------------------------------
3813  * Conversion operators.
3814  *---------------------------------------------------------*/
3815 
3816 
3817 /* timestamp_bin()
3818  * Bin timestamp into specified interval.
3819  */
3820 Datum
3821 timestamp_bin(PG_FUNCTION_ARGS)
3822 {
3823  Interval *stride = PG_GETARG_INTERVAL_P(0);
3824  Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
3825  Timestamp origin = PG_GETARG_TIMESTAMP(2);
3826  Timestamp result,
3827  tm_diff,
3828  stride_usecs,
3829  tm_delta;
3830 
3831  if (TIMESTAMP_NOT_FINITE(timestamp))
3832  PG_RETURN_TIMESTAMP(timestamp);
3833 
3834  if (TIMESTAMP_NOT_FINITE(origin))
3835  ereport(ERROR,
3836  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3837  errmsg("origin out of range")));
3838 
3839  if (stride->month != 0)
3840  ereport(ERROR,
3841  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3842  errmsg("timestamps cannot be binned into intervals containing months or years")));
3843 
3844  stride_usecs = stride->day * USECS_PER_DAY + stride->time;
3845 
3846  if (stride_usecs <= 0)
3847  ereport(ERROR,
3848  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3849  errmsg("stride must be greater than zero")));
3850 
3851  tm_diff = timestamp - origin;
3852  tm_delta = tm_diff - tm_diff % stride_usecs;
3853 
3854  /*
3855  * Make sure the returned timestamp is at the start of the bin, even if
3856  * the origin is in the future.
3857  */
3858  if (origin > timestamp && stride_usecs > 1)
3859  tm_delta -= stride_usecs;
3860 
3861  result = origin + tm_delta;
3862 
3863  PG_RETURN_TIMESTAMP(result);
3864 }
3865 
3866 /* timestamp_trunc()
3867  * Truncate timestamp to specified units.
3868  */
3869 Datum
3870 timestamp_trunc(PG_FUNCTION_ARGS)
3871 {
3872  text *units = PG_GETARG_TEXT_PP(0);
3873  Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
3874  Timestamp result;
3875  int type,
3876  val;
3877  char *lowunits;
3878  fsec_t fsec;
3879  struct pg_tm tt,
3880  *tm = &tt;
3881 
3882  if (TIMESTAMP_NOT_FINITE(timestamp))
3883  PG_RETURN_TIMESTAMP(timestamp);
3884 
3885  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
3886  VARSIZE_ANY_EXHDR(units),
3887  false);
3888 
3889  type = DecodeUnits(0, lowunits, &val);
3890 
3891  if (type == UNITS)
3892  {
3893  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
3894  ereport(ERROR,
3895  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3896  errmsg("timestamp out of range")));
3897 
3898  switch (val)
3899  {
3900  case DTK_WEEK:
3901  {
3902  int woy;
3903 
3904  woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
3905 
3906  /*
3907  * If it is week 52/53 and the month is January, then the
3908  * week must belong to the previous year. Also, some
3909  * December dates belong to the next year.
3910  */
3911  if (woy >= 52 && tm->tm_mon == 1)
3912  --tm->tm_year;
3913  if (woy <= 1 && tm->tm_mon == MONTHS_PER_YEAR)
3914  ++tm->tm_year;
3915  isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
3916  tm->tm_hour = 0;
3917  tm->tm_min = 0;
3918  tm->tm_sec = 0;
3919  fsec = 0;
3920  break;
3921  }
3922  case DTK_MILLENNIUM:
3923  /* see comments in timestamptz_trunc */
3924  if (tm->tm_year > 0)
3925  tm->tm_year = ((tm->tm_year + 999) / 1000) * 1000 - 999;
3926  else
3927  tm->tm_year = -((999 - (tm->tm_year - 1)) / 1000) * 1000 + 1;
3928  /* FALL THRU */
3929  case DTK_CENTURY:
3930  /* see comments in timestamptz_trunc */
3931  if (tm->tm_year > 0)
3932  tm->tm_year = ((tm->tm_year + 99) / 100) * 100 - 99;
3933  else
3934  tm->tm_year = -((99 - (tm->tm_year - 1)) / 100) * 100 + 1;
3935  /* FALL THRU */
3936  case DTK_DECADE:
3937  /* see comments in timestamptz_trunc */
3938  if (val != DTK_MILLENNIUM && val != DTK_CENTURY)
3939  {
3940  if (tm->tm_year > 0)
3941  tm->tm_year = (tm->tm_year / 10) * 10;
3942  else
3943  tm->tm_year = -((8 - (tm->tm_year - 1)) / 10) * 10;
3944  }
3945  /* FALL THRU */
3946  case DTK_YEAR:
3947  tm->tm_mon = 1;
3948  /* FALL THRU */
3949  case DTK_QUARTER:
3950  tm->tm_mon = (3 * ((tm->tm_mon - 1) / 3)) + 1;
3951  /* FALL THRU */
3952  case DTK_MONTH:
3953  tm->tm_mday = 1;
3954  /* FALL THRU */
3955  case DTK_DAY:
3956  tm->tm_hour = 0;
3957  /* FALL THRU */
3958  case DTK_HOUR:
3959  tm->tm_min = 0;
3960  /* FALL THRU */
3961  case DTK_MINUTE:
3962  tm->tm_sec = 0;
3963  /* FALL THRU */
3964  case DTK_SECOND:
3965  fsec = 0;
3966  break;
3967 
3968  case DTK_MILLISEC:
3969  fsec = (fsec / 1000) * 1000;
3970  break;
3971 
3972  case DTK_MICROSEC:
3973  break;
3974 
3975  default:
3976  ereport(ERROR,
3977  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3978  errmsg("timestamp units \"%s\" not supported",
3979  lowunits)));
3980  result = 0;
3981  }
3982 
3983  if (tm2timestamp(tm, fsec, NULL, &result) != 0)
3984  ereport(ERROR,
3985  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3986  errmsg("timestamp out of range")));
3987  }
3988  else
3989  {
3990  ereport(ERROR,
3991  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3992  errmsg("timestamp units \"%s\" not recognized",
3993  lowunits)));
3994  result = 0;
3995  }
3996 
3997  PG_RETURN_TIMESTAMP(result);
3998 }
3999 
4000 /* timestamptz_bin()
4001  * Bin timestamptz into specified interval using specified origin.
4002  */
4003 Datum
4004 timestamptz_bin(PG_FUNCTION_ARGS)
4005 {
4006  Interval *stride = PG_GETARG_INTERVAL_P(0);
4007  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4008  TimestampTz origin = PG_GETARG_TIMESTAMPTZ(2);
4009  TimestampTz result,
4010  stride_usecs,
4011  tm_diff,
4012  tm_delta;
4013 
4014  if (TIMESTAMP_NOT_FINITE(timestamp))
4015  PG_RETURN_TIMESTAMPTZ(timestamp);
4016 
4017  if (TIMESTAMP_NOT_FINITE(origin))
4018  ereport(ERROR,
4019  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4020  errmsg("origin out of range")));
4021 
4022  if (stride->month != 0)
4023  ereport(ERROR,
4024  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4025  errmsg("timestamps cannot be binned into intervals containing months or years")));
4026 
4027  stride_usecs = stride->day * USECS_PER_DAY + stride->time;
4028 
4029  if (stride_usecs <= 0)
4030  ereport(ERROR,
4031  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4032  errmsg("stride must be greater than zero")));
4033 
4034  tm_diff = timestamp - origin;
4035  tm_delta = tm_diff - tm_diff % stride_usecs;
4036 
4037  /*
4038  * Make sure the returned timestamp is at the start of the bin, even if
4039  * the origin is in the future.
4040  */
4041  if (origin > timestamp && stride_usecs > 1)
4042  tm_delta -= stride_usecs;
4043 
4044  result = origin + tm_delta;
4045 
4046  PG_RETURN_TIMESTAMPTZ(result);
4047 }
4048 
4049 /*
4050  * Common code for timestamptz_trunc() and timestamptz_trunc_zone().
4051  *
4052  * tzp identifies the zone to truncate with respect to. We assume
4053  * infinite timestamps have already been rejected.
4054  */
4055 static TimestampTz
4056 timestamptz_trunc_internal(text *units, TimestampTz timestamp, pg_tz *tzp)
4057 {
4058  TimestampTz result;
4059  int tz;
4060  int type,
4061  val;
4062  bool redotz = false;
4063  char *lowunits;
4064  fsec_t fsec;
4065  struct pg_tm tt,
4066  *tm = &tt;
4067 
4068  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4069  VARSIZE_ANY_EXHDR(units),
4070  false);
4071 
4072  type = DecodeUnits(0, lowunits, &val);
4073 
4074  if (type == UNITS)
4075  {
4076  if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, tzp) != 0)
4077  ereport(ERROR,
4078  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4079  errmsg("timestamp out of range")));
4080 
4081  switch (val)
4082  {
4083  case DTK_WEEK:
4084  {
4085  int woy;
4086 
4087  woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
4088 
4089  /*
4090  * If it is week 52/53 and the month is January, then the
4091  * week must belong to the previous year. Also, some
4092  * December dates belong to the next year.
4093  */
4094  if (woy >= 52 && tm->tm_mon == 1)
4095  --tm->tm_year;
4096  if (woy <= 1 && tm->tm_mon == MONTHS_PER_YEAR)
4097  ++tm->tm_year;
4098  isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
4099  tm->tm_hour = 0;
4100  tm->tm_min = 0;
4101  tm->tm_sec = 0;
4102  fsec = 0;
4103  redotz = true;
4104  break;
4105  }
4106  /* one may consider DTK_THOUSAND and DTK_HUNDRED... */
4107  case DTK_MILLENNIUM:
4108 
4109  /*
4110  * truncating to the millennium? what is this supposed to
4111  * mean? let us put the first year of the millennium... i.e.
4112  * -1000, 1, 1001, 2001...
4113  */
4114  if (tm->tm_year > 0)
4115  tm->tm_year = ((tm->tm_year + 999) / 1000) * 1000 - 999;
4116  else
4117  tm->tm_year = -((999 - (tm->tm_year - 1)) / 1000) * 1000 + 1;
4118  /* FALL THRU */
4119  case DTK_CENTURY:
4120  /* truncating to the century? as above: -100, 1, 101... */
4121  if (tm->tm_year > 0)
4122  tm->tm_year = ((tm->tm_year + 99) / 100) * 100 - 99;
4123  else
4124  tm->tm_year = -((99 - (tm->tm_year - 1)) / 100) * 100 + 1;
4125  /* FALL THRU */
4126  case DTK_DECADE:
4127 
4128  /*
4129  * truncating to the decade? first year of the decade. must
4130  * not be applied if year was truncated before!
4131  */
4132  if (val != DTK_MILLENNIUM && val != DTK_CENTURY)
4133  {
4134  if (tm->tm_year > 0)
4135  tm->tm_year = (tm->tm_year / 10) * 10;
4136  else
4137  tm->tm_year = -((8 - (tm->tm_year - 1)) / 10) * 10;
4138  }
4139  /* FALL THRU */
4140  case DTK_YEAR:
4141  tm->tm_mon = 1;
4142  /* FALL THRU */
4143  case DTK_QUARTER:
4144  tm->tm_mon = (3 * ((tm->tm_mon - 1) / 3)) + 1;
4145  /* FALL THRU */
4146  case DTK_MONTH:
4147  tm->tm_mday = 1;
4148  /* FALL THRU */
4149  case DTK_DAY:
4150  tm->tm_hour = 0;
4151  redotz = true; /* for all cases >= DAY */
4152  /* FALL THRU */
4153  case DTK_HOUR:
4154  tm->tm_min = 0;
4155  /* FALL THRU */
4156  case DTK_MINUTE:
4157  tm->tm_sec = 0;
4158  /* FALL THRU */
4159  case DTK_SECOND:
4160  fsec = 0;
4161  break;
4162  case DTK_MILLISEC:
4163  fsec = (fsec / 1000) * 1000;
4164  break;
4165  case DTK_MICROSEC:
4166  break;
4167 
4168  default:
4169  ereport(ERROR,
4170  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4171  errmsg("timestamp with time zone units \"%s\" not "
4172  "supported", lowunits)));
4173  result = 0;
4174  }
4175 
4176  if (redotz)
4177  tz = DetermineTimeZoneOffset(tm, tzp);
4178 
4179  if (tm2timestamp(tm, fsec, &tz, &result) != 0)
4180  ereport(ERROR,
4181  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4182  errmsg("timestamp out of range")));
4183  }
4184  else
4185  {
4186  ereport(ERROR,
4187  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4188  errmsg("timestamp with time zone units \"%s\" not recognized",
4189  lowunits)));
4190  result = 0;
4191  }
4192 
4193  return result;
4194 }
4195 
4196 /* timestamptz_trunc()
4197  * Truncate timestamptz to specified units in session timezone.
4198  */
4199 Datum
4200 timestamptz_trunc(PG_FUNCTION_ARGS)
4201 {
4202  text *units = PG_GETARG_TEXT_PP(0);
4203  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4204  TimestampTz result;
4205 
4206  if (TIMESTAMP_NOT_FINITE(timestamp))
4207  PG_RETURN_TIMESTAMPTZ(timestamp);
4208 
4209  result = timestamptz_trunc_internal(units, timestamp, session_timezone);
4210 
4211  PG_RETURN_TIMESTAMPTZ(result);
4212 }
4213 
4214 /* timestamptz_trunc_zone()
4215  * Truncate timestamptz to specified units in specified timezone.
4216  */
4217 Datum
4218 timestamptz_trunc_zone(PG_FUNCTION_ARGS)
4219 {
4220  text *units = PG_GETARG_TEXT_PP(0);
4221  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4222  text *zone = PG_GETARG_TEXT_PP(2);
4223  TimestampTz result;
4224  char tzname[TZ_STRLEN_MAX + 1];
4225  char *lowzone;
4226  int type,
4227  val;
4228  pg_tz *tzp;
4229 
4230  /*
4231  * timestamptz_zone() doesn't look up the zone for infinite inputs, so we
4232  * don't do so here either.
4233  */
4234  if (TIMESTAMP_NOT_FINITE(timestamp))
4235  PG_RETURN_TIMESTAMP(timestamp);
4236 
4237  /*
4238  * Look up the requested timezone (see notes in timestamptz_zone()).
4239  */
4240  text_to_cstring_buffer(zone, tzname, sizeof(tzname));
4241 
4242  /* DecodeTimezoneAbbrev requires lowercase input */
4243  lowzone = downcase_truncate_identifier(tzname,
4244  strlen(tzname),
4245  false);
4246 
4247  type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
4248 
4249  if (type == TZ || type == DTZ)
4250  {
4251  /* fixed-offset abbreviation, get a pg_tz descriptor for that */
4252  tzp = pg_tzset_offset(-val);
4253  }
4254  else if (type == DYNTZ)
4255  {
4256  /* dynamic-offset abbreviation, use its referenced timezone */
4257  }
4258  else
4259  {
4260  /* try it as a full zone name */
4261  tzp = pg_tzset(tzname);
4262  if (!tzp)
4263  ereport(ERROR,
4264  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4265  errmsg("time zone \"%s\" not recognized", tzname)));
4266  }
4267 
4268  result = timestamptz_trunc_internal(units, timestamp, tzp);
4269 
4270  PG_RETURN_TIMESTAMPTZ(result);
4271 }
4272 
4273 /* interval_trunc()
4274  * Extract specified field from interval.
4275  */
4276 Datum
4277 interval_trunc(PG_FUNCTION_ARGS)
4278 {
4279  text *units = PG_GETARG_TEXT_PP(0);
4280  Interval *interval = PG_GETARG_INTERVAL_P(1);
4281  Interval *result;
4282  int type,
4283  val;
4284  char *lowunits;
4285  fsec_t fsec;
4286  struct pg_tm tt,
4287  *tm = &tt;
4288 
4289  result = (Interval *) palloc(sizeof(Interval));
4290 
4291  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4292  VARSIZE_ANY_EXHDR(units),
4293  false);
4294 
4295  type = DecodeUnits(0, lowunits, &val);
4296 
4297  if (type == UNITS)
4298  {
4299  if (interval2tm(*interval, tm, &fsec) == 0)
4300  {
4301  switch (val)
4302  {
4303  case DTK_MILLENNIUM:
4304  /* caution: C division may have negative remainder */
4305  tm->tm_year = (tm->tm_year / 1000) * 1000;
4306  /* FALL THRU */
4307  case DTK_CENTURY:
4308  /* caution: C division may have negative remainder */
4309  tm->tm_year = (tm->tm_year / 100) * 100;
4310  /* FALL THRU */
4311  case DTK_DECADE:
4312  /* caution: C division may have negative remainder */
4313  tm->tm_year = (tm->tm_year / 10) * 10;
4314  /* FALL THRU */
4315  case DTK_YEAR:
4316  tm->tm_mon = 0;
4317  /* FALL THRU */
4318  case DTK_QUARTER:
4319  tm->tm_mon = 3 * (tm->tm_mon / 3);
4320  /* FALL THRU */
4321  case DTK_MONTH:
4322  tm->tm_mday = 0;
4323  /* FALL THRU */
4324  case DTK_DAY:
4325  tm->tm_hour = 0;
4326  /* FALL THRU */
4327  case DTK_HOUR:
4328  tm->tm_min = 0;
4329  /* FALL THRU */
4330  case DTK_MINUTE:
4331  tm->tm_sec = 0;
4332  /* FALL THRU */
4333  case DTK_SECOND:
4334  fsec = 0;
4335  break;
4336  case DTK_MILLISEC:
4337  fsec = (fsec / 1000) * 1000;
4338  break;
4339  case DTK_MICROSEC:
4340  break;
4341 
4342  default:
4343  if (val == DTK_WEEK)
4344  ereport(ERROR,
4345  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4346  errmsg("interval units \"%s\" not supported "
4347  "because months usually have fractional weeks",
4348  lowunits)));
4349  else
4350  ereport(ERROR,
4351  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4352  errmsg("interval units \"%s\" not supported",
4353  lowunits)));
4354  }
4355 
4356  if (tm2interval(tm, fsec, result) != 0)
4357  ereport(ERROR,
4358  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4359  errmsg("interval out of range")));
4360  }
4361  else
4362  elog(ERROR, "could not convert interval to tm");
4363  }
4364  else
4365  {
4366  ereport(ERROR,
4367  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4368  errmsg("interval units \"%s\" not recognized",
4369  lowunits)));
4370  }
4371 
4372  PG_RETURN_INTERVAL_P(result);
4373 }
4374 
4375 /* isoweek2j()
4376  *
4377  * Return the Julian day which corresponds to the first day (Monday) of the given ISO 8601 year and week.
4378  * Julian days are used to convert between ISO week dates and Gregorian dates.
4379  */
4380 int
4381 isoweek2j(int year, int week)
4382 {
4383  int day0,
4384  day4;
4385 
4386  /* fourth day of current year */
4387  day4 = date2j(year, 1, 4);
4388 
4389  /* day0 == offset to first day of week (Monday) */
4390  day0 = j2day(day4 - 1);
4391 
4392  return ((week - 1) * 7) + (day4 - day0);
4393 }
4394 
4395 /* isoweek2date()
4396  * Convert ISO week of year number to date.
4397  * The year field must be specified with the ISO year!
4398  * karel 2000/08/07
4399  */
4400 void
4401 isoweek2date(int woy, int *year, int *mon, int *mday)
4402 {
4403  j2date(isoweek2j(*year, woy), year, mon, mday);
4404 }
4405 
4406 /* isoweekdate2date()
4407  *
4408  * Convert an ISO 8601 week date (ISO year, ISO week) into a Gregorian date.
4409  * Gregorian day of week sent so weekday strings can be supplied.
4410  * Populates year, mon, and mday with the correct Gregorian values.
4411  * year must be passed in as the ISO year.
4412  */
4413 void
4414 isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday)
4415 {
4416  int jday;
4417 
4418  jday = isoweek2j(*year, isoweek);
4419  /* convert Gregorian week start (Sunday=1) to ISO week start (Monday=1) */
4420  if (wday > 1)
4421  jday += wday - 2;
4422  else
4423  jday += 6;
4424  j2date(jday, year, mon, mday);
4425 }
4426 
4427 /* date2isoweek()
4428  *
4429  * Returns ISO week number of year.
4430  */
4431 int
4432 date2isoweek(int year, int mon, int mday)
4433 {
4434  float8 result;
4435  int day0,
4436  day4,
4437  dayn;
4438 
4439  /* current day */
4440  dayn = date2j(year, mon, mday);
4441 
4442  /* fourth day of current year */
4443  day4 = date2j(year, 1, 4);
4444 
4445  /* day0 == offset to first day of week (Monday) */
4446  day0 = j2day(day4 - 1);
4447 
4448  /*
4449  * We need the first week containing a Thursday, otherwise this day falls
4450  * into the previous year for purposes of counting weeks
4451  */
4452  if (dayn < day4 - day0)
4453  {
4454  day4 = date2j(year - 1, 1, 4);
4455 
4456  /* day0 == offset to first day of week (Monday) */
4457  day0 = j2day(day4 - 1);
4458  }
4459 
4460  result = (dayn - (day4 - day0)) / 7 + 1;
4461 
4462  /*
4463  * Sometimes the last few days in a year will fall into the first week of
4464  * the next year, so check for this.
4465  */
4466  if (result >= 52)
4467  {
4468  day4 = date2j(year + 1, 1, 4);
4469 
4470  /* day0 == offset to first day of week (Monday) */
4471  day0 = j2day(day4 - 1);
4472 
4473  if (dayn >= day4 - day0)
4474  result = (dayn - (day4 - day0)) / 7 + 1;
4475  }
4476 
4477  return (int) result;
4478 }
4479 
4480 
4481 /* date2isoyear()
4482  *
4483  * Returns ISO 8601 year number.
4484  * Note: zero or negative results follow the year-zero-exists convention.
4485  */
4486 int
4487 date2isoyear(int year, int mon, int mday)
4488 {
4489  float8 result;
4490  int day0,
4491  day4,
4492  dayn;
4493 
4494  /* current day */
4495  dayn = date2j(year, mon, mday);
4496 
4497  /* fourth day of current year */
4498  day4 = date2j(year, 1, 4);
4499 
4500  /* day0 == offset to first day of week (Monday) */
4501  day0 = j2day(day4 - 1);
4502 
4503  /*
4504  * We need the first week containing a Thursday, otherwise this day falls
4505  * into the previous year for purposes of counting weeks
4506  */
4507  if (dayn < day4 - day0)
4508  {
4509  day4 = date2j(year - 1, 1, 4);
4510 
4511  /* day0 == offset to first day of week (Monday) */
4512  day0 = j2day(day4 - 1);
4513 
4514  year--;
4515  }
4516 
4517  result = (dayn - (day4 - day0)) / 7 + 1;
4518 
4519  /*
4520  * Sometimes the last few days in a year will fall into the first week of
4521  * the next year, so check for this.
4522  */
4523  if (result >= 52)
4524  {
4525  day4 = date2j(year + 1, 1, 4);
4526 
4527  /* day0 == offset to first day of week (Monday) */
4528  day0 = j2day(day4 - 1);
4529 
4530  if (dayn >= day4 - day0)
4531  year++;
4532  }
4533 
4534  return year;
4535 }
4536 
4537 
4538 /* date2isoyearday()
4539  *
4540  * Returns the ISO 8601 day-of-year, given a Gregorian year, month and day.
4541  * Possible return values are 1 through 371 (364 in non-leap years).
4542  */
4543 int
4544 date2isoyearday(int year, int mon, int mday)
4545 {
4546  return date2j(year, mon, mday) - isoweek2j(date2isoyear(year, mon, mday), 1) + 1;
4547 }
4548 
4549 /*
4550  * NonFiniteTimestampTzPart
4551  *
4552  * Used by timestamp_part and timestamptz_part when extracting from infinite
4553  * timestamp[tz]. Returns +/-Infinity if that is the appropriate result,
4554  * otherwise returns zero (which should be taken as meaning to return NULL).
4555  *
4556  * Errors thrown here for invalid units should exactly match those that
4557  * would be thrown in the calling functions, else there will be unexpected
4558  * discrepancies between finite- and infinite-input cases.
4559  */
4560 static float8
4561 NonFiniteTimestampTzPart(int type, int unit, char *lowunits,
4562  bool isNegative, bool isTz)
4563 {
4564  if ((type != UNITS) && (type != RESERV))
4565  {
4566  if (isTz)
4567  ereport(ERROR,
4568  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4569  errmsg("timestamp with time zone units \"%s\" not recognized",
4570  lowunits)));
4571  else
4572  ereport(ERROR,
4573  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4574  errmsg("timestamp units \"%s\" not recognized",
4575  lowunits)));
4576  }
4577 
4578  switch (unit)
4579  {
4580  /* Oscillating units */
4581  case DTK_MICROSEC:
4582  case DTK_MILLISEC:
4583  case DTK_SECOND:
4584  case DTK_MINUTE:
4585  case DTK_HOUR:
4586  case DTK_DAY:
4587  case DTK_MONTH:
4588  case DTK_QUARTER:
4589  case DTK_WEEK:
4590  case DTK_DOW:
4591  case DTK_ISODOW:
4592  case DTK_DOY:
4593  case DTK_TZ:
4594  case DTK_TZ_MINUTE:
4595  case DTK_TZ_HOUR:
4596  return 0.0;
4597 
4598  /* Monotonically-increasing units */
4599  case DTK_YEAR:
4600  case DTK_DECADE:
4601  case DTK_CENTURY:
4602  case DTK_MILLENNIUM:
4603  case DTK_JULIAN:
4604  case DTK_ISOYEAR:
4605  case DTK_EPOCH:
4606  if (isNegative)
4607  return -get_float8_infinity();
4608  else
4609  return get_float8_infinity();
4610 
4611  default:
4612  if (isTz)
4613  ereport(ERROR,
4614  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4615  errmsg("timestamp with time zone units \"%s\" not supported",
4616  lowunits)));
4617  else
4618  ereport(ERROR,
4619  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4620  errmsg("timestamp units \"%s\" not supported",
4621  lowunits)));
4622  return 0.0; /* keep compiler quiet */
4623  }
4624 }
4625 
4626 /* timestamp_part() and extract_timestamp()
4627  * Extract specified field from timestamp.
4628  */
4629 static Datum
4630 timestamp_part_common(PG_FUNCTION_ARGS, bool retnumeric)
4631 {
4632  text *units = PG_GETARG_TEXT_PP(0);
4633  Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
4634  int64 intresult;
4635  Timestamp epoch;
4636  int type,
4637  val;
4638  char *lowunits;
4639  fsec_t fsec;
4640  struct pg_tm tt,
4641  *tm = &tt;
4642 
4643  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4644  VARSIZE_ANY_EXHDR(units),
4645  false);
4646 
4647  type = DecodeUnits(0, lowunits, &val);
4648  if (type == UNKNOWN_FIELD)
4649  type = DecodeSpecial(0, lowunits, &val);
4650 
4651  if (TIMESTAMP_NOT_FINITE(timestamp))
4652  {
4653  double r = NonFiniteTimestampTzPart(type, val, lowunits,
4654  TIMESTAMP_IS_NOBEGIN(timestamp),
4655  false);
4656 
4657  if (r)
4658  {
4659  if (retnumeric)
4660  {
4661  if (r < 0)
4662  return DirectFunctionCall3(numeric_in,
4663  CStringGetDatum("-Infinity"),
4664  ObjectIdGetDatum(InvalidOid),
4665  Int32GetDatum(-1));
4666  else if (r > 0)
4667  return DirectFunctionCall3(numeric_in,
4668  CStringGetDatum("Infinity"),
4669  ObjectIdGetDatum(InvalidOid),
4670  Int32GetDatum(-1));
4671  }
4672  else
4673  PG_RETURN_FLOAT8(r);
4674  }
4675  else
4676  PG_RETURN_NULL();
4677  }
4678 
4679  if (type == UNITS)
4680  {
4681  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
4682  ereport(ERROR,
4683  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4684  errmsg("timestamp out of range")));
4685 
4686  switch (val)
4687  {
4688  case DTK_MICROSEC:
4689  intresult = tm->tm_sec * INT64CONST(1000000) + fsec;
4690  break;
4691 
4692  case DTK_MILLISEC:
4693  if (retnumeric)
4694  /*---
4695  * tm->tm_sec * 1000 + fsec / 1000
4696  * = (tm->tm_sec * 1'000'000 + fsec) / 1000
4697  */
4698  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 3));
4699  else
4700  PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0);
4701  break;
4702 
4703  case DTK_SECOND:
4704  if (retnumeric)
4705  /*---
4706  * tm->tm_sec + fsec / 1'000'000
4707  * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000
4708  */
4709  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 6));
4710  else
4711  PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0);
4712  break;
4713 
4714  case DTK_MINUTE:
4715  intresult = tm->tm_min;
4716  break;
4717 
4718  case DTK_HOUR:
4719  intresult = tm->tm_hour;
4720  break;
4721 
4722  case DTK_DAY:
4723  intresult = tm->tm_mday;
4724  break;
4725 
4726  case DTK_MONTH:
4727  intresult = tm->tm_mon;
4728  break;
4729 
4730  case DTK_QUARTER:
4731  intresult = (tm->tm_mon - 1) / 3 + 1;
4732  break;
4733 
4734  case DTK_WEEK:
4735  intresult = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
4736  break;
4737 
4738  case DTK_YEAR:
4739  if (tm->tm_year > 0)
4740  intresult = tm->tm_year;
4741  else
4742  /* there is no year 0, just 1 BC and 1 AD */
4743  intresult = tm->tm_year - 1;
4744  break;
4745 
4746  case DTK_DECADE:
4747 
4748  /*
4749  * what is a decade wrt dates? let us assume that decade 199
4750  * is 1990 thru 1999... decade 0 starts on year 1 BC, and -1
4751  * is 11 BC thru 2 BC...
4752  */
4753  if (tm->tm_year >= 0)
4754  intresult = tm->tm_year / 10;
4755  else
4756  intresult = -((8 - (tm->tm_year - 1)) / 10);
4757  break;
4758 
4759  case DTK_CENTURY:
4760 
4761  /* ----
4762  * centuries AD, c>0: year in [ (c-1)* 100 + 1 : c*100 ]
4763  * centuries BC, c<0: year in [ c*100 : (c+1) * 100 - 1]
4764  * there is no number 0 century.
4765  * ----
4766  */
4767  if (tm->tm_year > 0)
4768  intresult = (tm->tm_year + 99) / 100;
4769  else
4770  /* caution: C division may have negative remainder */
4771  intresult = -((99 - (tm->tm_year - 1)) / 100);
4772  break;
4773 
4774  case DTK_MILLENNIUM:
4775  /* see comments above. */
4776  if (tm->tm_year > 0)
4777  intresult = (tm->tm_year + 999) / 1000;
4778  else
4779  intresult = -((999 - (tm->tm_year - 1)) / 1000);
4780  break;
4781 
4782  case DTK_JULIAN:
4783  if (retnumeric)
4784  PG_RETURN_NUMERIC(numeric_add_opt_error(int64_to_numeric(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)),
4785  numeric_div_opt_error(int64_to_numeric(((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) * INT64CONST(1000000) + fsec),
4786  int64_to_numeric(SECS_PER_DAY * INT64CONST(1000000)),
4787  NULL),
4788  NULL));
4789  else
4790  PG_RETURN_FLOAT8(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) +
4791  ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) +
4792  tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY);
4793  break;
4794 
4795  case DTK_ISOYEAR:
4796  intresult = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday);
4797  /* Adjust BC years */
4798  if (intresult <= 0)
4799  intresult -= 1;
4800  break;
4801 
4802  case DTK_DOW:
4803  case DTK_ISODOW:
4804  intresult = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
4805  if (val == DTK_ISODOW && intresult == 0)
4806  intresult = 7;
4807  break;
4808 
4809  case DTK_DOY:
4810  intresult = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)
4811  - date2j(tm->tm_year, 1, 1) + 1);
4812  break;
4813 
4814  case DTK_TZ:
4815  case DTK_TZ_MINUTE:
4816  case DTK_TZ_HOUR:
4817  default:
4818  ereport(ERROR,
4819  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4820  errmsg("timestamp units \"%s\" not supported",
4821  lowunits)));
4822  intresult = 0;
4823  }
4824  }
4825  else if (type == RESERV)
4826  {
4827  switch (val)
4828  {
4829  case DTK_EPOCH:
4830  epoch = SetEpochTimestamp();
4831  /* (timestamp - epoch) / 1000000 */
4832  if (retnumeric)
4833  {
4834  Numeric result;
4835 
4836  if (timestamp < (PG_INT64_MAX + epoch))
4837  result = int64_div_fast_to_numeric(timestamp - epoch, 6);
4838  else
4839  {
4840  result = numeric_div_opt_error(numeric_sub_opt_error(int64_to_numeric(timestamp),
4841  int64_to_numeric(epoch),
4842  NULL),
4843  int64_to_numeric(1000000),
4844  NULL);
4845  result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
4846  NumericGetDatum(result),
4847  Int32GetDatum(6)));
4848  }
4849  PG_RETURN_NUMERIC(result);
4850  }
4851  else
4852  {
4853  float8 result;
4854 
4855  /* try to avoid precision loss in subtraction */
4856  if (timestamp < (PG_INT64_MAX + epoch))
4857  result = (timestamp - epoch) / 1000000.0;
4858  else
4859  result = ((float8) timestamp - epoch) / 1000000.0;
4860  PG_RETURN_FLOAT8(result);
4861  }
4862  break;
4863 
4864  default:
4865  ereport(ERROR,
4866  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4867  errmsg("timestamp units \"%s\" not supported",
4868  lowunits)));
4869  intresult = 0;
4870  }
4871 
4872  }
4873  else
4874  {
4875  ereport(ERROR,
4876  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4877  errmsg("timestamp units \"%s\" not recognized", lowunits)));
4878  intresult = 0;
4879  }
4880 
4881  if (retnumeric)
4882  PG_RETURN_NUMERIC(int64_to_numeric(intresult));
4883  else
4884  PG_RETURN_FLOAT8(intresult);
4885 }
4886 
4887 Datum
4888 timestamp_part(PG_FUNCTION_ARGS)
4889 {
4890  return timestamp_part_common(fcinfo, false);
4891 }
4892 
4893 Datum
4894 extract_timestamp(PG_FUNCTION_ARGS)
4895 {
4896  return timestamp_part_common(fcinfo, true);
4897 }
4898 
4899 /* timestamptz_part() and extract_timestamptz()
4900  * Extract specified field from timestamp with time zone.
4901  */
4902 static Datum
4903 timestamptz_part_common(PG_FUNCTION_ARGS, bool retnumeric)
4904 {
4905  text *units = PG_GETARG_TEXT_PP(0);
4906  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4907  int64 intresult;
4908  Timestamp epoch;
4909  int tz;
4910  int type,
4911  val;
4912  char *lowunits;
4913  fsec_t fsec;
4914  struct pg_tm tt,
4915  *tm = &tt;
4916 
4917  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4918  VARSIZE_ANY_EXHDR(units),
4919  false);
4920 
4921  type = DecodeUnits(0, lowunits, &val);
4922  if (type == UNKNOWN_FIELD)
4923  type = DecodeSpecial(0, lowunits, &val);
4924 
4925  if (TIMESTAMP_NOT_FINITE(timestamp))
4926  {
4927  double r = NonFiniteTimestampTzPart(type, val, lowunits,
4928  TIMESTAMP_IS_NOBEGIN(timestamp),
4929  true);
4930 
4931  if (r)
4932  {
4933  if (retnumeric)
4934  {
4935  if (r < 0)
4936  return DirectFunctionCall3(numeric_in,
4937  CStringGetDatum("-Infinity"),
4938  ObjectIdGetDatum(InvalidOid),
4939  Int32GetDatum(-1));
4940  else if (r > 0)
4941  return DirectFunctionCall3(numeric_in,
4942  CStringGetDatum("Infinity"),
4943  ObjectIdGetDatum(InvalidOid),
4944  Int32GetDatum(-1));
4945  }
4946  else
4947  PG_RETURN_FLOAT8(r);
4948  }
4949  else
4950  PG_RETURN_NULL();
4951  }
4952 
4953  if (type == UNITS)
4954  {
4955  if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
4956  ereport(ERROR,
4957  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4958  errmsg("timestamp out of range")));
4959 
4960  switch (val)
4961  {
4962  case DTK_TZ:
4963  intresult = -tz;
4964  break;
4965 
4966  case DTK_TZ_MINUTE:
4967  intresult = (-tz / SECS_PER_MINUTE) % MINS_PER_HOUR;
4968  break;
4969 
4970  case DTK_TZ_HOUR:
4971  intresult = -tz / SECS_PER_HOUR;
4972  break;
4973 
4974  case DTK_MICROSEC:
4975  intresult = tm->tm_sec * INT64CONST(1000000) + fsec;
4976  break;
4977 
4978  case DTK_MILLISEC:
4979  if (retnumeric)
4980  /*---
4981  * tm->tm_sec * 1000 + fsec / 1000
4982  * = (tm->tm_sec * 1'000'000 + fsec) / 1000
4983  */
4984  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 3));
4985  else
4986  PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0);
4987  break;
4988 
4989  case DTK_SECOND:
4990  if (retnumeric)
4991  /*---
4992  * tm->tm_sec + fsec / 1'000'000
4993  * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000
4994  */
4995  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 6));
4996  else
4997  PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0);
4998  break;
4999 
5000  case DTK_MINUTE:
5001  intresult = tm->tm_min;
5002  break;
5003 
5004  case DTK_HOUR:
5005  intresult = tm->tm_hour;
5006  break;
5007 
5008  case DTK_DAY:
5009  intresult = tm->tm_mday;
5010  break;
5011 
5012  case DTK_MONTH:
5013  intresult = tm->tm_mon;
5014  break;
5015 
5016  case DTK_QUARTER:
5017  intresult = (tm->tm_mon - 1) / 3 + 1;
5018  break;
5019 
5020  case DTK_WEEK:
5021  intresult = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
5022  break;
5023 
5024  case DTK_YEAR:
5025  if (tm->tm_year > 0)
5026  intresult = tm->tm_year;
5027  else
5028  /* there is no year 0, just 1 BC and 1 AD */
5029  intresult = tm->tm_year - 1;
5030  break;
5031 
5032  case DTK_DECADE:
5033  /* see comments in timestamp_part */
5034  if (tm->tm_year > 0)
5035  intresult = tm->tm_year / 10;
5036  else
5037  intresult = -((8 - (tm->tm_year - 1)) / 10);
5038  break;
5039 
5040  case DTK_CENTURY:
5041  /* see comments in timestamp_part */
5042  if (tm->tm_year > 0)
5043  intresult = (tm->tm_year + 99) / 100;
5044  else
5045  intresult = -((99 - (tm->tm_year - 1)) / 100);
5046  break;
5047 
5048  case DTK_MILLENNIUM:
5049  /* see comments in timestamp_part */
5050  if (tm->tm_year > 0)
5051  intresult = (tm->tm_year + 999) / 1000;
5052  else
5053  intresult = -((999 - (tm->tm_year - 1)) / 1000);
5054  break;
5055 
5056  case DTK_JULIAN:
5057  if (retnumeric)
5058  PG_RETURN_NUMERIC(numeric_add_opt_error(int64_to_numeric(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)),
5059  numeric_div_opt_error(int64_to_numeric(((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) * INT64CONST(1000000) + fsec),
5060  int64_to_numeric(SECS_PER_DAY * INT64CONST(1000000)),
5061  NULL),
5062  NULL));
5063  else
5064  PG_RETURN_FLOAT8(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) +
5065  ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) +
5066  tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY);
5067  break;
5068 
5069  case DTK_ISOYEAR:
5070  intresult = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday);
5071  /* Adjust BC years */
5072  if (intresult <= 0)
5073  intresult -= 1;
5074  break;
5075 
5076  case DTK_DOW:
5077  case DTK_ISODOW:
5078  intresult = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
5079  if (val == DTK_ISODOW && intresult == 0)
5080  intresult = 7;
5081  break;
5082 
5083  case DTK_DOY:
5084  intresult = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)
5085  - date2j(tm->tm_year, 1, 1) + 1);
5086  break;
5087 
5088  default:
5089  ereport(ERROR,
5090  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5091  errmsg("timestamp with time zone units \"%s\" not supported",
5092  lowunits)));
5093  intresult = 0;
5094  }
5095 
5096  }
5097  else if (type == RESERV)
5098  {
5099  switch (val)
5100  {
5101  case DTK_EPOCH:
5102  epoch = SetEpochTimestamp();
5103  /* (timestamp - epoch) / 1000000 */
5104  if (retnumeric)
5105  {
5106  Numeric result;
5107 
5108  if (timestamp < (PG_INT64_MAX + epoch))
5109  result = int64_div_fast_to_numeric(timestamp - epoch, 6);
5110  else
5111  {
5112  result = numeric_div_opt_error(numeric_sub_opt_error(int64_to_numeric(timestamp),
5113  int64_to_numeric(epoch),
5114  NULL),
5115  int64_to_numeric(1000000),
5116  NULL);
5117  result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
5118  NumericGetDatum(result),
5119  Int32GetDatum(6)));
5120  }
5121  PG_RETURN_NUMERIC(result);
5122  }
5123  else
5124  {
5125  float8 result;
5126 
5127  /* try to avoid precision loss in subtraction */
5128  if (timestamp < (PG_INT64_MAX + epoch))
5129  result = (timestamp - epoch) / 1000000.0;
5130  else
5131  result = ((float8) timestamp - epoch) / 1000000.0;
5132  PG_RETURN_FLOAT8(result);
5133  }
5134  break;
5135 
5136  default:
5137  ereport(ERROR,
5138  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5139  errmsg("timestamp with time zone units \"%s\" not supported",
5140  lowunits)));
5141  intresult = 0;
5142  }
5143  }
5144  else
5145  {
5146  ereport(ERROR,
5147  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5148  errmsg("timestamp with time zone units \"%s\" not recognized",
5149  lowunits)));
5150 
5151  intresult = 0;
5152  }
5153 
5154  if (retnumeric)
5155  PG_RETURN_NUMERIC(int64_to_numeric(intresult));
5156  else
5157  PG_RETURN_FLOAT8(intresult);
5158 }
5159 
5160 Datum
5161 timestamptz_part(PG_FUNCTION_ARGS)
5162 {
5163  return timestamptz_part_common(fcinfo, false);
5164 }
5165 
5166 Datum
5167 extract_timestamptz(PG_FUNCTION_ARGS)
5168 {
5169  return timestamptz_part_common(fcinfo, true);
5170 }
5171 
5172 
5173 /* interval_part() and extract_interval()
5174  * Extract specified field from interval.
5175  */
5176 static Datum
5177 interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
5178 {
5179  text *units = PG_GETARG_TEXT_PP(0);
5180  Interval *interval = PG_GETARG_INTERVAL_P(1);
5181  int64 intresult;
5182  int type,
5183  val;
5184  char *lowunits;
5185  fsec_t fsec;
5186  struct pg_tm tt,
5187  *tm = &tt;
5188 
5189  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
5190  VARSIZE_ANY_EXHDR(units),
5191  false);
5192 
5193  type = DecodeUnits(0, lowunits, &val);
5194  if (type == UNKNOWN_FIELD)
5195  type = DecodeSpecial(0, lowunits, &val);
5196 
5197  if (type == UNITS)
5198  {
5199  if (interval2tm(*interval, tm, &fsec) == 0)
5200  {
5201  switch (val)
5202  {
5203  case DTK_MICROSEC:
5204  intresult = tm->tm_sec * INT64CONST(1000000) + fsec;
5205  break;
5206 
5207  case DTK_MILLISEC:
5208  if (retnumeric)
5209  /*---
5210  * tm->tm_sec * 1000 + fsec / 1000
5211  * = (tm->tm_sec * 1'000'000 + fsec) / 1000
5212  */
5213  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 3));
5214  else
5215  PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0);
5216  break;
5217 
5218  case DTK_SECOND:
5219  if (retnumeric)
5220  /*---
5221  * tm->tm_sec + fsec / 1'000'000
5222  * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000
5223  */
5224  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 6));
5225  else
5226  PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0);
5227  break;
5228 
5229  case DTK_MINUTE:
5230  intresult = tm->tm_min;
5231  break;
5232 
5233  case DTK_HOUR:
5234  intresult = tm->tm_hour;
5235  break;
5236 
5237  case DTK_DAY:
5238  intresult = tm->tm_mday;
5239  break;
5240 
5241  case DTK_MONTH:
5242  intresult = tm->tm_mon;
5243  break;
5244 
5245  case DTK_QUARTER:
5246  intresult = (tm->tm_mon / 3) + 1;
5247  break;
5248 
5249  case DTK_YEAR:
5250  intresult = tm->tm_year;
5251  break;
5252 
5253  case DTK_DECADE:
5254  /* caution: C division may have negative remainder */
5255  intresult = tm->tm_year / 10;
5256  break;
5257 
5258  case DTK_CENTURY:
5259  /* caution: C division may have negative remainder */
5260  intresult = tm->tm_year / 100;
5261  break;
5262 
5263  case DTK_MILLENNIUM:
5264  /* caution: C division may have negative remainder */
5265  intresult = tm->tm_year / 1000;
5266  break;
5267 
5268  default:
5269  ereport(ERROR,
5270  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5271  errmsg("interval units \"%s\" not supported",
5272  lowunits)));
5273  intresult = 0;
5274  }
5275  }
5276  else
5277  {
5278  elog(ERROR, "could not convert interval to tm");
5279  intresult = 0;
5280  }
5281  }
5282  else if (type == RESERV && val == DTK_EPOCH)
5283  {
5284  if (retnumeric)
5285  {
5286  Numeric result;
5287  int64 secs_from_day_month;
5288  int64 val;
5289 
5290  /* this always fits into int64 */
5291  secs_from_day_month = ((int64) DAYS_PER_YEAR * (interval->month / MONTHS_PER_YEAR) +
5292  (int64) DAYS_PER_MONTH * (interval->month % MONTHS_PER_YEAR) +
5293  interval->day) * SECS_PER_DAY;
5294 
5295  /*---
5296  * result = secs_from_day_month + interval->time / 1'000'000
5297  * = (secs_from_day_month * 1'000'000 + interval->time) / 1'000'000
5298  */
5299 
5300  /*
5301  * Try the computation inside int64; if it overflows, do it in
5302  * numeric (slower). This overflow happens around 10^9 days, so
5303  * not common in practice.
5304  */
5305  if (!pg_mul_s64_overflow(secs_from_day_month, 1000000, &val) &&
5306  !pg_add_s64_overflow(val, interval->time, &val))
5307  result = int64_div_fast_to_numeric(val, 6);
5308  else
5309  result =
5310  numeric_add_opt_error(int64_div_fast_to_numeric(interval->time, 6),
5311  int64_to_numeric(secs_from_day_month),
5312  NULL);
5313 
5314  PG_RETURN_NUMERIC(result);
5315  }
5316  else
5317  {
5318  float8 result;
5319 
5320  result = interval->time / 1000000.0;
5321  result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);
5322  result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);
5323  result += ((double) SECS_PER_DAY) * interval->day;
5324 
5325  PG_RETURN_FLOAT8(result);
5326  }
5327  }
5328  else
5329  {
5330  ereport(ERROR,
5331  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5332  errmsg("interval units \"%s\" not recognized",
5333  lowunits)));
5334  intresult = 0;
5335  }
5336 
5337  if (retnumeric)
5338  PG_RETURN_NUMERIC(int64_to_numeric(intresult));
5339  else
5340  PG_RETURN_FLOAT8(intresult);
5341 }
5342 
5343 Datum
5344 interval_part(PG_FUNCTION_ARGS)
5345 {
5346  return interval_part_common(fcinfo, false);
5347 }
5348 
5349 Datum
5350 extract_interval(PG_FUNCTION_ARGS)
5351 {
5352  return interval_part_common(fcinfo, true);
5353 }
5354 
5355 
5356 /* timestamp_zone()
5357  * Encode timestamp type with specified time zone.
5358  * This function is just timestamp2timestamptz() except instead of
5359  * shifting to the global timezone, we shift to the specified timezone.
5360  * This is different from the other AT TIME ZONE cases because instead
5361  * of shifting _to_ a new time zone, it sets the time to _be_ the
5362  * specified timezone.
5363  */
5364 Datum
5365 timestamp_zone(PG_FUNCTION_ARGS)
5366 {
5367  text *zone = PG_GETARG_TEXT_PP(0);
5368  Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
5369  TimestampTz result;
5370  int tz;
5371  char tzname[TZ_STRLEN_MAX + 1];
5372  char *lowzone;
5373  int type,
5374  val;
5375  pg_tz *tzp;
5376  struct pg_tm tm;
5377  fsec_t fsec;
5378 
5379  if (TIMESTAMP_NOT_FINITE(timestamp))
5380  PG_RETURN_TIMESTAMPTZ(timestamp);
5381 
5382  /*
5383  * Look up the requested timezone. First we look in the timezone
5384  * abbreviation table (to handle cases like "EST"), and if that fails, we
5385  * look in the timezone database (to handle cases like
5386  * "America/New_York"). (This matches the order in which timestamp input
5387  * checks the cases; it's important because the timezone database unwisely
5388  * uses a few zone names that are identical to offset abbreviations.)
5389  */
5390  text_to_cstring_buffer(zone, tzname, sizeof(tzname));
5391 
5392  /* DecodeTimezoneAbbrev requires lowercase input */
5393  lowzone = downcase_truncate_identifier(tzname,
5394  strlen(tzname),
5395  false);
5396 
5397  type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
5398 
5399  if (type == TZ || type == DTZ)
5400  {
5401  /* fixed-offset abbreviation */
5402  tz = val;
5403  result = dt2local(timestamp, tz);
5404  }
5405  else if (type == DYNTZ)
5406  {
5407  /* dynamic-offset abbreviation, resolve using specified time */
5408  if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0)
5409  ereport(ERROR,
5410  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5411  errmsg("timestamp out of range")));
5412  tz = -DetermineTimeZoneAbbrevOffset(&tm, tzname, tzp);
5413  result = dt2local(timestamp, tz);
5414  }
5415  else
5416  {
5417  /* try it as a full zone name */
5418  tzp = pg_tzset(tzname);
5419  if (tzp)
5420  {
5421  /* Apply the timezone change */
5422  if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0)
5423  ereport(ERROR,
5424  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5425  errmsg("timestamp out of range")));
5426  tz = DetermineTimeZoneOffset(&tm, tzp);
5427  if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
5428  ereport(ERROR,
5429  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5430  errmsg("timestamp out of range")));
5431  }
5432  else
5433  {
5434  ereport(ERROR,
5435  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5436  errmsg("time zone \"%s\" not recognized", tzname)));
5437  result = 0; /* keep compiler quiet */
5438  }
5439  }
5440 
5441  if (!IS_VALID_TIMESTAMP(result))
5442  ereport(ERROR,
5443  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5444  errmsg("timestamp out of range")));
5445 
5446  PG_RETURN_TIMESTAMPTZ(result);
5447 }
5448 
5449 /* timestamp_izone()
5450  * Encode timestamp type with specified time interval as time zone.
5451  */
5452 Datum
5453 timestamp_izone(PG_FUNCTION_ARGS)
5454 {
5455  Interval *zone = PG_GETARG_INTERVAL_P(0);
5456  Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
5457  TimestampTz result;
5458  int tz;
5459 
5460  if (TIMESTAMP_NOT_FINITE(timestamp))
5461  PG_RETURN_TIMESTAMPTZ(timestamp);
5462 
5463  if (zone->month != 0 || zone->day != 0)
5464  ereport(ERROR,
5465  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5466  errmsg("interval time zone \"%s\" must not include months or days",
5467  DatumGetCString(DirectFunctionCall1(interval_out,
5468  PointerGetDatum(zone))))));
5469 
5470  tz = zone->time / USECS_PER_SEC;
5471 
5472  result = dt2local(timestamp, tz);
5473 
5474  if (!IS_VALID_TIMESTAMP(result))
5475  ereport(ERROR,
5476  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5477  errmsg("timestamp out of range")));
5478 
5479  PG_RETURN_TIMESTAMPTZ(result);
5480 } /* timestamp_izone() */
5481 
5482 /* TimestampTimestampTzRequiresRewrite()
5483  *
5484  * Returns false if the TimeZone GUC setting causes timestamp_timestamptz and
5485  * timestamptz_timestamp to be no-ops, where the return value has the same
5486  * bits as the argument. Since project convention is to assume a GUC changes
5487  * no more often than STABLE functions change, the answer is valid that long.
5488  */
5489 bool
5490 TimestampTimestampTzRequiresRewrite(void)
5491 {
5492  long offset;
5493 
5494  if (pg_get_timezone_offset(session_timezone, &offset) && offset == 0)
5495  return false;
5496  return true;
5497 }
5498 
5499 /* timestamp_timestamptz()
5500  * Convert local timestamp to timestamp at GMT
5501  */
5502 Datum
5503 timestamp_timestamptz(PG_FUNCTION_ARGS)
5504 {
5505  Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
5506 
5507  PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(timestamp));
5508 }
5509 
5510 /*
5511  * Convert timestamp to timestamp with time zone.
5512  *
5513  * On successful conversion, *overflow is set to zero if it's not NULL.
5514  *
5515  * If the timestamp is finite but out of the valid range for timestamptz, then:
5516  * if overflow is NULL, we throw an out-of-range error.
5517  * if overflow is not NULL, we store +1 or -1 there to indicate the sign
5518  * of the overflow, and return the appropriate timestamptz infinity.
5519  */
5520 TimestampTz
5521 timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow)
5522 {
5523  TimestampTz result;
5524  struct pg_tm tt,
5525  *tm = &tt;
5526  fsec_t fsec;
5527  int tz;
5528 
5529  if (overflow)
5530  *overflow = 0;
5531 
5532  if (TIMESTAMP_NOT_FINITE(timestamp))
5533  return timestamp;
5534 
5535  /* We don't expect this to fail, but check it pro forma */
5536  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
5537  {
5538  tz = DetermineTimeZoneOffset(tm, session_timezone);
5539 
5540  result = dt2local(timestamp, -tz);
5541 
5542  if (IS_VALID_TIMESTAMP(result))
5543  {
5544  return result;
5545  }
5546  else if (overflow)
5547  {
5548  if (result < MIN_TIMESTAMP)
5549  {
5550  *overflow = -1;
5551  TIMESTAMP_NOBEGIN(result);
5552  }
5553  else
5554  {
5555  *overflow = 1;
5556  TIMESTAMP_NOEND(result);
5557  }
5558  return result;
5559  }
5560  }
5561 
5562  ereport(ERROR,
5563  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5564  errmsg("timestamp out of range")));
5565 
5566  return 0;
5567 }
5568 
5569 /*
5570  * Promote timestamp to timestamptz, throwing error for overflow.
5571  */
5572 static TimestampTz
5573 timestamp2timestamptz(Timestamp timestamp)
5574 {
5575  return timestamp2timestamptz_opt_overflow(timestamp, NULL);
5576 }
5577 
5578 /* timestamptz_timestamp()
5579  * Convert timestamp at GMT to local timestamp
5580  */
5581 Datum
5582 timestamptz_timestamp(PG_FUNCTION_ARGS)
5583 {
5584  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0)