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