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