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