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