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 days;
2356 
2357  /*
2358  * Combine the month and day fields into an integral number of days.
2359  * Because the inputs are int32, int64 arithmetic suffices here.
2360  */
2361  days = interval->month * INT64CONST(30);
2362  days += interval->day;
2363 
2364  /* Widen time field to 128 bits */
2365  span = int64_to_int128(interval->time);
2366 
2367  /* Scale up days to microseconds, forming a 128-bit product */
2368  int128_add_int64_mul_int64(&span, days, USECS_PER_DAY);
2369 
2370  return span;
2371 }
2372 
2373 static int
2374 interval_cmp_internal(Interval *interval1, Interval *interval2)
2375 {
2376  INT128 span1 = interval_cmp_value(interval1);
2377  INT128 span2 = interval_cmp_value(interval2);
2378 
2379  return int128_compare(span1, span2);
2380 }
2381 
2382 Datum
2383 interval_eq(PG_FUNCTION_ARGS)
2384 {
2385  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2386  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2387 
2388  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) == 0);
2389 }
2390 
2391 Datum
2392 interval_ne(PG_FUNCTION_ARGS)
2393 {
2394  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2395  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2396 
2397  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) != 0);
2398 }
2399 
2400 Datum
2401 interval_lt(PG_FUNCTION_ARGS)
2402 {
2403  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2404  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2405 
2406  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) < 0);
2407 }
2408 
2409 Datum
2410 interval_gt(PG_FUNCTION_ARGS)
2411 {
2412  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2413  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2414 
2415  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) > 0);
2416 }
2417 
2418 Datum
2419 interval_le(PG_FUNCTION_ARGS)
2420 {
2421  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2422  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2423 
2424  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) <= 0);
2425 }
2426 
2427 Datum
2428 interval_ge(PG_FUNCTION_ARGS)
2429 {
2430  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2431  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2432 
2433  PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) >= 0);
2434 }
2435 
2436 Datum
2437 interval_cmp(PG_FUNCTION_ARGS)
2438 {
2439  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2440  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2441 
2442  PG_RETURN_INT32(interval_cmp_internal(interval1, interval2));
2443 }
2444 
2445 /*
2446  * Hashing for intervals
2447  *
2448  * We must produce equal hashvals for values that interval_cmp_internal()
2449  * considers equal. So, compute the net span the same way it does,
2450  * and then hash that.
2451  */
2452 Datum
2453 interval_hash(PG_FUNCTION_ARGS)
2454 {
2455  Interval *interval = PG_GETARG_INTERVAL_P(0);
2456  INT128 span = interval_cmp_value(interval);
2457  int64 span64;
2458 
2459  /*
2460  * Use only the least significant 64 bits for hashing. The upper 64 bits
2461  * seldom add any useful information, and besides we must do it like this
2462  * for compatibility with hashes calculated before use of INT128 was
2463  * introduced.
2464  */
2465  span64 = int128_to_int64(span);
2466 
2467  return DirectFunctionCall1(hashint8, Int64GetDatumFast(span64));
2468 }
2469 
2470 Datum
2471 interval_hash_extended(PG_FUNCTION_ARGS)
2472 {
2473  Interval *interval = PG_GETARG_INTERVAL_P(0);
2474  INT128 span = interval_cmp_value(interval);
2475  int64 span64;
2476 
2477  /* Same approach as interval_hash */
2478  span64 = int128_to_int64(span);
2479 
2480  return DirectFunctionCall2(hashint8extended, Int64GetDatumFast(span64),
2481  PG_GETARG_DATUM(1));
2482 }
2483 
2484 /* overlaps_timestamp() --- implements the SQL OVERLAPS operator.
2485  *
2486  * Algorithm is per SQL spec. This is much harder than you'd think
2487  * because the spec requires us to deliver a non-null answer in some cases
2488  * where some of the inputs are null.
2489  */
2490 Datum
2491 overlaps_timestamp(PG_FUNCTION_ARGS)
2492 {
2493  /*
2494  * The arguments are Timestamps, but we leave them as generic Datums to
2495  * avoid unnecessary conversions between value and reference forms --- not
2496  * to mention possible dereferences of null pointers.
2497  */
2498  Datum ts1 = PG_GETARG_DATUM(0);
2499  Datum te1 = PG_GETARG_DATUM(1);
2500  Datum ts2 = PG_GETARG_DATUM(2);
2501  Datum te2 = PG_GETARG_DATUM(3);
2502  bool ts1IsNull = PG_ARGISNULL(0);
2503  bool te1IsNull = PG_ARGISNULL(1);
2504  bool ts2IsNull = PG_ARGISNULL(2);
2505  bool te2IsNull = PG_ARGISNULL(3);
2506 
2507 #define TIMESTAMP_GT(t1,t2) \
2508  DatumGetBool(DirectFunctionCall2(timestamp_gt,t1,t2))
2509 #define TIMESTAMP_LT(t1,t2) \
2510  DatumGetBool(DirectFunctionCall2(timestamp_lt,t1,t2))
2511 
2512  /*
2513  * If both endpoints of interval 1 are null, the result is null (unknown).
2514  * If just one endpoint is null, take ts1 as the non-null one. Otherwise,
2515  * take ts1 as the lesser endpoint.
2516  */
2517  if (ts1IsNull)
2518  {
2519  if (te1IsNull)
2520  PG_RETURN_NULL();
2521  /* swap null for non-null */
2522  ts1 = te1;
2523  te1IsNull = true;
2524  }
2525  else if (!te1IsNull)
2526  {
2527  if (TIMESTAMP_GT(ts1, te1))
2528  {
2529  Datum tt = ts1;
2530 
2531  ts1 = te1;
2532  te1 = tt;
2533  }
2534  }
2535 
2536  /* Likewise for interval 2. */
2537  if (ts2IsNull)
2538  {
2539  if (te2IsNull)
2540  PG_RETURN_NULL();
2541  /* swap null for non-null */
2542  ts2 = te2;
2543  te2IsNull = true;
2544  }
2545  else if (!te2IsNull)
2546  {
2547  if (TIMESTAMP_GT(ts2, te2))
2548  {
2549  Datum tt = ts2;
2550 
2551  ts2 = te2;
2552  te2 = tt;
2553  }
2554  }
2555 
2556  /*
2557  * At this point neither ts1 nor ts2 is null, so we can consider three
2558  * cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
2559  */
2560  if (TIMESTAMP_GT(ts1, ts2))
2561  {
2562  /*
2563  * This case is ts1 < te2 OR te1 < te2, which may look redundant but
2564  * in the presence of nulls it's not quite completely so.
2565  */
2566  if (te2IsNull)
2567  PG_RETURN_NULL();
2568  if (TIMESTAMP_LT(ts1, te2))
2569  PG_RETURN_BOOL(true);
2570  if (te1IsNull)
2571  PG_RETURN_NULL();
2572 
2573  /*
2574  * If te1 is not null then we had ts1 <= te1 above, and we just found
2575  * ts1 >= te2, hence te1 >= te2.
2576  */
2577  PG_RETURN_BOOL(false);
2578  }
2579  else if (TIMESTAMP_LT(ts1, ts2))
2580  {
2581  /* This case is ts2 < te1 OR te2 < te1 */
2582  if (te1IsNull)
2583  PG_RETURN_NULL();
2584  if (TIMESTAMP_LT(ts2, te1))
2585  PG_RETURN_BOOL(true);
2586  if (te2IsNull)
2587  PG_RETURN_NULL();
2588 
2589  /*
2590  * If te2 is not null then we had ts2 <= te2 above, and we just found
2591  * ts2 >= te1, hence te2 >= te1.
2592  */
2593  PG_RETURN_BOOL(false);
2594  }
2595  else
2596  {
2597  /*
2598  * For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
2599  * rather silly way of saying "true if both are non-null, else null".
2600  */
2601  if (te1IsNull || te2IsNull)
2602  PG_RETURN_NULL();
2603  PG_RETURN_BOOL(true);
2604  }
2605 
2606 #undef TIMESTAMP_GT
2607 #undef TIMESTAMP_LT
2608 }
2609 
2610 
2611 /*----------------------------------------------------------
2612  * "Arithmetic" operators on date/times.
2613  *---------------------------------------------------------*/
2614 
2615 Datum
2616 timestamp_smaller(PG_FUNCTION_ARGS)
2617 {
2618  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2619  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2620  Timestamp result;
2621 
2622  /* use timestamp_cmp_internal to be sure this agrees with comparisons */
2623  if (timestamp_cmp_internal(dt1, dt2) < 0)
2624  result = dt1;
2625  else
2626  result = dt2;
2627  PG_RETURN_TIMESTAMP(result);
2628 }
2629 
2630 Datum
2631 timestamp_larger(PG_FUNCTION_ARGS)
2632 {
2633  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2634  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2635  Timestamp result;
2636 
2637  if (timestamp_cmp_internal(dt1, dt2) > 0)
2638  result = dt1;
2639  else
2640  result = dt2;
2641  PG_RETURN_TIMESTAMP(result);
2642 }
2643 
2644 
2645 Datum
2646 timestamp_mi(PG_FUNCTION_ARGS)
2647 {
2648  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2649  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2650  Interval *result;
2651 
2652  result = (Interval *) palloc(sizeof(Interval));
2653 
2654  if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
2655  ereport(ERROR,
2656  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2657  errmsg("cannot subtract infinite timestamps")));
2658 
2659  result->time = dt1 - dt2;
2660 
2661  result->month = 0;
2662  result->day = 0;
2663 
2664  /*----------
2665  * This is wrong, but removing it breaks a lot of regression tests.
2666  * For example:
2667  *
2668  * test=> SET timezone = 'EST5EDT';
2669  * test=> SELECT
2670  * test-> ('2005-10-30 13:22:00-05'::timestamptz -
2671  * test(> '2005-10-29 13:22:00-04'::timestamptz);
2672  * ?column?
2673  * ----------------
2674  * 1 day 01:00:00
2675  * (1 row)
2676  *
2677  * so adding that to the first timestamp gets:
2678  *
2679  * test=> SELECT
2680  * test-> ('2005-10-29 13:22:00-04'::timestamptz +
2681  * test(> ('2005-10-30 13:22:00-05'::timestamptz -
2682  * test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
2683  * timezone
2684  * --------------------
2685  * 2005-10-30 14:22:00
2686  * (1 row)
2687  *----------
2688  */
2689  result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours,
2690  IntervalPGetDatum(result)));
2691 
2692  PG_RETURN_INTERVAL_P(result);
2693 }
2694 
2695 /*
2696  * interval_justify_interval()
2697  *
2698  * Adjust interval so 'month', 'day', and 'time' portions are within
2699  * customary bounds. Specifically:
2700  *
2701  * 0 <= abs(time) < 24 hours
2702  * 0 <= abs(day) < 30 days
2703  *
2704  * Also, the sign bit on all three fields is made equal, so either
2705  * all three fields are negative or all are positive.
2706  */
2707 Datum
2708 interval_justify_interval(PG_FUNCTION_ARGS)
2709 {
2710  Interval *span = PG_GETARG_INTERVAL_P(0);
2711  Interval *result;
2712  TimeOffset wholeday;
2713  int32 wholemonth;
2714 
2715  result = (Interval *) palloc(sizeof(Interval));
2716  result->month = span->month;
2717  result->day = span->day;
2718  result->time = span->time;
2719 
2720  TMODULO(result->time, wholeday, USECS_PER_DAY);
2721  result->day += wholeday; /* could overflow... */
2722 
2723  wholemonth = result->day / DAYS_PER_MONTH;
2724  result->day -= wholemonth * DAYS_PER_MONTH;
2725  result->month += wholemonth;
2726 
2727  if (result->month > 0 &&
2728  (result->day < 0 || (result->day == 0 && result->time < 0)))
2729  {
2730  result->day += DAYS_PER_MONTH;
2731  result->month--;
2732  }
2733  else if (result->month < 0 &&
2734  (result->day > 0 || (result->day == 0 && result->time > 0)))
2735  {
2736  result->day -= DAYS_PER_MONTH;
2737  result->month++;
2738  }
2739 
2740  if (result->day > 0 && result->time < 0)
2741  {
2742  result->time += USECS_PER_DAY;
2743  result->day--;
2744  }
2745  else if (result->day < 0 && result->time > 0)
2746  {
2747  result->time -= USECS_PER_DAY;
2748  result->day++;
2749  }
2750 
2751  PG_RETURN_INTERVAL_P(result);
2752 }
2753 
2754 /*
2755  * interval_justify_hours()
2756  *
2757  * Adjust interval so 'time' contains less than a whole day, adding
2758  * the excess to 'day'. This is useful for
2759  * situations (such as non-TZ) where '1 day' = '24 hours' is valid,
2760  * e.g. interval subtraction and division.
2761  */
2762 Datum
2763 interval_justify_hours(PG_FUNCTION_ARGS)
2764 {
2765  Interval *span = PG_GETARG_INTERVAL_P(0);
2766  Interval *result;
2767  TimeOffset wholeday;
2768 
2769  result = (Interval *) palloc(sizeof(Interval));
2770  result->month = span->month;
2771  result->day = span->day;
2772  result->time = span->time;
2773 
2774  TMODULO(result->time, wholeday, USECS_PER_DAY);
2775  result->day += wholeday; /* could overflow... */
2776 
2777  if (result->day > 0 && result->time < 0)
2778  {
2779  result->time += USECS_PER_DAY;
2780  result->day--;
2781  }
2782  else if (result->day < 0 && result->time > 0)
2783  {
2784  result->time -= USECS_PER_DAY;
2785  result->day++;
2786  }
2787 
2788  PG_RETURN_INTERVAL_P(result);
2789 }
2790 
2791 /*
2792  * interval_justify_days()
2793  *
2794  * Adjust interval so 'day' contains less than 30 days, adding
2795  * the excess to 'month'.
2796  */
2797 Datum
2798 interval_justify_days(PG_FUNCTION_ARGS)
2799 {
2800  Interval *span = PG_GETARG_INTERVAL_P(0);
2801  Interval *result;
2802  int32 wholemonth;
2803 
2804  result = (Interval *) palloc(sizeof(Interval));
2805  result->month = span->month;
2806  result->day = span->day;
2807  result->time = span->time;
2808 
2809  wholemonth = result->day / DAYS_PER_MONTH;
2810  result->day -= wholemonth * DAYS_PER_MONTH;
2811  result->month += wholemonth;
2812 
2813  if (result->month > 0 && result->day < 0)
2814  {
2815  result->day += DAYS_PER_MONTH;
2816  result->month--;
2817  }
2818  else if (result->month < 0 && result->day > 0)
2819  {
2820  result->day -= DAYS_PER_MONTH;
2821  result->month++;
2822  }
2823 
2824  PG_RETURN_INTERVAL_P(result);
2825 }
2826 
2827 /* timestamp_pl_interval()
2828  * Add an interval to a timestamp data type.
2829  * Note that interval has provisions for qualitative year/month and day
2830  * units, so try to do the right thing with them.
2831  * To add a month, increment the month, and use the same day of month.
2832  * Then, if the next month has fewer days, set the day of month
2833  * to the last day of month.
2834  * To add a day, increment the mday, and use the same time of day.
2835  * Lastly, add in the "quantitative time".
2836  */
2837 Datum
2838 timestamp_pl_interval(PG_FUNCTION_ARGS)
2839 {
2840  Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
2841  Interval *span = PG_GETARG_INTERVAL_P(1);
2842  Timestamp result;
2843 
2844  if (TIMESTAMP_NOT_FINITE(timestamp))
2845  result = timestamp;
2846  else
2847  {
2848  if (span->month != 0)
2849  {
2850  struct pg_tm tt,
2851  *tm = &tt;
2852  fsec_t fsec;
2853 
2854  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
2855  ereport(ERROR,
2856  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2857  errmsg("timestamp out of range")));
2858 
2859  tm->tm_mon += span->month;
2860  if (tm->tm_mon > MONTHS_PER_YEAR)
2861  {
2862  tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
2863  tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
2864  }
2865  else if (tm->tm_mon < 1)
2866  {
2867  tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
2868  tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR + MONTHS_PER_YEAR;
2869  }
2870 
2871  /* adjust for end of month boundary problems... */
2872  if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
2873  tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
2874 
2875  if (tm2timestamp(tm, fsec, NULL, &timestamp) != 0)
2876  ereport(ERROR,
2877  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2878  errmsg("timestamp out of range")));
2879  }
2880 
2881  if (span->day != 0)
2882  {
2883  struct pg_tm tt,
2884  *tm = &tt;
2885  fsec_t fsec;
2886  int julian;
2887 
2888  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
2889  ereport(ERROR,
2890  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2891  errmsg("timestamp out of range")));
2892 
2893  /* Add days by converting to and from Julian */
2894  julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
2895  j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
2896 
2897  if (tm2timestamp(tm, fsec, NULL, &timestamp) != 0)
2898  ereport(ERROR,
2899  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2900  errmsg("timestamp out of range")));
2901  }
2902 
2903  timestamp += span->time;
2904 
2905  if (!IS_VALID_TIMESTAMP(timestamp))
2906  ereport(ERROR,
2907  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2908  errmsg("timestamp out of range")));
2909 
2910  result = timestamp;
2911  }
2912 
2913  PG_RETURN_TIMESTAMP(result);
2914 }
2915 
2916 Datum
2917 timestamp_mi_interval(PG_FUNCTION_ARGS)
2918 {
2919  Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
2920  Interval *span = PG_GETARG_INTERVAL_P(1);
2921  Interval tspan;
2922 
2923  tspan.month = -span->month;
2924  tspan.day = -span->day;
2925  tspan.time = -span->time;
2926 
2927  return DirectFunctionCall2(timestamp_pl_interval,
2928  TimestampGetDatum(timestamp),
2929  PointerGetDatum(&tspan));
2930 }
2931 
2932 
2933 /* timestamptz_pl_interval()
2934  * Add an interval to a timestamp with time zone data type.
2935  * Note that interval has provisions for qualitative year/month
2936  * units, so try to do the right thing with them.
2937  * To add a month, increment the month, and use the same day of month.
2938  * Then, if the next month has fewer days, set the day of month
2939  * to the last day of month.
2940  * Lastly, add in the "quantitative time".
2941  */
2942 Datum
2943 timestamptz_pl_interval(PG_FUNCTION_ARGS)
2944 {
2945  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
2946  Interval *span = PG_GETARG_INTERVAL_P(1);
2947  TimestampTz result;
2948  int tz;
2949 
2950  if (TIMESTAMP_NOT_FINITE(timestamp))
2951  result = timestamp;
2952  else
2953  {
2954  if (span->month != 0)
2955  {
2956  struct pg_tm tt,
2957  *tm = &tt;
2958  fsec_t fsec;
2959 
2960  if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
2961  ereport(ERROR,
2962  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2963  errmsg("timestamp out of range")));
2964 
2965  tm->tm_mon += span->month;
2966  if (tm->tm_mon > MONTHS_PER_YEAR)
2967  {
2968  tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
2969  tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
2970  }
2971  else if (tm->tm_mon < 1)
2972  {
2973  tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
2974  tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR + MONTHS_PER_YEAR;
2975  }
2976 
2977  /* adjust for end of month boundary problems... */
2978  if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
2979  tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
2980 
2981  tz = DetermineTimeZoneOffset(tm, session_timezone);
2982 
2983  if (tm2timestamp(tm, fsec, &tz, &timestamp) != 0)
2984  ereport(ERROR,
2985  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2986  errmsg("timestamp out of range")));
2987  }
2988 
2989  if (span->day != 0)
2990  {
2991  struct pg_tm tt,
2992  *tm = &tt;
2993  fsec_t fsec;
2994  int julian;
2995 
2996  if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
2997  ereport(ERROR,
2998  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2999  errmsg("timestamp out of range")));
3000 
3001  /* Add days by converting to and from Julian */
3002  julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
3003  j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
3004 
3005  tz = DetermineTimeZoneOffset(tm, session_timezone);
3006 
3007  if (tm2timestamp(tm, fsec, &tz, &timestamp) != 0)
3008  ereport(ERROR,
3009  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3010  errmsg("timestamp out of range")));
3011  }
3012 
3013  timestamp += span->time;
3014 
3015  if (!IS_VALID_TIMESTAMP(timestamp))
3016  ereport(ERROR,
3017  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3018  errmsg("timestamp out of range")));
3019 
3020  result = timestamp;
3021  }
3022 
3023  PG_RETURN_TIMESTAMP(result);
3024 }
3025 
3026 Datum
3027 timestamptz_mi_interval(PG_FUNCTION_ARGS)
3028 {
3029  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
3030  Interval *span = PG_GETARG_INTERVAL_P(1);
3031  Interval tspan;
3032 
3033  tspan.month = -span->month;
3034  tspan.day = -span->day;
3035  tspan.time = -span->time;
3036 
3037  return DirectFunctionCall2(timestamptz_pl_interval,
3038  TimestampGetDatum(timestamp),
3039  PointerGetDatum(&tspan));
3040 }
3041 
3042 
3043 Datum
3044 interval_um(PG_FUNCTION_ARGS)
3045 {
3046  Interval *interval = PG_GETARG_INTERVAL_P(0);
3047  Interval *result;
3048 
3049  result = (Interval *) palloc(sizeof(Interval));
3050 
3051  result->time = -interval->time;
3052  /* overflow check copied from int4um */
3053  if (interval->time != 0 && SAMESIGN(result->time, interval->time))
3054  ereport(ERROR,
3055  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3056  errmsg("interval out of range")));
3057  result->day = -interval->day;
3058  if (interval->day != 0 && SAMESIGN(result->day, interval->day))
3059  ereport(ERROR,
3060  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3061  errmsg("interval out of range")));
3062  result->month = -interval->month;
3063  if (interval->month != 0 && SAMESIGN(result->month, interval->month))
3064  ereport(ERROR,
3065  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3066  errmsg("interval out of range")));
3067 
3068  PG_RETURN_INTERVAL_P(result);
3069 }
3070 
3071 
3072 Datum
3073 interval_smaller(PG_FUNCTION_ARGS)
3074 {
3075  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
3076  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
3077  Interval *result;
3078 
3079  /* use interval_cmp_internal to be sure this agrees with comparisons */
3080  if (interval_cmp_internal(interval1, interval2) < 0)
3081  result = interval1;
3082  else
3083  result = interval2;
3084  PG_RETURN_INTERVAL_P(result);
3085 }
3086 
3087 Datum
3088 interval_larger(PG_FUNCTION_ARGS)
3089 {
3090  Interval *interval1 = PG_GETARG_INTERVAL_P(0);
3091  Interval *interval2 = PG_GETARG_INTERVAL_P(1);
3092  Interval *result;
3093 
3094  if (interval_cmp_internal(interval1, interval2) > 0)
3095  result = interval1;
3096  else
3097  result = interval2;
3098  PG_RETURN_INTERVAL_P(result);
3099 }
3100 
3101 Datum
3102 interval_pl(PG_FUNCTION_ARGS)
3103 {
3104  Interval *span1 = PG_GETARG_INTERVAL_P(0);
3105  Interval *span2 = PG_GETARG_INTERVAL_P(1);
3106  Interval *result;
3107 
3108  result = (Interval *) palloc(sizeof(Interval));
3109 
3110  result->month = span1->month + span2->month;
3111  /* overflow check copied from int4pl */
3112  if (SAMESIGN(span1->month, span2->month) &&
3113  !SAMESIGN(result->month, span1->month))
3114  ereport(ERROR,
3115  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3116  errmsg("interval out of range")));
3117 
3118  result->day = span1->day + span2->day;
3119  if (SAMESIGN(span1->day, span2->day) &&
3120  !SAMESIGN(result->day, span1->day))
3121  ereport(ERROR,
3122  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3123  errmsg("interval out of range")));
3124 
3125  result->time = span1->time + span2->time;
3126  if (SAMESIGN(span1->time, span2->time) &&
3127  !SAMESIGN(result->time, span1->time))
3128  ereport(ERROR,
3129  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3130  errmsg("interval out of range")));
3131 
3132  PG_RETURN_INTERVAL_P(result);
3133 }
3134 
3135 Datum
3136 interval_mi(PG_FUNCTION_ARGS)
3137 {
3138  Interval *span1 = PG_GETARG_INTERVAL_P(0);
3139  Interval *span2 = PG_GETARG_INTERVAL_P(1);
3140  Interval *result;
3141 
3142  result = (Interval *) palloc(sizeof(Interval));
3143 
3144  result->month = span1->month - span2->month;
3145  /* overflow check copied from int4mi */
3146  if (!SAMESIGN(span1->month, span2->month) &&
3147  !SAMESIGN(result->month, span1->month))
3148  ereport(ERROR,
3149  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3150  errmsg("interval out of range")));
3151 
3152  result->day = span1->day - span2->day;
3153  if (!SAMESIGN(span1->day, span2->day) &&
3154  !SAMESIGN(result->day, span1->day))
3155  ereport(ERROR,
3156  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3157  errmsg("interval out of range")));
3158 
3159  result->time = span1->time - span2->time;
3160  if (!SAMESIGN(span1->time, span2->time) &&
3161  !SAMESIGN(result->time, span1->time))
3162  ereport(ERROR,
3163  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3164  errmsg("interval out of range")));
3165 
3166  PG_RETURN_INTERVAL_P(result);
3167 }
3168 
3169 /*
3170  * There is no interval_abs(): it is unclear what value to return:
3171  * http://archives.postgresql.org/pgsql-general/2009-10/msg01031.php
3172  * http://archives.postgresql.org/pgsql-general/2009-11/msg00041.php
3173  */
3174 
3175 Datum
3176 interval_mul(PG_FUNCTION_ARGS)
3177 {
3178  Interval *span = PG_GETARG_INTERVAL_P(0);
3179  float8 factor = PG_GETARG_FLOAT8(1);
3180  double month_remainder_days,
3181  sec_remainder,
3182  result_double;
3183  int32 orig_month = span->month,
3184  orig_day = span->day;
3185  Interval *result;
3186 
3187  result = (Interval *) palloc(sizeof(Interval));
3188 
3189  result_double = span->month * factor;
3190  if (isnan(result_double) ||
3191  result_double > INT_MAX || result_double < INT_MIN)
3192  ereport(ERROR,
3193  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3194  errmsg("interval out of range")));
3195  result->month = (int32) result_double;
3196 
3197  result_double = span->day * factor;
3198  if (isnan(result_double) ||
3199  result_double > INT_MAX || result_double < INT_MIN)
3200  ereport(ERROR,
3201  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3202  errmsg("interval out of range")));
3203  result->day = (int32) result_double;
3204 
3205  /*
3206  * The above correctly handles the whole-number part of the month and day
3207  * products, but we have to do something with any fractional part
3208  * resulting when the factor is non-integral. We cascade the fractions
3209  * down to lower units using the conversion factors DAYS_PER_MONTH and
3210  * SECS_PER_DAY. Note we do NOT cascade up, since we are not forced to do
3211  * so by the representation. The user can choose to cascade up later,
3212  * using justify_hours and/or justify_days.
3213  */
3214 
3215  /*
3216  * Fractional months full days into days.
3217  *
3218  * Floating point calculation are inherently imprecise, so these
3219  * calculations are crafted to produce the most reliable result possible.
3220  * TSROUND() is needed to more accurately produce whole numbers where
3221  * appropriate.
3222  */
3223  month_remainder_days = (orig_month * factor - result->month) * DAYS_PER_MONTH;
3224  month_remainder_days = TSROUND(month_remainder_days);
3225  sec_remainder = (orig_day * factor - result->day +
3226  month_remainder_days - (int) month_remainder_days) * SECS_PER_DAY;
3227  sec_remainder = TSROUND(sec_remainder);
3228 
3229  /*
3230  * Might have 24:00:00 hours due to rounding, or >24 hours because of time
3231  * cascade from months and days. It might still be >24 if the combination
3232  * of cascade and the seconds factor operation itself.
3233  */
3234  if (Abs(sec_remainder) >= SECS_PER_DAY)
3235  {
3236  result->day += (int) (sec_remainder / SECS_PER_DAY);
3237  sec_remainder -= (int) (sec_remainder / SECS_PER_DAY) * SECS_PER_DAY;
3238  }
3239 
3240  /* cascade units down */
3241  result->day += (int32) month_remainder_days;
3242  result_double = rint(span->time * factor + sec_remainder * USECS_PER_SEC);
3243  if (isnan(result_double) || !FLOAT8_FITS_IN_INT64(result_double))
3244  ereport(ERROR,
3245  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3246  errmsg("interval out of range")));
3247  result->time = (int64) result_double;
3248 
3249  PG_RETURN_INTERVAL_P(result);
3250 }
3251 
3252 Datum
3253 mul_d_interval(PG_FUNCTION_ARGS)
3254 {
3255  /* Args are float8 and Interval *, but leave them as generic Datum */
3256  Datum factor = PG_GETARG_DATUM(0);
3257  Datum span = PG_GETARG_DATUM(1);
3258 
3259  return DirectFunctionCall2(interval_mul, span, factor);
3260 }
3261 
3262 Datum
3263 interval_div(PG_FUNCTION_ARGS)
3264 {
3265  Interval *span = PG_GETARG_INTERVAL_P(0);
3266  float8 factor = PG_GETARG_FLOAT8(1);
3267  double month_remainder_days,
3268  sec_remainder;
3269  int32 orig_month = span->month,
3270  orig_day = span->day;
3271  Interval *result;
3272 
3273  result = (Interval *) palloc(sizeof(Interval));
3274 
3275  if (factor == 0.0)
3276  ereport(ERROR,
3277  (errcode(ERRCODE_DIVISION_BY_ZERO),
3278  errmsg("division by zero")));
3279 
3280  result->month = (int32) (span->month / factor);
3281  result->day = (int32) (span->day / factor);
3282 
3283  /*
3284  * Fractional months full days into days. See comment in interval_mul().
3285  */
3286  month_remainder_days = (orig_month / factor - result->month) * DAYS_PER_MONTH;
3287  month_remainder_days = TSROUND(month_remainder_days);
3288  sec_remainder = (orig_day / factor - result->day +
3289  month_remainder_days - (int) month_remainder_days) * SECS_PER_DAY;
3290  sec_remainder = TSROUND(sec_remainder);
3291  if (Abs(sec_remainder) >= SECS_PER_DAY)
3292  {
3293  result->day += (int) (sec_remainder / SECS_PER_DAY);
3294  sec_remainder -= (int) (sec_remainder / SECS_PER_DAY) * SECS_PER_DAY;
3295  }
3296 
3297  /* cascade units down */
3298  result->day += (int32) month_remainder_days;
3299  result->time = rint(span->time / factor + sec_remainder * USECS_PER_SEC);
3300 
3301  PG_RETURN_INTERVAL_P(result);
3302 }
3303 
3304 
3305 /*
3306  * in_range support functions for timestamps and intervals.
3307  *
3308  * Per SQL spec, we support these with interval as the offset type.
3309  * The spec's restriction that the offset not be negative is a bit hard to
3310  * decipher for intervals, but we choose to interpret it the same as our
3311  * interval comparison operators would.
3312  */
3313 
3314 Datum
3315 in_range_timestamptz_interval(PG_FUNCTION_ARGS)
3316 {
3317  TimestampTz val = PG_GETARG_TIMESTAMPTZ(0);
3318  TimestampTz base = PG_GETARG_TIMESTAMPTZ(1);
3319  Interval *offset = PG_GETARG_INTERVAL_P(2);
3320  bool sub = PG_GETARG_BOOL(3);
3321  bool less = PG_GETARG_BOOL(4);
3322  TimestampTz sum;
3323 
3324  if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0)
3325  ereport(ERROR,
3326  (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3327  errmsg("invalid preceding or following size in window function")));
3328 
3329  /* We don't currently bother to avoid overflow hazards here */
3330  if (sub)
3331  sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_mi_interval,
3332  TimestampTzGetDatum(base),
3333  IntervalPGetDatum(offset)));
3334  else
3335  sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval,
3336  TimestampTzGetDatum(base),
3337  IntervalPGetDatum(offset)));
3338 
3339  if (less)
3340  PG_RETURN_BOOL(val <= sum);
3341  else
3342  PG_RETURN_BOOL(val >= sum);
3343 }
3344 
3345 Datum
3346 in_range_timestamp_interval(PG_FUNCTION_ARGS)
3347 {
3348  Timestamp val = PG_GETARG_TIMESTAMP(0);
3349  Timestamp base = PG_GETARG_TIMESTAMP(1);
3350  Interval *offset = PG_GETARG_INTERVAL_P(2);
3351  bool sub = PG_GETARG_BOOL(3);
3352  bool less = PG_GETARG_BOOL(4);
3353  Timestamp sum;
3354 
3355  if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0)
3356  ereport(ERROR,
3357  (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3358  errmsg("invalid preceding or following size in window function")));
3359 
3360  /* We don't currently bother to avoid overflow hazards here */
3361  if (sub)
3362  sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_mi_interval,
3363  TimestampGetDatum(base),
3364  IntervalPGetDatum(offset)));
3365  else
3366  sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_pl_interval,
3367  TimestampGetDatum(base),
3368  IntervalPGetDatum(offset)));
3369 
3370  if (less)
3371  PG_RETURN_BOOL(val <= sum);
3372  else
3373  PG_RETURN_BOOL(val >= sum);
3374 }
3375 
3376 Datum
3377 in_range_interval_interval(PG_FUNCTION_ARGS)
3378 {
3379  Interval *val = PG_GETARG_INTERVAL_P(0);
3380  Interval *base = PG_GETARG_INTERVAL_P(1);
3381  Interval *offset = PG_GETARG_INTERVAL_P(2);
3382  bool sub = PG_GETARG_BOOL(3);
3383  bool less = PG_GETARG_BOOL(4);
3384  Interval *sum;
3385 
3386  if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0)
3387  ereport(ERROR,
3388  (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3389  errmsg("invalid preceding or following size in window function")));
3390 
3391  /* We don't currently bother to avoid overflow hazards here */
3392  if (sub)
3393  sum = DatumGetIntervalP(DirectFunctionCall2(interval_mi,
3394  IntervalPGetDatum(base),
3395  IntervalPGetDatum(offset)));
3396  else
3397  sum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3398  IntervalPGetDatum(base),
3399  IntervalPGetDatum(offset)));
3400 
3401  if (less)
3402  PG_RETURN_BOOL(interval_cmp_internal(val, sum) <= 0);
3403  else
3404  PG_RETURN_BOOL(interval_cmp_internal(val, sum) >= 0);
3405 }
3406 
3407 
3408 /*
3409  * interval_accum, interval_accum_inv, and interval_avg implement the
3410  * AVG(interval) aggregate.
3411  *
3412  * The transition datatype for this aggregate is a 2-element array of
3413  * intervals, where the first is the running sum and the second contains
3414  * the number of values so far in its 'time' field. This is a bit ugly
3415  * but it beats inventing a specialized datatype for the purpose.
3416  */
3417 
3418 Datum
3419 interval_accum(PG_FUNCTION_ARGS)
3420 {
3421  ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
3422  Interval *newval = PG_GETARG_INTERVAL_P(1);
3423  Datum *transdatums;
3424  int ndatums;
3425  Interval sumX,
3426  N;
3427  Interval *newsum;
3428  ArrayType *result;
3429 
3430  deconstruct_array(transarray,
3431  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE,
3432  &transdatums, NULL, &ndatums);
3433  if (ndatums != 2)
3434  elog(ERROR, "expected 2-element interval array");
3435 
3436  sumX = *(DatumGetIntervalP(transdatums[0]));
3437  N = *(DatumGetIntervalP(transdatums[1]));
3438 
3439  newsum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3440  IntervalPGetDatum(&sumX),
3441  IntervalPGetDatum(newval)));
3442  N.time += 1;
3443 
3444  transdatums[0] = IntervalPGetDatum(newsum);
3445  transdatums[1] = IntervalPGetDatum(&N);
3446 
3447  result = construct_array(transdatums, 2,
3448  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE);
3449 
3450  PG_RETURN_ARRAYTYPE_P(result);
3451 }
3452 
3453 Datum
3454 interval_combine(PG_FUNCTION_ARGS)
3455 {
3456  ArrayType *transarray1 = PG_GETARG_ARRAYTYPE_P(0);
3457  ArrayType *transarray2 = PG_GETARG_ARRAYTYPE_P(1);
3458  Datum *transdatums1;
3459  Datum *transdatums2;
3460  int ndatums1;
3461  int ndatums2;
3462  Interval sum1,
3463  N1;
3464  Interval sum2,
3465  N2;
3466 
3467  Interval *newsum;
3468  ArrayType *result;
3469 
3470  deconstruct_array(transarray1,
3471  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE,
3472  &transdatums1, NULL, &ndatums1);
3473  if (ndatums1 != 2)
3474  elog(ERROR, "expected 2-element interval array");
3475 
3476  sum1 = *(DatumGetIntervalP(transdatums1[0]));
3477  N1 = *(DatumGetIntervalP(transdatums1[1]));
3478 
3479  deconstruct_array(transarray2,
3480  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE,
3481  &transdatums2, NULL, &ndatums2);
3482  if (ndatums2 != 2)
3483  elog(ERROR, "expected 2-element interval array");
3484 
3485  sum2 = *(DatumGetIntervalP(transdatums2[0]));
3486  N2 = *(DatumGetIntervalP(transdatums2[1]));
3487 
3488  newsum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3489  IntervalPGetDatum(&sum1),
3490  IntervalPGetDatum(&sum2)));
3491  N1.time += N2.time;
3492 
3493  transdatums1[0] = IntervalPGetDatum(newsum);
3494  transdatums1[1] = IntervalPGetDatum(&N1);
3495 
3496  result = construct_array(transdatums1, 2,
3497  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE);
3498 
3499  PG_RETURN_ARRAYTYPE_P(result);
3500 }
3501 
3502 Datum
3503 interval_accum_inv(PG_FUNCTION_ARGS)
3504 {
3505  ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
3506  Interval *newval = PG_GETARG_INTERVAL_P(1);
3507  Datum *transdatums;
3508  int ndatums;
3509  Interval sumX,
3510  N;
3511  Interval *newsum;
3512  ArrayType *result;
3513 
3514  deconstruct_array(transarray,
3515  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE,
3516  &transdatums, NULL, &ndatums);
3517  if (ndatums != 2)
3518  elog(ERROR, "expected 2-element interval array");
3519 
3520  sumX = *(DatumGetIntervalP(transdatums[0]));
3521  N = *(DatumGetIntervalP(transdatums[1]));
3522 
3523  newsum = DatumGetIntervalP(DirectFunctionCall2(interval_mi,
3524  IntervalPGetDatum(&sumX),
3525  IntervalPGetDatum(newval)));
3526  N.time -= 1;
3527 
3528  transdatums[0] = IntervalPGetDatum(newsum);
3529  transdatums[1] = IntervalPGetDatum(&N);
3530 
3531  result = construct_array(transdatums, 2,
3532  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE);
3533 
3534  PG_RETURN_ARRAYTYPE_P(result);
3535 }
3536 
3537 Datum
3538 interval_avg(PG_FUNCTION_ARGS)
3539 {
3540  ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
3541  Datum *transdatums;
3542  int ndatums;
3543  Interval sumX,
3544  N;
3545 
3546  deconstruct_array(transarray,
3547  INTERVALOID, sizeof(Interval), false, TYPALIGN_DOUBLE,
3548  &transdatums, NULL, &ndatums);
3549  if (ndatums != 2)
3550  elog(ERROR, "expected 2-element interval array");
3551 
3552  sumX = *(DatumGetIntervalP(transdatums[0]));
3553  N = *(DatumGetIntervalP(transdatums[1]));
3554 
3555  /* SQL defines AVG of no values to be NULL */
3556  if (N.time == 0)
3557  PG_RETURN_NULL();
3558 
3559  return DirectFunctionCall2(interval_div,
3560  IntervalPGetDatum(&sumX),
3561  Float8GetDatum((double) N.time));
3562 }
3563 
3564 
3565 /* timestamp_age()
3566  * Calculate time difference while retaining year/month fields.
3567  * Note that this does not result in an accurate absolute time span
3568  * since year and month are out of context once the arithmetic
3569  * is done.
3570  */
3571 Datum
3572 timestamp_age(PG_FUNCTION_ARGS)
3573 {
3574  Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
3575  Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
3576  Interval *result;
3577  fsec_t fsec,
3578  fsec1,
3579  fsec2;
3580  struct pg_tm tt,
3581  *tm = &tt;
3582  struct pg_tm tt1,
3583  *tm1 = &tt1;
3584  struct pg_tm tt2,
3585  *tm2 = &tt2;
3586 
3587  result = (Interval *) palloc(sizeof(Interval));
3588 
3589  if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 &&
3590  timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
3591  {
3592  /* form the symbolic difference */
3593  fsec = fsec1 - fsec2;
3594  tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
3595  tm->tm_min = tm1->tm_min - tm2->tm_min;
3596  tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
3597  tm->tm_mday = tm1->tm_mday - tm2->tm_mday;
3598  tm->tm_mon = tm1->tm_mon - tm2->tm_mon;
3599  tm->tm_year = tm1->tm_year - tm2->tm_year;
3600 
3601  /* flip sign if necessary... */
3602  if (dt1 < dt2)
3603  {
3604  fsec = -fsec;
3605  tm->tm_sec = -tm->tm_sec;
3606  tm->tm_min = -tm->tm_min;
3607  tm->tm_hour = -tm->tm_hour;
3608  tm->tm_mday = -tm->tm_mday;
3609  tm->tm_mon = -tm->tm_mon;
3610  tm->tm_year = -tm->tm_year;
3611  }
3612 
3613  /* propagate any negative fields into the next higher field */
3614  while (fsec < 0)
3615  {
3616  fsec += USECS_PER_SEC;
3617  tm->tm_sec--;
3618  }
3619 
3620  while (tm->tm_sec < 0)
3621  {
3622  tm->tm_sec += SECS_PER_MINUTE;
3623  tm->tm_min--;
3624  }
3625 
3626  while (tm->tm_min < 0)
3627  {
3628  tm->tm_min += MINS_PER_HOUR;
3629  tm->tm_hour--;
3630  }
3631 
3632  while (tm->tm_hour < 0)
3633  {
3634  tm->tm_hour += HOURS_PER_DAY;
3635  tm->tm_mday--;
3636  }
3637 
3638  while (tm->tm_mday < 0)
3639  {
3640  if (dt1 < dt2)
3641  {
3642  tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
3643  tm->tm_mon--;
3644  }
3645  else
3646  {
3647  tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
3648  tm->tm_mon--;
3649  }
3650  }
3651 
3652  while (tm->tm_mon < 0)
3653  {
3654  tm->tm_mon += MONTHS_PER_YEAR;
3655  tm->tm_year--;
3656  }
3657 
3658  /* recover sign if necessary... */
3659  if (dt1 < dt2)
3660  {
3661  fsec = -fsec;
3662  tm->tm_sec = -tm->tm_sec;
3663  tm->tm_min = -tm->tm_min;
3664  tm->tm_hour = -tm->tm_hour;
3665  tm->tm_mday = -tm->tm_mday;
3666  tm->tm_mon = -tm->tm_mon;
3667  tm->tm_year = -tm->tm_year;
3668  }
3669 
3670  if (tm2interval(tm, fsec, result) != 0)
3671  ereport(ERROR,
3672  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3673  errmsg("interval out of range")));
3674  }
3675  else
3676  ereport(ERROR,
3677  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3678  errmsg("timestamp out of range")));
3679 
3680  PG_RETURN_INTERVAL_P(result);
3681 }
3682 
3683 
3684 /* timestamptz_age()
3685  * Calculate time difference while retaining year/month fields.
3686  * Note that this does not result in an accurate absolute time span
3687  * since year and month are out of context once the arithmetic
3688  * is done.
3689  */
3690 Datum
3691 timestamptz_age(PG_FUNCTION_ARGS)
3692 {
3693  TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
3694  TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
3695  Interval *result;
3696  fsec_t fsec,
3697  fsec1,
3698  fsec2;
3699  struct pg_tm tt,
3700  *tm = &tt;
3701  struct pg_tm tt1,
3702  *tm1 = &tt1;
3703  struct pg_tm tt2,
3704  *tm2 = &tt2;
3705  int tz1;
3706  int tz2;
3707 
3708  result = (Interval *) palloc(sizeof(Interval));
3709 
3710  if (timestamp2tm(dt1, &tz1, tm1, &fsec1, NULL, NULL) == 0 &&
3711  timestamp2tm(dt2, &tz2, tm2, &fsec2, NULL, NULL) == 0)
3712  {
3713  /* form the symbolic difference */
3714  fsec = fsec1 - fsec2;
3715  tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
3716  tm->tm_min = tm1->tm_min - tm2->tm_min;
3717  tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
3718  tm->tm_mday = tm1->tm_mday - tm2->tm_mday;
3719  tm->tm_mon = tm1->tm_mon - tm2->tm_mon;
3720  tm->tm_year = tm1->tm_year - tm2->tm_year;
3721 
3722  /* flip sign if necessary... */
3723  if (dt1 < dt2)
3724  {
3725  fsec = -fsec;
3726  tm->tm_sec = -tm->tm_sec;
3727  tm->tm_min = -tm->tm_min;
3728  tm->tm_hour = -tm->tm_hour;
3729  tm->tm_mday = -tm->tm_mday;
3730  tm->tm_mon = -tm->tm_mon;
3731  tm->tm_year = -tm->tm_year;
3732  }
3733 
3734  /* propagate any negative fields into the next higher field */
3735  while (fsec < 0)
3736  {
3737  fsec += USECS_PER_SEC;
3738  tm->tm_sec--;
3739  }
3740 
3741  while (tm->tm_sec < 0)
3742  {
3743  tm->tm_sec += SECS_PER_MINUTE;
3744  tm->tm_min--;
3745  }
3746 
3747  while (tm->tm_min < 0)
3748  {
3749  tm->tm_min += MINS_PER_HOUR;
3750  tm->tm_hour--;
3751  }
3752 
3753  while (tm->tm_hour < 0)
3754  {
3755  tm->tm_hour += HOURS_PER_DAY;
3756  tm->tm_mday--;
3757  }
3758 
3759  while (tm->tm_mday < 0)
3760  {
3761  if (dt1 < dt2)
3762  {
3763  tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
3764  tm->tm_mon--;
3765  }
3766  else
3767  {
3768  tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
3769  tm->tm_mon--;
3770  }
3771  }
3772 
3773  while (tm->tm_mon < 0)
3774  {
3775  tm->tm_mon += MONTHS_PER_YEAR;
3776  tm->tm_year--;
3777  }
3778 
3779  /*
3780  * Note: we deliberately ignore any difference between tz1 and tz2.
3781  */
3782 
3783  /* recover sign if necessary... */
3784  if (dt1 < dt2)
3785  {
3786  fsec = -fsec;
3787  tm->tm_sec = -tm->tm_sec;
3788  tm->tm_min = -tm->tm_min;
3789  tm->tm_hour = -tm->tm_hour;
3790  tm->tm_mday = -tm->tm_mday;
3791  tm->tm_mon = -tm->tm_mon;
3792  tm->tm_year = -tm->tm_year;
3793  }
3794 
3795  if (tm2interval(tm, fsec, result) != 0)
3796  ereport(ERROR,
3797  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3798  errmsg("interval out of range")));
3799  }
3800  else
3801  ereport(ERROR,
3802  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3803  errmsg("timestamp out of range")));
3804 
3805  PG_RETURN_INTERVAL_P(result);
3806 }
3807 
3808 
3809 /*----------------------------------------------------------
3810  * Conversion operators.
3811  *---------------------------------------------------------*/
3812 
3813 
3814 /* timestamp_bin()
3815  * Bin timestamp into specified interval.
3816  */
3817 Datum
3818 timestamp_bin(PG_FUNCTION_ARGS)
3819 {
3820  Interval *stride = PG_GETARG_INTERVAL_P(0);
3821  Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
3822  Timestamp origin = PG_GETARG_TIMESTAMP(2);
3823  Timestamp result,
3824  tm_diff,
3825  stride_usecs,
3826  tm_delta;
3827 
3828  if (TIMESTAMP_NOT_FINITE(timestamp))
3829  PG_RETURN_TIMESTAMP(timestamp);
3830 
3831  if (TIMESTAMP_NOT_FINITE(origin))
3832  ereport(ERROR,
3833  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3834  errmsg("origin out of range")));
3835 
3836  if (stride->month != 0)
3837  ereport(ERROR,
3838  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3839  errmsg("timestamps cannot be binned into intervals containing months or years")));
3840 
3841  stride_usecs = stride->day * USECS_PER_DAY + stride->time;
3842 
3843  if (stride_usecs <= 0)
3844  ereport(ERROR,
3845  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3846  errmsg("stride must be greater than zero")));
3847 
3848  tm_diff = timestamp - origin;
3849  tm_delta = tm_diff - tm_diff % stride_usecs;
3850 
3851  /*
3852  * Make sure the returned timestamp is at the start of the bin, even if
3853  * the origin is in the future.
3854  */
3855  if (origin > timestamp && stride_usecs > 1)
3856  tm_delta -= stride_usecs;
3857 
3858  result = origin + tm_delta;
3859 
3860  PG_RETURN_TIMESTAMP(result);
3861 }
3862 
3863 /* timestamp_trunc()
3864  * Truncate timestamp to specified units.
3865  */
3866 Datum
3867 timestamp_trunc(PG_FUNCTION_ARGS)
3868 {
3869  text *units = PG_GETARG_TEXT_PP(0);
3870  Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
3871  Timestamp result;
3872  int type,
3873  val;
3874  char *lowunits;
3875  fsec_t fsec;
3876  struct pg_tm tt,
3877  *tm = &tt;
3878 
3879  if (TIMESTAMP_NOT_FINITE(timestamp))
3880  PG_RETURN_TIMESTAMP(timestamp);
3881 
3882  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
3883  VARSIZE_ANY_EXHDR(units),
3884  false);
3885 
3886  type = DecodeUnits(0, lowunits, &val);
3887 
3888  if (type == UNITS)
3889  {
3890  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
3891  ereport(ERROR,
3892  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3893  errmsg("timestamp out of range")));
3894 
3895  switch (val)
3896  {
3897  case DTK_WEEK:
3898  {
3899  int woy;
3900 
3901  woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
3902 
3903  /*
3904  * If it is week 52/53 and the month is January, then the
3905  * week must belong to the previous year. Also, some
3906  * December dates belong to the next year.
3907  */
3908  if (woy >= 52 && tm->tm_mon == 1)
3909  --tm->tm_year;
3910  if (woy <= 1 && tm->tm_mon == MONTHS_PER_YEAR)
3911  ++tm->tm_year;
3912  isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
3913  tm->tm_hour = 0;
3914  tm->tm_min = 0;
3915  tm->tm_sec = 0;
3916  fsec = 0;
3917  break;
3918  }
3919  case DTK_MILLENNIUM:
3920  /* see comments in timestamptz_trunc */
3921  if (tm->tm_year > 0)
3922  tm->tm_year = ((tm->tm_year + 999) / 1000) * 1000 - 999;
3923  else
3924  tm->tm_year = -((999 - (tm->tm_year - 1)) / 1000) * 1000 + 1;
3925  /* FALL THRU */
3926  case DTK_CENTURY:
3927  /* see comments in timestamptz_trunc */
3928  if (tm->tm_year > 0)
3929  tm->tm_year = ((tm->tm_year + 99) / 100) * 100 - 99;
3930  else
3931  tm->tm_year = -((99 - (tm->tm_year - 1)) / 100) * 100 + 1;
3932  /* FALL THRU */
3933  case DTK_DECADE:
3934  /* see comments in timestamptz_trunc */
3935  if (val != DTK_MILLENNIUM && val != DTK_CENTURY)
3936  {
3937  if (tm->tm_year > 0)
3938  tm->tm_year = (tm->tm_year / 10) * 10;
3939  else
3940  tm->tm_year = -((8 - (tm->tm_year - 1)) / 10) * 10;
3941  }
3942  /* FALL THRU */
3943  case DTK_YEAR:
3944  tm->tm_mon = 1;
3945  /* FALL THRU */
3946  case DTK_QUARTER:
3947  tm->tm_mon = (3 * ((tm->tm_mon - 1) / 3)) + 1;
3948  /* FALL THRU */
3949  case DTK_MONTH:
3950  tm->tm_mday = 1;
3951  /* FALL THRU */
3952  case DTK_DAY:
3953  tm->tm_hour = 0;
3954  /* FALL THRU */
3955  case DTK_HOUR:
3956  tm->tm_min = 0;
3957  /* FALL THRU */
3958  case DTK_MINUTE:
3959  tm->tm_sec = 0;
3960  /* FALL THRU */
3961  case DTK_SECOND:
3962  fsec = 0;
3963  break;
3964 
3965  case DTK_MILLISEC:
3966  fsec = (fsec / 1000) * 1000;
3967  break;
3968 
3969  case DTK_MICROSEC:
3970  break;
3971 
3972  default:
3973  ereport(ERROR,
3974  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3975  errmsg("timestamp units \"%s\" not supported",
3976  lowunits)));
3977  result = 0;
3978  }
3979 
3980  if (tm2timestamp(tm, fsec, NULL, &result) != 0)
3981  ereport(ERROR,
3982  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3983  errmsg("timestamp out of range")));
3984  }
3985  else
3986  {
3987  ereport(ERROR,
3988  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3989  errmsg("timestamp units \"%s\" not recognized",
3990  lowunits)));
3991  result = 0;
3992  }
3993 
3994  PG_RETURN_TIMESTAMP(result);
3995 }
3996 
3997 /* timestamptz_bin()
3998  * Bin timestamptz into specified interval using specified origin.
3999  */
4000 Datum
4001 timestamptz_bin(PG_FUNCTION_ARGS)
4002 {
4003  Interval *stride = PG_GETARG_INTERVAL_P(0);
4004  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4005  TimestampTz origin = PG_GETARG_TIMESTAMPTZ(2);
4006  TimestampTz result,
4007  stride_usecs,
4008  tm_diff,
4009  tm_delta;
4010 
4011  if (TIMESTAMP_NOT_FINITE(timestamp))
4012  PG_RETURN_TIMESTAMPTZ(timestamp);
4013 
4014  if (TIMESTAMP_NOT_FINITE(origin))
4015  ereport(ERROR,
4016  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4017  errmsg("origin out of range")));
4018 
4019  if (stride->month != 0)
4020  ereport(ERROR,
4021  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4022  errmsg("timestamps cannot be binned into intervals containing months or years")));
4023 
4024  stride_usecs = stride->day * USECS_PER_DAY + stride->time;
4025 
4026  if (stride_usecs <= 0)
4027  ereport(ERROR,
4028  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4029  errmsg("stride must be greater than zero")));
4030 
4031  tm_diff = timestamp - origin;
4032  tm_delta = tm_diff - tm_diff % stride_usecs;
4033 
4034  /*
4035  * Make sure the returned timestamp is at the start of the bin, even if
4036  * the origin is in the future.
4037  */
4038  if (origin > timestamp && stride_usecs > 1)
4039  tm_delta -= stride_usecs;
4040 
4041  result = origin + tm_delta;
4042 
4043  PG_RETURN_TIMESTAMPTZ(result);
4044 }
4045 
4046 /*
4047  * Common code for timestamptz_trunc() and timestamptz_trunc_zone().
4048  *
4049  * tzp identifies the zone to truncate with respect to. We assume
4050  * infinite timestamps have already been rejected.
4051  */
4052 static TimestampTz
4053 timestamptz_trunc_internal(text *units, TimestampTz timestamp, pg_tz *tzp)
4054 {
4055  TimestampTz result;
4056  int tz;
4057  int type,
4058  val;
4059  bool redotz = false;
4060  char *lowunits;
4061  fsec_t fsec;
4062  struct pg_tm tt,
4063  *tm = &tt;
4064 
4065  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4066  VARSIZE_ANY_EXHDR(units),
4067  false);
4068 
4069  type = DecodeUnits(0, lowunits, &val);
4070 
4071  if (type == UNITS)
4072  {
4073  if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, tzp) != 0)
4074  ereport(ERROR,
4075  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4076  errmsg("timestamp out of range")));
4077 
4078  switch (val)
4079  {
4080  case DTK_WEEK:
4081  {
4082  int woy;
4083 
4084  woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
4085 
4086  /*
4087  * If it is week 52/53 and the month is January, then the
4088  * week must belong to the previous year. Also, some
4089  * December dates belong to the next year.
4090  */
4091  if (woy >= 52 && tm->tm_mon == 1)
4092  --tm->tm_year;
4093  if (woy <= 1 && tm->tm_mon == MONTHS_PER_YEAR)
4094  ++tm->tm_year;
4095  isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
4096  tm->tm_hour = 0;
4097  tm->tm_min = 0;
4098  tm->tm_sec = 0;
4099  fsec = 0;
4100  redotz = true;
4101  break;
4102  }
4103  /* one may consider DTK_THOUSAND and DTK_HUNDRED... */
4104  case DTK_MILLENNIUM:
4105 
4106  /*
4107  * truncating to the millennium? what is this supposed to
4108  * mean? let us put the first year of the millennium... i.e.
4109  * -1000, 1, 1001, 2001...
4110  */
4111  if (tm->tm_year > 0)
4112  tm->tm_year = ((tm->tm_year + 999) / 1000) * 1000 - 999;
4113  else
4114  tm->tm_year = -((999 - (tm->tm_year - 1)) / 1000) * 1000 + 1;
4115  /* FALL THRU */
4116  case DTK_CENTURY:
4117  /* truncating to the century? as above: -100, 1, 101... */
4118  if (tm->tm_year > 0)
4119  tm->tm_year = ((tm->tm_year + 99) / 100) * 100 - 99;
4120  else
4121  tm->tm_year = -((99 - (tm->tm_year - 1)) / 100) * 100 + 1;
4122  /* FALL THRU */
4123  case DTK_DECADE:
4124 
4125  /*
4126  * truncating to the decade? first year of the decade. must
4127  * not be applied if year was truncated before!
4128  */
4129  if (val != DTK_MILLENNIUM && val != DTK_CENTURY)
4130  {
4131  if (tm->tm_year > 0)
4132  tm->tm_year = (tm->tm_year / 10) * 10;
4133  else
4134  tm->tm_year = -((8 - (tm->tm_year - 1)) / 10) * 10;
4135  }
4136  /* FALL THRU */
4137  case DTK_YEAR:
4138  tm->tm_mon = 1;
4139  /* FALL THRU */
4140  case DTK_QUARTER:
4141  tm->tm_mon = (3 * ((tm->tm_mon - 1) / 3)) + 1;
4142  /* FALL THRU */
4143  case DTK_MONTH:
4144  tm->tm_mday = 1;
4145  /* FALL THRU */
4146  case DTK_DAY:
4147  tm->tm_hour = 0;
4148  redotz = true; /* for all cases >= DAY */
4149  /* FALL THRU */
4150  case DTK_HOUR:
4151  tm->tm_min = 0;
4152  /* FALL THRU */
4153  case DTK_MINUTE:
4154  tm->tm_sec = 0;
4155  /* FALL THRU */
4156  case DTK_SECOND:
4157  fsec = 0;
4158  break;
4159  case DTK_MILLISEC:
4160  fsec = (fsec / 1000) * 1000;
4161  break;
4162  case DTK_MICROSEC:
4163  break;
4164 
4165  default:
4166  ereport(ERROR,
4167  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4168  errmsg("timestamp with time zone units \"%s\" not "
4169  "supported", lowunits)));
4170  result = 0;
4171  }
4172 
4173  if (redotz)
4174  tz = DetermineTimeZoneOffset(tm, tzp);
4175 
4176  if (tm2timestamp(tm, fsec, &tz, &result) != 0)
4177  ereport(ERROR,
4178  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4179  errmsg("timestamp out of range")));
4180  }
4181  else
4182  {
4183  ereport(ERROR,
4184  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4185  errmsg("timestamp with time zone units \"%s\" not recognized",
4186  lowunits)));
4187  result = 0;
4188  }
4189 
4190  return result;
4191 }
4192 
4193 /* timestamptz_trunc()
4194  * Truncate timestamptz to specified units in session timezone.
4195  */
4196 Datum
4197 timestamptz_trunc(PG_FUNCTION_ARGS)
4198 {
4199  text *units = PG_GETARG_TEXT_PP(0);
4200  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4201  TimestampTz result;
4202 
4203  if (TIMESTAMP_NOT_FINITE(timestamp))
4204  PG_RETURN_TIMESTAMPTZ(timestamp);
4205 
4206  result = timestamptz_trunc_internal(units, timestamp, session_timezone);
4207 
4208  PG_RETURN_TIMESTAMPTZ(result);
4209 }
4210 
4211 /* timestamptz_trunc_zone()
4212  * Truncate timestamptz to specified units in specified timezone.
4213  */
4214 Datum
4215 timestamptz_trunc_zone(PG_FUNCTION_ARGS)
4216 {
4217  text *units = PG_GETARG_TEXT_PP(0);
4218  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4219  text *zone = PG_GETARG_TEXT_PP(2);
4220  TimestampTz result;
4221  char tzname[TZ_STRLEN_MAX + 1];
4222  char *lowzone;
4223  int type,
4224  val;
4225  pg_tz *tzp;
4226 
4227  /*
4228  * timestamptz_zone() doesn't look up the zone for infinite inputs, so we
4229  * don't do so here either.
4230  */
4231  if (TIMESTAMP_NOT_FINITE(timestamp))
4232  PG_RETURN_TIMESTAMP(timestamp);
4233 
4234  /*
4235  * Look up the requested timezone (see notes in timestamptz_zone()).
4236  */
4237  text_to_cstring_buffer(zone, tzname, sizeof(tzname));
4238 
4239  /* DecodeTimezoneAbbrev requires lowercase input */
4240  lowzone = downcase_truncate_identifier(tzname,
4241  strlen(tzname),
4242  false);
4243 
4244  type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
4245 
4246  if (type == TZ || type == DTZ)
4247  {
4248  /* fixed-offset abbreviation, get a pg_tz descriptor for that */
4249  tzp = pg_tzset_offset(-val);
4250  }
4251  else if (type == DYNTZ)
4252  {
4253  /* dynamic-offset abbreviation, use its referenced timezone */
4254  }
4255  else
4256  {
4257  /* try it as a full zone name */
4258  tzp = pg_tzset(tzname);
4259  if (!tzp)
4260  ereport(ERROR,
4261  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4262  errmsg("time zone \"%s\" not recognized", tzname)));
4263  }
4264 
4265  result = timestamptz_trunc_internal(units, timestamp, tzp);
4266 
4267  PG_RETURN_TIMESTAMPTZ(result);
4268 }
4269 
4270 /* interval_trunc()
4271  * Extract specified field from interval.
4272  */
4273 Datum
4274 interval_trunc(PG_FUNCTION_ARGS)
4275 {
4276  text *units = PG_GETARG_TEXT_PP(0);
4277  Interval *interval = PG_GETARG_INTERVAL_P(1);
4278  Interval *result;
4279  int type,
4280  val;
4281  char *lowunits;
4282  fsec_t fsec;
4283  struct pg_tm tt,
4284  *tm = &tt;
4285 
4286  result = (Interval *) palloc(sizeof(Interval));
4287 
4288  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4289  VARSIZE_ANY_EXHDR(units),
4290  false);
4291 
4292  type = DecodeUnits(0, lowunits, &val);
4293 
4294  if (type == UNITS)
4295  {
4296  if (interval2tm(*interval, tm, &fsec) == 0)
4297  {
4298  switch (val)
4299  {
4300  case DTK_MILLENNIUM:
4301  /* caution: C division may have negative remainder */
4302  tm->tm_year = (tm->tm_year / 1000) * 1000;
4303  /* FALL THRU */
4304  case DTK_CENTURY:
4305  /* caution: C division may have negative remainder */
4306  tm->tm_year = (tm->tm_year / 100) * 100;
4307  /* FALL THRU */
4308  case DTK_DECADE:
4309  /* caution: C division may have negative remainder */
4310  tm->tm_year = (tm->tm_year / 10) * 10;
4311  /* FALL THRU */
4312  case DTK_YEAR:
4313  tm->tm_mon = 0;
4314  /* FALL THRU */
4315  case DTK_QUARTER:
4316  tm->tm_mon = 3 * (tm->tm_mon / 3);
4317  /* FALL THRU */
4318  case DTK_MONTH:
4319  tm->tm_mday = 0;
4320  /* FALL THRU */
4321  case DTK_DAY:
4322  tm->tm_hour = 0;
4323  /* FALL THRU */
4324  case DTK_HOUR:
4325  tm->tm_min = 0;
4326  /* FALL THRU */
4327  case DTK_MINUTE:
4328  tm->tm_sec = 0;
4329  /* FALL THRU */
4330  case DTK_SECOND:
4331  fsec = 0;
4332  break;
4333  case DTK_MILLISEC:
4334  fsec = (fsec / 1000) * 1000;
4335  break;
4336  case DTK_MICROSEC:
4337  break;
4338 
4339  default:
4340  if (val == DTK_WEEK)
4341  ereport(ERROR,
4342  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4343  errmsg("interval units \"%s\" not supported "
4344  "because months usually have fractional weeks",
4345  lowunits)));
4346  else
4347  ereport(ERROR,
4348  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4349  errmsg("interval units \"%s\" not supported",
4350  lowunits)));
4351  }
4352 
4353  if (tm2interval(tm, fsec, result) != 0)
4354  ereport(ERROR,
4355  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4356  errmsg("interval out of range")));
4357  }
4358  else
4359  elog(ERROR, "could not convert interval to tm");
4360  }
4361  else
4362  {
4363  ereport(ERROR,
4364  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4365  errmsg("interval units \"%s\" not recognized",
4366  lowunits)));
4367  }
4368 
4369  PG_RETURN_INTERVAL_P(result);
4370 }
4371 
4372 /* isoweek2j()
4373  *
4374  * Return the Julian day which corresponds to the first day (Monday) of the given ISO 8601 year and week.
4375  * Julian days are used to convert between ISO week dates and Gregorian dates.
4376  */
4377 int
4378 isoweek2j(int year, int week)
4379 {
4380  int day0,
4381  day4;
4382 
4383  /* fourth day of current year */
4384  day4 = date2j(year, 1, 4);
4385 
4386  /* day0 == offset to first day of week (Monday) */
4387  day0 = j2day(day4 - 1);
4388 
4389  return ((week - 1) * 7) + (day4 - day0);
4390 }
4391 
4392 /* isoweek2date()
4393  * Convert ISO week of year number to date.
4394  * The year field must be specified with the ISO year!
4395  * karel 2000/08/07
4396  */
4397 void
4398 isoweek2date(int woy, int *year, int *mon, int *mday)
4399 {
4400  j2date(isoweek2j(*year, woy), year, mon, mday);
4401 }
4402 
4403 /* isoweekdate2date()
4404  *
4405  * Convert an ISO 8601 week date (ISO year, ISO week) into a Gregorian date.
4406  * Gregorian day of week sent so weekday strings can be supplied.
4407  * Populates year, mon, and mday with the correct Gregorian values.
4408  * year must be passed in as the ISO year.
4409  */
4410 void
4411 isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday)
4412 {
4413  int jday;
4414 
4415  jday = isoweek2j(*year, isoweek);
4416  /* convert Gregorian week start (Sunday=1) to ISO week start (Monday=1) */
4417  if (wday > 1)
4418  jday += wday - 2;
4419  else
4420  jday += 6;
4421  j2date(jday, year, mon, mday);
4422 }
4423 
4424 /* date2isoweek()
4425  *
4426  * Returns ISO week number of year.
4427  */
4428 int
4429 date2isoweek(int year, int mon, int mday)
4430 {
4431  float8 result;
4432  int day0,
4433  day4,
4434  dayn;
4435 
4436  /* current day */
4437  dayn = date2j(year, mon, mday);
4438 
4439  /* fourth day of current year */
4440  day4 = date2j(year, 1, 4);
4441 
4442  /* day0 == offset to first day of week (Monday) */
4443  day0 = j2day(day4 - 1);
4444 
4445  /*
4446  * We need the first week containing a Thursday, otherwise this day falls
4447  * into the previous year for purposes of counting weeks
4448  */
4449  if (dayn < day4 - day0)
4450  {
4451  day4 = date2j(year - 1, 1, 4);
4452 
4453  /* day0 == offset to first day of week (Monday) */
4454  day0 = j2day(day4 - 1);
4455  }
4456 
4457  result = (dayn - (day4 - day0)) / 7 + 1;
4458 
4459  /*
4460  * Sometimes the last few days in a year will fall into the first week of
4461  * the next year, so check for this.
4462  */
4463  if (result >= 52)
4464  {
4465  day4 = date2j(year + 1, 1, 4);
4466 
4467  /* day0 == offset to first day of week (Monday) */
4468  day0 = j2day(day4 - 1);
4469 
4470  if (dayn >= day4 - day0)
4471  result = (dayn - (day4 - day0)) / 7 + 1;
4472  }
4473 
4474  return (int) result;
4475 }
4476 
4477 
4478 /* date2isoyear()
4479  *
4480  * Returns ISO 8601 year number.
4481  * Note: zero or negative results follow the year-zero-exists convention.
4482  */
4483 int
4484 date2isoyear(int year, int mon, int mday)
4485 {
4486  float8 result;
4487  int day0,
4488  day4,
4489  dayn;
4490 
4491  /* current day */
4492  dayn = date2j(year, mon, mday);
4493 
4494  /* fourth day of current year */
4495  day4 = date2j(year, 1, 4);
4496 
4497  /* day0 == offset to first day of week (Monday) */
4498  day0 = j2day(day4 - 1);
4499 
4500  /*
4501  * We need the first week containing a Thursday, otherwise this day falls
4502  * into the previous year for purposes of counting weeks
4503  */
4504  if (dayn < day4 - day0)
4505  {
4506  day4 = date2j(year - 1, 1, 4);
4507 
4508  /* day0 == offset to first day of week (Monday) */
4509  day0 = j2day(day4 - 1);
4510 
4511  year--;
4512  }
4513 
4514  result = (dayn - (day4 - day0)) / 7 + 1;
4515 
4516  /*
4517  * Sometimes the last few days in a year will fall into the first week of
4518  * the next year, so check for this.
4519  */
4520  if (result >= 52)
4521  {
4522  day4 = date2j(year + 1, 1, 4);
4523 
4524  /* day0 == offset to first day of week (Monday) */
4525  day0 = j2day(day4 - 1);
4526 
4527  if (dayn >= day4 - day0)
4528  year++;
4529  }
4530 
4531  return year;
4532 }
4533 
4534 
4535 /* date2isoyearday()
4536  *
4537  * Returns the ISO 8601 day-of-year, given a Gregorian year, month and day.
4538  * Possible return values are 1 through 371 (364 in non-leap years).
4539  */
4540 int
4541 date2isoyearday(int year, int mon, int mday)
4542 {
4543  return date2j(year, mon, mday) - isoweek2j(date2isoyear(year, mon, mday), 1) + 1;
4544 }
4545 
4546 /*
4547  * NonFiniteTimestampTzPart
4548  *
4549  * Used by timestamp_part and timestamptz_part when extracting from infinite
4550  * timestamp[tz]. Returns +/-Infinity if that is the appropriate result,
4551  * otherwise returns zero (which should be taken as meaning to return NULL).
4552  *
4553  * Errors thrown here for invalid units should exactly match those that
4554  * would be thrown in the calling functions, else there will be unexpected
4555  * discrepancies between finite- and infinite-input cases.
4556  */
4557 static float8
4558 NonFiniteTimestampTzPart(int type, int unit, char *lowunits,
4559  bool isNegative, bool isTz)
4560 {
4561  if ((type != UNITS) && (type != RESERV))
4562  {
4563  if (isTz)
4564  ereport(ERROR,
4565  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4566  errmsg("timestamp with time zone units \"%s\" not recognized",
4567  lowunits)));
4568  else
4569  ereport(ERROR,
4570  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4571  errmsg("timestamp units \"%s\" not recognized",
4572  lowunits)));
4573  }
4574 
4575  switch (unit)
4576  {
4577  /* Oscillating units */
4578  case DTK_MICROSEC:
4579  case DTK_MILLISEC:
4580  case DTK_SECOND:
4581  case DTK_MINUTE:
4582  case DTK_HOUR:
4583  case DTK_DAY:
4584  case DTK_MONTH:
4585  case DTK_QUARTER:
4586  case DTK_WEEK:
4587  case DTK_DOW:
4588  case DTK_ISODOW:
4589  case DTK_DOY:
4590  case DTK_TZ:
4591  case DTK_TZ_MINUTE:
4592  case DTK_TZ_HOUR:
4593  return 0.0;
4594 
4595  /* Monotonically-increasing units */
4596  case DTK_YEAR:
4597  case DTK_DECADE:
4598  case DTK_CENTURY:
4599  case DTK_MILLENNIUM:
4600  case DTK_JULIAN:
4601  case DTK_ISOYEAR:
4602  case DTK_EPOCH:
4603  if (isNegative)
4604  return -get_float8_infinity();
4605  else
4606  return get_float8_infinity();
4607 
4608  default:
4609  if (isTz)
4610  ereport(ERROR,
4611  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4612  errmsg("timestamp with time zone units \"%s\" not supported",
4613  lowunits)));
4614  else
4615  ereport(ERROR,
4616  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4617  errmsg("timestamp units \"%s\" not supported",
4618  lowunits)));
4619  return 0.0; /* keep compiler quiet */
4620  }
4621 }
4622 
4623 /* timestamp_part() and extract_timestamp()
4624  * Extract specified field from timestamp.
4625  */
4626 static Datum
4627 timestamp_part_common(PG_FUNCTION_ARGS, bool retnumeric)
4628 {
4629  text *units = PG_GETARG_TEXT_PP(0);
4630  Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
4631  int64 intresult;
4632  Timestamp epoch;
4633  int type,
4634  val;
4635  char *lowunits;
4636  fsec_t fsec;
4637  struct pg_tm tt,
4638  *tm = &tt;
4639 
4640  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4641  VARSIZE_ANY_EXHDR(units),
4642  false);
4643 
4644  type = DecodeUnits(0, lowunits, &val);
4645  if (type == UNKNOWN_FIELD)
4646  type = DecodeSpecial(0, lowunits, &val);
4647 
4648  if (TIMESTAMP_NOT_FINITE(timestamp))
4649  {
4650  double r = NonFiniteTimestampTzPart(type, val, lowunits,
4651  TIMESTAMP_IS_NOBEGIN(timestamp),
4652  false);
4653 
4654  if (r)
4655  {
4656  if (retnumeric)
4657  {
4658  if (r < 0)
4659  return DirectFunctionCall3(numeric_in,
4660  CStringGetDatum("-Infinity"),
4661  ObjectIdGetDatum(InvalidOid),
4662  Int32GetDatum(-1));
4663  else if (r > 0)
4664  return DirectFunctionCall3(numeric_in,
4665  CStringGetDatum("Infinity"),
4666  ObjectIdGetDatum(InvalidOid),
4667  Int32GetDatum(-1));
4668  }
4669  else
4670  PG_RETURN_FLOAT8(r);
4671  }
4672  else
4673  PG_RETURN_NULL();
4674  }
4675 
4676  if (type == UNITS)
4677  {
4678  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
4679  ereport(ERROR,
4680  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4681  errmsg("timestamp out of range")));
4682 
4683  switch (val)
4684  {
4685  case DTK_MICROSEC:
4686  intresult = tm->tm_sec * INT64CONST(1000000) + fsec;
4687  break;
4688 
4689  case DTK_MILLISEC:
4690  if (retnumeric)
4691  /*---
4692  * tm->tm_sec * 1000 + fsec / 1000
4693  * = (tm->tm_sec * 1'000'000 + fsec) / 1000
4694  */
4695  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 3));
4696  else
4697  PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0);
4698  break;
4699 
4700  case DTK_SECOND:
4701  if (retnumeric)
4702  /*---
4703  * tm->tm_sec + fsec / 1'000'000
4704  * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000
4705  */
4706  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 6));
4707  else
4708  PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0);
4709  break;
4710 
4711  case DTK_MINUTE:
4712  intresult = tm->tm_min;
4713  break;
4714 
4715  case DTK_HOUR:
4716  intresult = tm->tm_hour;
4717  break;
4718 
4719  case DTK_DAY:
4720  intresult = tm->tm_mday;
4721  break;
4722 
4723  case DTK_MONTH:
4724  intresult = tm->tm_mon;
4725  break;
4726 
4727  case DTK_QUARTER:
4728  intresult = (tm->tm_mon - 1) / 3 + 1;
4729  break;
4730 
4731  case DTK_WEEK:
4732  intresult = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
4733  break;
4734 
4735  case DTK_YEAR:
4736  if (tm->tm_year > 0)
4737  intresult = tm->tm_year;
4738  else
4739  /* there is no year 0, just 1 BC and 1 AD */
4740  intresult = tm->tm_year - 1;
4741  break;
4742 
4743  case DTK_DECADE:
4744 
4745  /*
4746  * what is a decade wrt dates? let us assume that decade 199
4747  * is 1990 thru 1999... decade 0 starts on year 1 BC, and -1
4748  * is 11 BC thru 2 BC...
4749  */
4750  if (tm->tm_year >= 0)
4751  intresult = tm->tm_year / 10;
4752  else
4753  intresult = -((8 - (tm->tm_year - 1)) / 10);
4754  break;
4755 
4756  case DTK_CENTURY:
4757 
4758  /* ----
4759  * centuries AD, c>0: year in [ (c-1)* 100 + 1 : c*100 ]
4760  * centuries BC, c<0: year in [ c*100 : (c+1) * 100 - 1]
4761  * there is no number 0 century.
4762  * ----
4763  */
4764  if (tm->tm_year > 0)
4765  intresult = (tm->tm_year + 99) / 100;
4766  else
4767  /* caution: C division may have negative remainder */
4768  intresult = -((99 - (tm->tm_year - 1)) / 100);
4769  break;
4770 
4771  case DTK_MILLENNIUM:
4772  /* see comments above. */
4773  if (tm->tm_year > 0)
4774  intresult = (tm->tm_year + 999) / 1000;
4775  else
4776  intresult = -((999 - (tm->tm_year - 1)) / 1000);
4777  break;
4778 
4779  case DTK_JULIAN:
4780  if (retnumeric)
4781  PG_RETURN_NUMERIC(numeric_add_opt_error(int64_to_numeric(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)),
4782  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),
4783  int64_to_numeric(SECS_PER_DAY * INT64CONST(1000000)),
4784  NULL),
4785  NULL));
4786  else
4787  PG_RETURN_FLOAT8(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) +
4788  ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) +
4789  tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY);
4790  break;
4791 
4792  case DTK_ISOYEAR:
4793  intresult = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday);
4794  /* Adjust BC years */
4795  if (intresult <= 0)
4796  intresult -= 1;
4797  break;
4798 
4799  case DTK_DOW:
4800  case DTK_ISODOW:
4801  intresult = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
4802  if (val == DTK_ISODOW && intresult == 0)
4803  intresult = 7;
4804  break;
4805 
4806  case DTK_DOY:
4807  intresult = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)
4808  - date2j(tm->tm_year, 1, 1) + 1);
4809  break;
4810 
4811  case DTK_TZ:
4812  case DTK_TZ_MINUTE:
4813  case DTK_TZ_HOUR:
4814  default:
4815  ereport(ERROR,
4816  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4817  errmsg("timestamp units \"%s\" not supported",
4818  lowunits)));
4819  intresult = 0;
4820  }
4821  }
4822  else if (type == RESERV)
4823  {
4824  switch (val)
4825  {
4826  case DTK_EPOCH:
4827  epoch = SetEpochTimestamp();
4828  /* (timestamp - epoch) / 1000000 */
4829  if (retnumeric)
4830  {
4831  Numeric result;
4832 
4833  if (timestamp < (PG_INT64_MAX + epoch))
4834  result = int64_div_fast_to_numeric(timestamp - epoch, 6);
4835  else
4836  {
4837  result = numeric_div_opt_error(numeric_sub_opt_error(int64_to_numeric(timestamp),
4838  int64_to_numeric(epoch),
4839  NULL),
4840  int64_to_numeric(1000000),
4841  NULL);
4842  result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
4843  NumericGetDatum(result),
4844  Int32GetDatum(6)));
4845  }
4846  PG_RETURN_NUMERIC(result);
4847  }
4848  else
4849  {
4850  float8 result;
4851 
4852  /* try to avoid precision loss in subtraction */
4853  if (timestamp < (PG_INT64_MAX + epoch))
4854  result = (timestamp - epoch) / 1000000.0;
4855  else
4856  result = ((float8) timestamp - epoch) / 1000000.0;
4857  PG_RETURN_FLOAT8(result);
4858  }
4859  break;
4860 
4861  default:
4862  ereport(ERROR,
4863  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4864  errmsg("timestamp units \"%s\" not supported",
4865  lowunits)));
4866  intresult = 0;
4867  }
4868 
4869  }
4870  else
4871  {
4872  ereport(ERROR,
4873  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4874  errmsg("timestamp units \"%s\" not recognized", lowunits)));
4875  intresult = 0;
4876  }
4877 
4878  if (retnumeric)
4879  PG_RETURN_NUMERIC(int64_to_numeric(intresult));
4880  else
4881  PG_RETURN_FLOAT8(intresult);
4882 }
4883 
4884 Datum
4885 timestamp_part(PG_FUNCTION_ARGS)
4886 {
4887  return timestamp_part_common(fcinfo, false);
4888 }
4889 
4890 Datum
4891 extract_timestamp(PG_FUNCTION_ARGS)
4892 {
4893  return timestamp_part_common(fcinfo, true);
4894 }
4895 
4896 /* timestamptz_part() and extract_timestamptz()
4897  * Extract specified field from timestamp with time zone.
4898  */
4899 static Datum
4900 timestamptz_part_common(PG_FUNCTION_ARGS, bool retnumeric)
4901 {
4902  text *units = PG_GETARG_TEXT_PP(0);
4903  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4904  int64 intresult;
4905  Timestamp epoch;
4906  int tz;
4907  int type,
4908  val;
4909  char *lowunits;
4910  fsec_t fsec;
4911  struct pg_tm tt,
4912  *tm = &tt;
4913 
4914  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4915  VARSIZE_ANY_EXHDR(units),
4916  false);
4917 
4918  type = DecodeUnits(0, lowunits, &val);
4919  if (type == UNKNOWN_FIELD)
4920  type = DecodeSpecial(0, lowunits, &val);
4921 
4922  if (TIMESTAMP_NOT_FINITE(timestamp))
4923  {
4924  double r = NonFiniteTimestampTzPart(type, val, lowunits,
4925  TIMESTAMP_IS_NOBEGIN(timestamp),
4926  true);
4927 
4928  if (r)
4929  {
4930  if (retnumeric)
4931  {
4932  if (r < 0)
4933  return DirectFunctionCall3(numeric_in,
4934  CStringGetDatum("-Infinity"),
4935  ObjectIdGetDatum(InvalidOid),
4936  Int32GetDatum(-1));
4937  else if (r > 0)
4938  return DirectFunctionCall3(numeric_in,
4939  CStringGetDatum("Infinity"),
4940  ObjectIdGetDatum(InvalidOid),
4941  Int32GetDatum(-1));
4942  }
4943  else
4944  PG_RETURN_FLOAT8(r);
4945  }
4946  else
4947  PG_RETURN_NULL();
4948  }
4949 
4950  if (type == UNITS)
4951  {
4952  if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
4953  ereport(ERROR,
4954  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4955  errmsg("timestamp out of range")));
4956 
4957  switch (val)
4958  {
4959  case DTK_TZ:
4960  intresult = -tz;
4961  break;
4962 
4963  case DTK_TZ_MINUTE:
4964  intresult = (-tz / SECS_PER_MINUTE) % MINS_PER_HOUR;
4965  break;
4966 
4967  case DTK_TZ_HOUR:
4968  intresult = -tz / SECS_PER_HOUR;
4969  break;
4970 
4971  case DTK_MICROSEC:
4972  intresult = tm->tm_sec * INT64CONST(1000000) + fsec;
4973  break;
4974 
4975  case DTK_MILLISEC:
4976  if (retnumeric)
4977  /*---
4978  * tm->tm_sec * 1000 + fsec / 1000
4979  * = (tm->tm_sec * 1'000'000 + fsec) / 1000
4980  */
4981  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 3));
4982  else
4983  PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0);
4984  break;
4985 
4986  case DTK_SECOND:
4987  if (retnumeric)
4988  /*---
4989  * tm->tm_sec + fsec / 1'000'000
4990  * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000
4991  */
4992  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 6));
4993  else
4994  PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0);
4995  break;
4996 
4997  case DTK_MINUTE:
4998  intresult = tm->tm_min;
4999  break;
5000 
5001  case DTK_HOUR:
5002  intresult = tm->tm_hour;
5003  break;
5004 
5005  case DTK_DAY:
5006  intresult = tm->tm_mday;
5007  break;
5008 
5009  case DTK_MONTH:
5010  intresult = tm->tm_mon;
5011  break;
5012 
5013  case DTK_QUARTER:
5014  intresult = (tm->tm_mon - 1) / 3 + 1;
5015  break;
5016 
5017  case DTK_WEEK:
5018  intresult = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
5019  break;
5020 
5021  case DTK_YEAR:
5022  if (tm->tm_year > 0)
5023  intresult = tm->tm_year;
5024  else
5025  /* there is no year 0, just 1 BC and 1 AD */
5026  intresult = tm->tm_year - 1;
5027  break;
5028 
5029  case DTK_DECADE:
5030  /* see comments in timestamp_part */
5031  if (tm->tm_year > 0)
5032  intresult = tm->tm_year / 10;
5033  else
5034  intresult = -((8 - (tm->tm_year - 1)) / 10);
5035  break;
5036 
5037  case DTK_CENTURY:
5038  /* see comments in timestamp_part */
5039  if (tm->tm_year > 0)
5040  intresult = (tm->tm_year + 99) / 100;
5041  else
5042  intresult = -((99 - (tm->tm_year - 1)) / 100);
5043  break;
5044 
5045  case DTK_MILLENNIUM:
5046  /* see comments in timestamp_part */
5047  if (tm->tm_year > 0)
5048  intresult = (tm->tm_year + 999) / 1000;
5049  else
5050  intresult = -((999 - (tm->tm_year - 1)) / 1000);
5051  break;
5052 
5053  case DTK_JULIAN:
5054  if (retnumeric)
5055  PG_RETURN_NUMERIC(numeric_add_opt_error(int64_to_numeric(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)),
5056  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),
5057  int64_to_numeric(SECS_PER_DAY * INT64CONST(1000000)),
5058  NULL),
5059  NULL));
5060  else
5061  PG_RETURN_FLOAT8(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) +
5062  ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) +
5063  tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY);
5064  break;
5065 
5066  case DTK_ISOYEAR:
5067  intresult = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday);
5068  /* Adjust BC years */
5069  if (intresult <= 0)
5070  intresult -= 1;
5071  break;
5072 
5073  case DTK_DOW:
5074  case DTK_ISODOW:
5075  intresult = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
5076  if (val == DTK_ISODOW && intresult == 0)
5077  intresult = 7;
5078  break;
5079 
5080  case DTK_DOY:
5081  intresult = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)
5082  - date2j(tm->tm_year, 1, 1) + 1);
5083  break;
5084 
5085  default:
5086  ereport(ERROR,
5087  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5088  errmsg("timestamp with time zone units \"%s\" not supported",
5089  lowunits)));
5090  intresult = 0;
5091  }
5092 
5093  }
5094  else if (type == RESERV)
5095  {
5096  switch (val)
5097  {
5098  case DTK_EPOCH:
5099  epoch = SetEpochTimestamp();
5100  /* (timestamp - epoch) / 1000000 */
5101  if (retnumeric)
5102  {
5103  Numeric result;
5104 
5105  if (timestamp < (PG_INT64_MAX + epoch))
5106  result = int64_div_fast_to_numeric(timestamp - epoch, 6);
5107  else
5108  {
5109  result = numeric_div_opt_error(numeric_sub_opt_error(int64_to_numeric(timestamp),
5110  int64_to_numeric(epoch),
5111  NULL),
5112  int64_to_numeric(1000000),
5113  NULL);
5114  result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
5115  NumericGetDatum(result),
5116  Int32GetDatum(6)));
5117  }
5118  PG_RETURN_NUMERIC(result);
5119  }
5120  else
5121  {
5122  float8 result;
5123 
5124  /* try to avoid precision loss in subtraction */
5125  if (timestamp < (PG_INT64_MAX + epoch))
5126  result = (timestamp - epoch) / 1000000.0;
5127  else
5128  result = ((float8) timestamp - epoch) / 1000000.0;
5129  PG_RETURN_FLOAT8(result);
5130  }
5131  break;
5132 
5133  default:
5134  ereport(ERROR,
5135  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5136  errmsg("timestamp with time zone units \"%s\" not supported",
5137  lowunits)));
5138  intresult = 0;
5139  }
5140  }
5141  else
5142  {
5143  ereport(ERROR,
5144  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5145  errmsg("timestamp with time zone units \"%s\" not recognized",
5146  lowunits)));
5147 
5148  intresult = 0;
5149  }
5150 
5151  if (retnumeric)
5152  PG_RETURN_NUMERIC(int64_to_numeric(intresult));
5153  else
5154  PG_RETURN_FLOAT8(intresult);
5155 }
5156 
5157 Datum
5158 timestamptz_part(PG_FUNCTION_ARGS)
5159 {
5160  return timestamptz_part_common(fcinfo, false);
5161 }
5162 
5163 Datum
5164 extract_timestamptz(PG_FUNCTION_ARGS)
5165 {
5166  return timestamptz_part_common(fcinfo, true);
5167 }
5168 
5169 
5170 /* interval_part() and extract_interval()
5171  * Extract specified field from interval.
5172  */
5173 static Datum
5174 interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
5175 {
5176  text *units = PG_GETARG_TEXT_PP(0);
5177  Interval *interval = PG_GETARG_INTERVAL_P(1);
5178  int64 intresult;
5179  int type,
5180  val;
5181  char *lowunits;
5182  fsec_t fsec;
5183  struct pg_tm tt,
5184  *tm = &tt;
5185 
5186  lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
5187  VARSIZE_ANY_EXHDR(units),
5188  false);
5189 
5190  type = DecodeUnits(0, lowunits, &val);
5191  if (type == UNKNOWN_FIELD)
5192  type = DecodeSpecial(0, lowunits, &val);
5193 
5194  if (type == UNITS)
5195  {
5196  if (interval2tm(*interval, tm, &fsec) == 0)
5197  {
5198  switch (val)
5199  {
5200  case DTK_MICROSEC:
5201  intresult = tm->tm_sec * INT64CONST(1000000) + fsec;
5202  break;
5203 
5204  case DTK_MILLISEC:
5205  if (retnumeric)
5206  /*---
5207  * tm->tm_sec * 1000 + fsec / 1000
5208  * = (tm->tm_sec * 1'000'000 + fsec) / 1000
5209  */
5210  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 3));
5211  else
5212  PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0);
5213  break;
5214 
5215  case DTK_SECOND:
5216  if (retnumeric)
5217  /*---
5218  * tm->tm_sec + fsec / 1'000'000
5219  * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000
5220  */
5221  PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 6));
5222  else
5223  PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0);
5224  break;
5225 
5226  case DTK_MINUTE:
5227  intresult = tm->tm_min;
5228  break;
5229 
5230  case DTK_HOUR:
5231  intresult = tm->tm_hour;
5232  break;
5233 
5234  case DTK_DAY:
5235  intresult = tm->tm_mday;
5236  break;
5237 
5238  case DTK_MONTH:
5239  intresult = tm->tm_mon;
5240  break;
5241 
5242  case DTK_QUARTER:
5243  intresult = (tm->tm_mon / 3) + 1;
5244  break;
5245 
5246  case DTK_YEAR:
5247  intresult = tm->tm_year;
5248  break;
5249 
5250  case DTK_DECADE:
5251  /* caution: C division may have negative remainder */
5252  intresult = tm->tm_year / 10;
5253  break;
5254 
5255  case DTK_CENTURY:
5256  /* caution: C division may have negative remainder */
5257  intresult = tm->tm_year / 100;
5258  break;
5259 
5260  case DTK_MILLENNIUM:
5261  /* caution: C division may have negative remainder */
5262  intresult = tm->tm_year / 1000;
5263  break;
5264 
5265  default:
5266  ereport(ERROR,
5267  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5268  errmsg("interval units \"%s\" not supported",
5269  lowunits)));
5270  intresult = 0;
5271  }
5272  }
5273  else
5274  {
5275  elog(ERROR, "could not convert interval to tm");
5276  intresult = 0;
5277  }
5278  }
5279  else if (type == RESERV && val == DTK_EPOCH)
5280  {
5281  if (retnumeric)
5282  {
5283  Numeric result;
5284  int64 secs_from_day_month;
5285  int64 val;
5286 
5287  /* this always fits into int64 */
5288  secs_from_day_month = ((int64) DAYS_PER_YEAR * (interval->month / MONTHS_PER_YEAR) +
5289  (int64) DAYS_PER_MONTH * (interval->month % MONTHS_PER_YEAR) +
5290  interval->day) * SECS_PER_DAY;
5291 
5292  /*---
5293  * result = secs_from_day_month + interval->time / 1'000'000
5294  * = (secs_from_day_month * 1'000'000 + interval->time) / 1'000'000
5295  */
5296 
5297  /*
5298  * Try the computation inside int64; if it overflows, do it in
5299  * numeric (slower). This overflow happens around 10^9 days, so
5300  * not common in practice.
5301  */
5302  if (!pg_mul_s64_overflow(secs_from_day_month, 1000000, &val) &&
5303  !pg_add_s64_overflow(val, interval->time, &val))
5304  result = int64_div_fast_to_numeric(val, 6);
5305  else
5306  result =
5307  numeric_add_opt_error(int64_div_fast_to_numeric(interval->time, 6),
5308  int64_to_numeric(secs_from_day_month),
5309  NULL);
5310 
5311  PG_RETURN_NUMERIC(result);
5312  }
5313  else
5314  {
5315  float8 result;
5316 
5317  result = interval->time / 1000000.0;
5318  result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);
5319  result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);
5320  result += ((double) SECS_PER_DAY) * interval->day;
5321 
5322  PG_RETURN_FLOAT8(result);
5323  }
5324  }
5325  else
5326  {
5327  ereport(ERROR,
5328  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5329  errmsg("interval units \"%s\" not recognized",
5330  lowunits)));
5331  intresult = 0;
5332  }
5333 
5334  if (retnumeric)
5335  PG_RETURN_NUMERIC(int64_to_numeric(intresult));
5336  else
5337  PG_RETURN_FLOAT8(intresult);
5338 }
5339 
5340 Datum
5341 interval_part(PG_FUNCTION_ARGS)
5342 {
5343  return interval_part_common(fcinfo, false);
5344 }
5345 
5346 Datum
5347 extract_interval(PG_FUNCTION_ARGS)
5348 {
5349  return interval_part_common(fcinfo, true);
5350 }
5351 
5352 
5353 /* timestamp_zone()
5354  * Encode timestamp type with specified time zone.
5355  * This function is just timestamp2timestamptz() except instead of
5356  * shifting to the global timezone, we shift to the specified timezone.
5357  * This is different from the other AT TIME ZONE cases because instead
5358  * of shifting _to_ a new time zone, it sets the time to _be_ the
5359  * specified timezone.
5360  */
5361 Datum
5362 timestamp_zone(PG_FUNCTION_ARGS)
5363 {
5364  text *zone = PG_GETARG_TEXT_PP(0);
5365  Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
5366  TimestampTz result;
5367  int tz;
5368  char tzname[TZ_STRLEN_MAX + 1];
5369  char *lowzone;
5370  int type,
5371  val;
5372  pg_tz *tzp;
5373  struct pg_tm tm;
5374  fsec_t fsec;
5375 
5376  if (TIMESTAMP_NOT_FINITE(timestamp))
5377  PG_RETURN_TIMESTAMPTZ(timestamp);
5378 
5379  /*
5380  * Look up the requested timezone. First we look in the timezone
5381  * abbreviation table (to handle cases like "EST"), and if that fails, we
5382  * look in the timezone database (to handle cases like
5383  * "America/New_York"). (This matches the order in which timestamp input
5384  * checks the cases; it's important because the timezone database unwisely
5385  * uses a few zone names that are identical to offset abbreviations.)
5386  */
5387  text_to_cstring_buffer(zone, tzname, sizeof(tzname));
5388 
5389  /* DecodeTimezoneAbbrev requires lowercase input */
5390  lowzone = downcase_truncate_identifier(tzname,
5391  strlen(tzname),
5392  false);
5393 
5394  type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
5395 
5396  if (type == TZ || type == DTZ)
5397  {
5398  /* fixed-offset abbreviation */
5399  tz = val;
5400  result = dt2local(timestamp, tz);
5401  }
5402  else if (type == DYNTZ)
5403  {
5404  /* dynamic-offset abbreviation, resolve using specified time */
5405  if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0)
5406  ereport(ERROR,
5407  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5408  errmsg("timestamp out of range")));
5409  tz = -DetermineTimeZoneAbbrevOffset(&tm, tzname, tzp);
5410  result = dt2local(timestamp, tz);
5411  }
5412  else
5413  {
5414  /* try it as a full zone name */
5415  tzp = pg_tzset(tzname);
5416  if (tzp)
5417  {
5418  /* Apply the timezone change */
5419  if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0)
5420  ereport(ERROR,
5421  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5422  errmsg("timestamp out of range")));
5423  tz = DetermineTimeZoneOffset(&tm, tzp);
5424  if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
5425  ereport(ERROR,
5426  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5427  errmsg("timestamp out of range")));
5428  }
5429  else
5430  {
5431  ereport(ERROR,
5432  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5433  errmsg("time zone \"%s\" not recognized", tzname)));
5434  result = 0; /* keep compiler quiet */
5435  }
5436  }
5437 
5438  if (!IS_VALID_TIMESTAMP(result))
5439  ereport(ERROR,
5440  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5441  errmsg("timestamp out of range")));
5442 
5443  PG_RETURN_TIMESTAMPTZ(result);
5444 }
5445 
5446 /* timestamp_izone()
5447  * Encode timestamp type with specified time interval as time zone.
5448  */
5449 Datum
5450 timestamp_izone(PG_FUNCTION_ARGS)
5451 {
5452  Interval *zone = PG_GETARG_INTERVAL_P(0);
5453  Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
5454  TimestampTz result;
5455  int tz;
5456 
5457  if (TIMESTAMP_NOT_FINITE(timestamp))
5458  PG_RETURN_TIMESTAMPTZ(timestamp);
5459 
5460  if (zone->month != 0 || zone->day != 0)
5461  ereport(ERROR,
5462  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5463  errmsg("interval time zone \"%s\" must not include months or days",
5464  DatumGetCString(DirectFunctionCall1(interval_out,
5465  PointerGetDatum(zone))))));
5466 
5467  tz = zone->time / USECS_PER_SEC;
5468 
5469  result = dt2local(timestamp, tz);
5470 
5471  if (!IS_VALID_TIMESTAMP(result))
5472  ereport(ERROR,
5473  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5474  errmsg("timestamp out of range")));
5475 
5476  PG_RETURN_TIMESTAMPTZ(result);
5477 } /* timestamp_izone() */
5478 
5479 /* TimestampTimestampTzRequiresRewrite()
5480  *
5481  * Returns false if the TimeZone GUC setting causes timestamp_timestamptz and
5482  * timestamptz_timestamp to be no-ops, where the return value has the same
5483  * bits as the argument. Since project convention is to assume a GUC changes
5484  * no more often than STABLE functions change, the answer is valid that long.
5485  */
5486 bool
5487 TimestampTimestampTzRequiresRewrite(void)
5488 {
5489  long offset;
5490 
5491  if (pg_get_timezone_offset(session_timezone, &offset) && offset == 0)
5492  return false;
5493  return true;
5494 }
5495 
5496 /* timestamp_timestamptz()
5497  * Convert local timestamp to timestamp at GMT
5498  */
5499 Datum
5500 timestamp_timestamptz(PG_FUNCTION_ARGS)
5501 {
5502  Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
5503 
5504  PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(timestamp));
5505 }
5506 
5507 /*
5508  * Convert timestamp to timestamp with time zone.
5509  *
5510  * On successful conversion, *overflow is set to zero if it's not NULL.
5511  *
5512  * If the timestamp is finite but out of the valid range for timestamptz, then:
5513  * if overflow is NULL, we throw an out-of-range error.
5514  * if overflow is not NULL, we store +1 or -1 there to indicate the sign
5515  * of the overflow, and return the appropriate timestamptz infinity.
5516  */
5517 TimestampTz
5518 timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow)
5519 {
5520  TimestampTz result;
5521  struct pg_tm tt,
5522  *tm = &tt;
5523  fsec_t fsec;
5524  int tz;
5525 
5526  if (overflow)
5527  *overflow = 0;
5528 
5529  if (TIMESTAMP_NOT_FINITE(timestamp))
5530  return timestamp;
5531 
5532  /* We don't expect this to fail, but check it pro forma */
5533  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
5534  {
5535  tz = DetermineTimeZoneOffset(tm, session_timezone);
5536 
5537  result = dt2local(timestamp, -tz);
5538 
5539  if (IS_VALID_TIMESTAMP(result))
5540  {
5541  return result;
5542  }
5543  else if (overflow)
5544  {
5545  if (result < MIN_TIMESTAMP)
5546  {
5547  *overflow = -1;
5548  TIMESTAMP_NOBEGIN(result);
5549  }
5550  else
5551  {
5552  *overflow = 1;
5553  TIMESTAMP_NOEND(result);
5554  }
5555  return result;
5556  }
5557  }
5558 
5559  ereport(ERROR,
5560  (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5561  errmsg("timestamp out of range")));
5562 
5563  return 0;
5564 }
5565 
5566 /*
5567  * Promote timestamp to timestamptz, throwing error for overflow.
5568  */
5569 static TimestampTz
5570 timestamp2timestamptz(Timestamp timestamp)
5571 {
5572  return timestamp2timestamptz_opt_overflow(timestamp, NULL);
5573 }
5574 
5575 /* timestamptz_timestamp()
5576  * Convert timestamp at GMT to local timestamp
5577  */
5578 Datum
5579 timestamptz_timestamp(PG_FUNCTION_ARGS)
5580 {
5581  TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
5582 
5583  PG_RETURN_TIMESTAMP(timestamptz2timestamp(timestamp));
5584 }
5585