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