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