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