1 /*-------------------------------------------------------------------------
4 * implements DATE and TIME data types specified in SQL standard
6 * Portions Copyright (c) 1996-2013, PostgreSQL Global Development Group
7 * Portions Copyright (c) 1994-5, Regents of the University of California
11 * src/backend/utils/adt/date.c
13 *-------------------------------------------------------------------------
23 #include "access/hash.h"
24 #include "libpq/pqformat.h"
25 #include "miscadmin.h"
26 #include "parser/scansup.h"
27 #include "utils/array.h"
28 #include "utils/builtins.h"
29 #include "utils/date.h"
30 #include "utils/datetime.h"
31 #include "utils/nabstime.h"
32 #include "utils/sortsupport.h"
35 * gcc's -ffast-math switch breaks routines that expect exact results from
36 * expressions like timeval / SECS_PER_HOUR, where timeval is double.
39 #error -ffast-math is known to break this code
43 static void EncodeSpecialDate(DateADT dt, char *str);
44 static int time2tm(TimeADT time, struct pg_tm * tm, fsec_t *fsec);
45 static int timetz2tm(TimeTzADT *time, struct pg_tm * tm, fsec_t *fsec, int *tzp);
46 static int tm2time(struct pg_tm * tm, fsec_t fsec, TimeADT *result);
47 static int tm2timetz(struct pg_tm * tm, fsec_t fsec, int tz, TimeTzADT *result);
48 static void AdjustTimeForTypmod(TimeADT *time, int32 typmod);
51 /* common code for timetypmodin and timetztypmodin */
53 anytime_typmodin(bool istz, ArrayType *ta)
59 tl = ArrayGetIntegerTypmods(ta, &n);
62 * we're not too tense about good error message here because grammar
63 * shouldn't allow wrong number of modifiers for TIME
67 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
68 errmsg("invalid type modifier")));
72 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
73 errmsg("TIME(%d)%s precision must not be negative",
74 *tl, (istz ? " WITH TIME ZONE" : ""))));
75 if (*tl > MAX_TIME_PRECISION)
78 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
79 errmsg("TIME(%d)%s precision reduced to maximum allowed, %d",
80 *tl, (istz ? " WITH TIME ZONE" : ""),
81 MAX_TIME_PRECISION)));
82 typmod = MAX_TIME_PRECISION;
90 /* common code for timetypmodout and timetztypmodout */
92 anytime_typmodout(bool istz, int32 typmod)
94 char *res = (char *) palloc(64);
95 const char *tz = istz ? " with time zone" : " without time zone";
98 snprintf(res, 64, "(%d)%s", (int) typmod, tz);
100 snprintf(res, 64, "%s", tz);
105 /*****************************************************************************
107 *****************************************************************************/
111 * Given date text string, convert to internal date format.
114 date_in(PG_FUNCTION_ARGS)
116 char *str = PG_GETARG_CSTRING(0);
125 char *field[MAXDATEFIELDS];
126 int ftype[MAXDATEFIELDS];
127 char workbuf[MAXDATELEN + 1];
129 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
130 field, ftype, MAXDATEFIELDS, &nf);
132 dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp);
134 DateTimeParseError(dterr, str, "date");
143 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
144 errmsg("date/time value \"current\" is no longer supported")));
146 GetCurrentDateTime(tm);
155 PG_RETURN_DATEADT(date);
159 PG_RETURN_DATEADT(date);
162 DateTimeParseError(DTERR_BAD_FORMAT, str, "date");
166 if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
168 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
169 errmsg("date out of range: \"%s\"", str)));
171 date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
173 PG_RETURN_DATEADT(date);
177 * Given internal format date, convert to text string.
180 date_out(PG_FUNCTION_ARGS)
182 DateADT date = PG_GETARG_DATEADT(0);
186 char buf[MAXDATELEN + 1];
188 if (DATE_NOT_FINITE(date))
189 EncodeSpecialDate(date, buf);
192 j2date(date + POSTGRES_EPOCH_JDATE,
193 &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
194 EncodeDateOnly(tm, DateStyle, buf);
197 result = pstrdup(buf);
198 PG_RETURN_CSTRING(result);
202 * date_recv - converts external binary format to date
205 date_recv(PG_FUNCTION_ARGS)
207 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
210 result = (DateADT) pq_getmsgint(buf, sizeof(DateADT));
212 /* Limit to the same range that date_in() accepts. */
213 if (DATE_NOT_FINITE(result))
215 else if (result < -POSTGRES_EPOCH_JDATE ||
216 result >= JULIAN_MAX - POSTGRES_EPOCH_JDATE)
218 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
219 errmsg("date out of range")));
221 PG_RETURN_DATEADT(result);
225 * date_send - converts date to binary format
228 date_send(PG_FUNCTION_ARGS)
230 DateADT date = PG_GETARG_DATEADT(0);
233 pq_begintypsend(&buf);
234 pq_sendint(&buf, date, sizeof(date));
235 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
239 * Convert reserved date values to string.
242 EncodeSpecialDate(DateADT dt, char *str)
244 if (DATE_IS_NOBEGIN(dt))
246 else if (DATE_IS_NOEND(dt))
248 else /* shouldn't happen */
249 elog(ERROR, "invalid argument for EncodeSpecialDate");
254 * Comparison functions for dates
258 date_eq(PG_FUNCTION_ARGS)
260 DateADT dateVal1 = PG_GETARG_DATEADT(0);
261 DateADT dateVal2 = PG_GETARG_DATEADT(1);
263 PG_RETURN_BOOL(dateVal1 == dateVal2);
267 date_ne(PG_FUNCTION_ARGS)
269 DateADT dateVal1 = PG_GETARG_DATEADT(0);
270 DateADT dateVal2 = PG_GETARG_DATEADT(1);
272 PG_RETURN_BOOL(dateVal1 != dateVal2);
276 date_lt(PG_FUNCTION_ARGS)
278 DateADT dateVal1 = PG_GETARG_DATEADT(0);
279 DateADT dateVal2 = PG_GETARG_DATEADT(1);
281 PG_RETURN_BOOL(dateVal1 < dateVal2);
285 date_le(PG_FUNCTION_ARGS)
287 DateADT dateVal1 = PG_GETARG_DATEADT(0);
288 DateADT dateVal2 = PG_GETARG_DATEADT(1);
290 PG_RETURN_BOOL(dateVal1 <= dateVal2);
294 date_gt(PG_FUNCTION_ARGS)
296 DateADT dateVal1 = PG_GETARG_DATEADT(0);
297 DateADT dateVal2 = PG_GETARG_DATEADT(1);
299 PG_RETURN_BOOL(dateVal1 > dateVal2);
303 date_ge(PG_FUNCTION_ARGS)
305 DateADT dateVal1 = PG_GETARG_DATEADT(0);
306 DateADT dateVal2 = PG_GETARG_DATEADT(1);
308 PG_RETURN_BOOL(dateVal1 >= dateVal2);
312 date_cmp(PG_FUNCTION_ARGS)
314 DateADT dateVal1 = PG_GETARG_DATEADT(0);
315 DateADT dateVal2 = PG_GETARG_DATEADT(1);
317 if (dateVal1 < dateVal2)
319 else if (dateVal1 > dateVal2)
325 date_fastcmp(Datum x, Datum y, SortSupport ssup)
327 DateADT a = DatumGetDateADT(x);
328 DateADT b = DatumGetDateADT(y);
338 date_sortsupport(PG_FUNCTION_ARGS)
340 SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0);
342 ssup->comparator = date_fastcmp;
347 date_finite(PG_FUNCTION_ARGS)
349 DateADT date = PG_GETARG_DATEADT(0);
351 PG_RETURN_BOOL(!DATE_NOT_FINITE(date));
355 date_larger(PG_FUNCTION_ARGS)
357 DateADT dateVal1 = PG_GETARG_DATEADT(0);
358 DateADT dateVal2 = PG_GETARG_DATEADT(1);
360 PG_RETURN_DATEADT((dateVal1 > dateVal2) ? dateVal1 : dateVal2);
364 date_smaller(PG_FUNCTION_ARGS)
366 DateADT dateVal1 = PG_GETARG_DATEADT(0);
367 DateADT dateVal2 = PG_GETARG_DATEADT(1);
369 PG_RETURN_DATEADT((dateVal1 < dateVal2) ? dateVal1 : dateVal2);
372 /* Compute difference between two dates in days.
375 date_mi(PG_FUNCTION_ARGS)
377 DateADT dateVal1 = PG_GETARG_DATEADT(0);
378 DateADT dateVal2 = PG_GETARG_DATEADT(1);
380 if (DATE_NOT_FINITE(dateVal1) || DATE_NOT_FINITE(dateVal2))
382 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
383 errmsg("cannot subtract infinite dates")));
385 PG_RETURN_INT32((int32) (dateVal1 - dateVal2));
388 /* Add a number of days to a date, giving a new date.
389 * Must handle both positive and negative numbers of days.
392 date_pli(PG_FUNCTION_ARGS)
394 DateADT dateVal = PG_GETARG_DATEADT(0);
395 int32 days = PG_GETARG_INT32(1);
397 if (DATE_NOT_FINITE(dateVal))
398 days = 0; /* can't change infinity */
400 PG_RETURN_DATEADT(dateVal + days);
403 /* Subtract a number of days from a date, giving a new date.
406 date_mii(PG_FUNCTION_ARGS)
408 DateADT dateVal = PG_GETARG_DATEADT(0);
409 int32 days = PG_GETARG_INT32(1);
411 if (DATE_NOT_FINITE(dateVal))
412 days = 0; /* can't change infinity */
414 PG_RETURN_DATEADT(dateVal - days);
418 * Internal routines for promoting date to timestamp and timestamp with
423 date2timestamp(DateADT dateVal)
427 if (DATE_IS_NOBEGIN(dateVal))
428 TIMESTAMP_NOBEGIN(result);
429 else if (DATE_IS_NOEND(dateVal))
430 TIMESTAMP_NOEND(result);
433 #ifdef HAVE_INT64_TIMESTAMP
434 /* date is days since 2000, timestamp is microseconds since same... */
435 result = dateVal * USECS_PER_DAY;
436 /* Date's range is wider than timestamp's, so check for overflow */
437 if (result / USECS_PER_DAY != dateVal)
439 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
440 errmsg("date out of range for timestamp")));
442 /* date is days since 2000, timestamp is seconds since same... */
443 result = dateVal * (double) SECS_PER_DAY;
451 date2timestamptz(DateADT dateVal)
458 if (DATE_IS_NOBEGIN(dateVal))
459 TIMESTAMP_NOBEGIN(result);
460 else if (DATE_IS_NOEND(dateVal))
461 TIMESTAMP_NOEND(result);
464 j2date(dateVal + POSTGRES_EPOCH_JDATE,
465 &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
469 tz = DetermineTimeZoneOffset(tm, session_timezone);
471 #ifdef HAVE_INT64_TIMESTAMP
472 result = dateVal * USECS_PER_DAY + tz * USECS_PER_SEC;
473 /* Date's range is wider than timestamp's, so check for overflow */
474 if ((result - tz * USECS_PER_SEC) / USECS_PER_DAY != dateVal)
476 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
477 errmsg("date out of range for timestamp")));
479 result = dateVal * (double) SECS_PER_DAY + tz;
487 * date2timestamp_no_overflow
489 * This is chartered to produce a double value that is numerically
490 * equivalent to the corresponding Timestamp value, if the date is in the
491 * valid range of Timestamps, but in any case not throw an overflow error.
492 * We can do this since the numerical range of double is greater than
493 * that of non-erroneous timestamps. The results are currently only
494 * used for statistical estimation purposes.
497 date2timestamp_no_overflow(DateADT dateVal)
501 if (DATE_IS_NOBEGIN(dateVal))
503 else if (DATE_IS_NOEND(dateVal))
507 #ifdef HAVE_INT64_TIMESTAMP
508 /* date is days since 2000, timestamp is microseconds since same... */
509 result = dateVal * (double) USECS_PER_DAY;
511 /* date is days since 2000, timestamp is seconds since same... */
512 result = dateVal * (double) SECS_PER_DAY;
521 * Crosstype comparison functions for dates
525 date_eq_timestamp(PG_FUNCTION_ARGS)
527 DateADT dateVal = PG_GETARG_DATEADT(0);
528 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
531 dt1 = date2timestamp(dateVal);
533 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
537 date_ne_timestamp(PG_FUNCTION_ARGS)
539 DateADT dateVal = PG_GETARG_DATEADT(0);
540 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
543 dt1 = date2timestamp(dateVal);
545 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
549 date_lt_timestamp(PG_FUNCTION_ARGS)
551 DateADT dateVal = PG_GETARG_DATEADT(0);
552 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
555 dt1 = date2timestamp(dateVal);
557 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
561 date_gt_timestamp(PG_FUNCTION_ARGS)
563 DateADT dateVal = PG_GETARG_DATEADT(0);
564 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
567 dt1 = date2timestamp(dateVal);
569 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
573 date_le_timestamp(PG_FUNCTION_ARGS)
575 DateADT dateVal = PG_GETARG_DATEADT(0);
576 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
579 dt1 = date2timestamp(dateVal);
581 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
585 date_ge_timestamp(PG_FUNCTION_ARGS)
587 DateADT dateVal = PG_GETARG_DATEADT(0);
588 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
591 dt1 = date2timestamp(dateVal);
593 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
597 date_cmp_timestamp(PG_FUNCTION_ARGS)
599 DateADT dateVal = PG_GETARG_DATEADT(0);
600 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
603 dt1 = date2timestamp(dateVal);
605 PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
609 date_eq_timestamptz(PG_FUNCTION_ARGS)
611 DateADT dateVal = PG_GETARG_DATEADT(0);
612 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
615 dt1 = date2timestamptz(dateVal);
617 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) == 0);
621 date_ne_timestamptz(PG_FUNCTION_ARGS)
623 DateADT dateVal = PG_GETARG_DATEADT(0);
624 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
627 dt1 = date2timestamptz(dateVal);
629 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) != 0);
633 date_lt_timestamptz(PG_FUNCTION_ARGS)
635 DateADT dateVal = PG_GETARG_DATEADT(0);
636 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
639 dt1 = date2timestamptz(dateVal);
641 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) < 0);
645 date_gt_timestamptz(PG_FUNCTION_ARGS)
647 DateADT dateVal = PG_GETARG_DATEADT(0);
648 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
651 dt1 = date2timestamptz(dateVal);
653 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) > 0);
657 date_le_timestamptz(PG_FUNCTION_ARGS)
659 DateADT dateVal = PG_GETARG_DATEADT(0);
660 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
663 dt1 = date2timestamptz(dateVal);
665 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) <= 0);
669 date_ge_timestamptz(PG_FUNCTION_ARGS)
671 DateADT dateVal = PG_GETARG_DATEADT(0);
672 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
675 dt1 = date2timestamptz(dateVal);
677 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) >= 0);
681 date_cmp_timestamptz(PG_FUNCTION_ARGS)
683 DateADT dateVal = PG_GETARG_DATEADT(0);
684 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
687 dt1 = date2timestamptz(dateVal);
689 PG_RETURN_INT32(timestamptz_cmp_internal(dt1, dt2));
693 timestamp_eq_date(PG_FUNCTION_ARGS)
695 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
696 DateADT dateVal = PG_GETARG_DATEADT(1);
699 dt2 = date2timestamp(dateVal);
701 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
705 timestamp_ne_date(PG_FUNCTION_ARGS)
707 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
708 DateADT dateVal = PG_GETARG_DATEADT(1);
711 dt2 = date2timestamp(dateVal);
713 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
717 timestamp_lt_date(PG_FUNCTION_ARGS)
719 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
720 DateADT dateVal = PG_GETARG_DATEADT(1);
723 dt2 = date2timestamp(dateVal);
725 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
729 timestamp_gt_date(PG_FUNCTION_ARGS)
731 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
732 DateADT dateVal = PG_GETARG_DATEADT(1);
735 dt2 = date2timestamp(dateVal);
737 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
741 timestamp_le_date(PG_FUNCTION_ARGS)
743 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
744 DateADT dateVal = PG_GETARG_DATEADT(1);
747 dt2 = date2timestamp(dateVal);
749 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
753 timestamp_ge_date(PG_FUNCTION_ARGS)
755 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
756 DateADT dateVal = PG_GETARG_DATEADT(1);
759 dt2 = date2timestamp(dateVal);
761 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
765 timestamp_cmp_date(PG_FUNCTION_ARGS)
767 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
768 DateADT dateVal = PG_GETARG_DATEADT(1);
771 dt2 = date2timestamp(dateVal);
773 PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
777 timestamptz_eq_date(PG_FUNCTION_ARGS)
779 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
780 DateADT dateVal = PG_GETARG_DATEADT(1);
783 dt2 = date2timestamptz(dateVal);
785 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) == 0);
789 timestamptz_ne_date(PG_FUNCTION_ARGS)
791 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
792 DateADT dateVal = PG_GETARG_DATEADT(1);
795 dt2 = date2timestamptz(dateVal);
797 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) != 0);
801 timestamptz_lt_date(PG_FUNCTION_ARGS)
803 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
804 DateADT dateVal = PG_GETARG_DATEADT(1);
807 dt2 = date2timestamptz(dateVal);
809 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) < 0);
813 timestamptz_gt_date(PG_FUNCTION_ARGS)
815 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
816 DateADT dateVal = PG_GETARG_DATEADT(1);
819 dt2 = date2timestamptz(dateVal);
821 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) > 0);
825 timestamptz_le_date(PG_FUNCTION_ARGS)
827 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
828 DateADT dateVal = PG_GETARG_DATEADT(1);
831 dt2 = date2timestamptz(dateVal);
833 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) <= 0);
837 timestamptz_ge_date(PG_FUNCTION_ARGS)
839 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
840 DateADT dateVal = PG_GETARG_DATEADT(1);
843 dt2 = date2timestamptz(dateVal);
845 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) >= 0);
849 timestamptz_cmp_date(PG_FUNCTION_ARGS)
851 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
852 DateADT dateVal = PG_GETARG_DATEADT(1);
855 dt2 = date2timestamptz(dateVal);
857 PG_RETURN_INT32(timestamptz_cmp_internal(dt1, dt2));
861 /* Add an interval to a date, giving a new date.
862 * Must handle both positive and negative intervals.
864 * We implement this by promoting the date to timestamp (without time zone)
865 * and then using the timestamp plus interval function.
868 date_pl_interval(PG_FUNCTION_ARGS)
870 DateADT dateVal = PG_GETARG_DATEADT(0);
871 Interval *span = PG_GETARG_INTERVAL_P(1);
874 dateStamp = date2timestamp(dateVal);
876 return DirectFunctionCall2(timestamp_pl_interval,
877 TimestampGetDatum(dateStamp),
878 PointerGetDatum(span));
881 /* Subtract an interval from a date, giving a new date.
882 * Must handle both positive and negative intervals.
884 * We implement this by promoting the date to timestamp (without time zone)
885 * and then using the timestamp minus interval function.
888 date_mi_interval(PG_FUNCTION_ARGS)
890 DateADT dateVal = PG_GETARG_DATEADT(0);
891 Interval *span = PG_GETARG_INTERVAL_P(1);
894 dateStamp = date2timestamp(dateVal);
896 return DirectFunctionCall2(timestamp_mi_interval,
897 TimestampGetDatum(dateStamp),
898 PointerGetDatum(span));
902 * Convert date to timestamp data type.
905 date_timestamp(PG_FUNCTION_ARGS)
907 DateADT dateVal = PG_GETARG_DATEADT(0);
910 result = date2timestamp(dateVal);
912 PG_RETURN_TIMESTAMP(result);
916 * Convert timestamp to date data type.
919 timestamp_date(PG_FUNCTION_ARGS)
921 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
927 if (TIMESTAMP_IS_NOBEGIN(timestamp))
928 DATE_NOBEGIN(result);
929 else if (TIMESTAMP_IS_NOEND(timestamp))
933 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
935 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
936 errmsg("timestamp out of range")));
938 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
941 PG_RETURN_DATEADT(result);
945 /* date_timestamptz()
946 * Convert date to timestamp with time zone data type.
949 date_timestamptz(PG_FUNCTION_ARGS)
951 DateADT dateVal = PG_GETARG_DATEADT(0);
954 result = date2timestamptz(dateVal);
956 PG_RETURN_TIMESTAMP(result);
960 /* timestamptz_date()
961 * Convert timestamp with time zone to date data type.
964 timestamptz_date(PG_FUNCTION_ARGS)
966 TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
973 if (TIMESTAMP_IS_NOBEGIN(timestamp))
974 DATE_NOBEGIN(result);
975 else if (TIMESTAMP_IS_NOEND(timestamp))
979 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
981 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
982 errmsg("timestamp out of range")));
984 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
987 PG_RETURN_DATEADT(result);
992 * Convert abstime to date data type.
995 abstime_date(PG_FUNCTION_ARGS)
997 AbsoluteTime abstime = PG_GETARG_ABSOLUTETIME(0);
1005 case INVALID_ABSTIME:
1007 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1008 errmsg("cannot convert reserved abstime value to date")));
1009 result = 0; /* keep compiler quiet */
1012 case NOSTART_ABSTIME:
1013 DATE_NOBEGIN(result);
1021 abstime2tm(abstime, &tz, tm, NULL);
1022 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
1026 PG_RETURN_DATEADT(result);
1030 /*****************************************************************************
1032 *****************************************************************************/
1035 time_in(PG_FUNCTION_ARGS)
1037 char *str = PG_GETARG_CSTRING(0);
1040 Oid typelem = PG_GETARG_OID(1);
1042 int32 typmod = PG_GETARG_INT32(2);
1050 char workbuf[MAXDATELEN + 1];
1051 char *field[MAXDATEFIELDS];
1053 int ftype[MAXDATEFIELDS];
1055 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
1056 field, ftype, MAXDATEFIELDS, &nf);
1058 dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz);
1060 DateTimeParseError(dterr, str, "time");
1062 tm2time(tm, fsec, &result);
1063 AdjustTimeForTypmod(&result, typmod);
1065 PG_RETURN_TIMEADT(result);
1069 * Convert a tm structure to a time data type.
1072 tm2time(struct pg_tm * tm, fsec_t fsec, TimeADT *result)
1074 #ifdef HAVE_INT64_TIMESTAMP
1075 *result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec)
1076 * USECS_PER_SEC) + fsec;
1078 *result = ((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec + fsec;
1084 * Convert time data type to POSIX time structure.
1086 * For dates within the range of pg_time_t, convert to the local time zone.
1087 * If out of this range, leave as UTC (in practice that could only happen
1088 * if pg_time_t is just 32 bits) - thomas 97/05/27
1091 time2tm(TimeADT time, struct pg_tm * tm, fsec_t *fsec)
1093 #ifdef HAVE_INT64_TIMESTAMP
1094 tm->tm_hour = time / USECS_PER_HOUR;
1095 time -= tm->tm_hour * USECS_PER_HOUR;
1096 tm->tm_min = time / USECS_PER_MINUTE;
1097 time -= tm->tm_min * USECS_PER_MINUTE;
1098 tm->tm_sec = time / USECS_PER_SEC;
1099 time -= tm->tm_sec * USECS_PER_SEC;
1106 TMODULO(trem, tm->tm_hour, (double) SECS_PER_HOUR);
1107 TMODULO(trem, tm->tm_min, (double) SECS_PER_MINUTE);
1108 TMODULO(trem, tm->tm_sec, 1.0);
1109 trem = TIMEROUND(trem);
1110 /* roundoff may need to propagate to higher-order fields */
1123 time_out(PG_FUNCTION_ARGS)
1125 TimeADT time = PG_GETARG_TIMEADT(0);
1130 char buf[MAXDATELEN + 1];
1132 time2tm(time, tm, &fsec);
1133 EncodeTimeOnly(tm, fsec, false, 0, DateStyle, buf);
1135 result = pstrdup(buf);
1136 PG_RETURN_CSTRING(result);
1140 * time_recv - converts external binary format to time
1142 * We make no attempt to provide compatibility between int and float
1143 * time representations ...
1146 time_recv(PG_FUNCTION_ARGS)
1148 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
1151 Oid typelem = PG_GETARG_OID(1);
1153 int32 typmod = PG_GETARG_INT32(2);
1156 #ifdef HAVE_INT64_TIMESTAMP
1157 result = pq_getmsgint64(buf);
1159 if (result < INT64CONST(0) || result > USECS_PER_DAY)
1161 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1162 errmsg("time out of range")));
1164 result = pq_getmsgfloat8(buf);
1166 if (result < 0 || result > (double) SECS_PER_DAY)
1168 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1169 errmsg("time out of range")));
1172 AdjustTimeForTypmod(&result, typmod);
1174 PG_RETURN_TIMEADT(result);
1178 * time_send - converts time to binary format
1181 time_send(PG_FUNCTION_ARGS)
1183 TimeADT time = PG_GETARG_TIMEADT(0);
1186 pq_begintypsend(&buf);
1187 #ifdef HAVE_INT64_TIMESTAMP
1188 pq_sendint64(&buf, time);
1190 pq_sendfloat8(&buf, time);
1192 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
1196 timetypmodin(PG_FUNCTION_ARGS)
1198 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
1200 PG_RETURN_INT32(anytime_typmodin(false, ta));
1204 timetypmodout(PG_FUNCTION_ARGS)
1206 int32 typmod = PG_GETARG_INT32(0);
1208 PG_RETURN_CSTRING(anytime_typmodout(false, typmod));
1213 * Flatten calls to time_scale() and timetz_scale() that solely represent
1214 * increases in allowed precision.
1217 time_transform(PG_FUNCTION_ARGS)
1219 PG_RETURN_POINTER(TemporalTransform(MAX_TIME_PRECISION,
1220 (Node *) PG_GETARG_POINTER(0)));
1224 * Adjust time type for specified scale factor.
1225 * Used by PostgreSQL type system to stuff columns.
1228 time_scale(PG_FUNCTION_ARGS)
1230 TimeADT time = PG_GETARG_TIMEADT(0);
1231 int32 typmod = PG_GETARG_INT32(1);
1235 AdjustTimeForTypmod(&result, typmod);
1237 PG_RETURN_TIMEADT(result);
1240 /* AdjustTimeForTypmod()
1241 * Force the precision of the time value to a specified value.
1242 * Uses *exactly* the same code as in AdjustTimestampForTypemod()
1243 * but we make a separate copy because those types do not
1244 * have a fundamental tie together but rather a coincidence of
1245 * implementation. - thomas
1248 AdjustTimeForTypmod(TimeADT *time, int32 typmod)
1250 #ifdef HAVE_INT64_TIMESTAMP
1251 static const int64 TimeScales[MAX_TIME_PRECISION + 1] = {
1252 INT64CONST(1000000),
1261 static const int64 TimeOffsets[MAX_TIME_PRECISION + 1] = {
1271 /* note MAX_TIME_PRECISION differs in this case */
1272 static const double TimeScales[MAX_TIME_PRECISION + 1] = {
1287 if (typmod >= 0 && typmod <= MAX_TIME_PRECISION)
1290 * Note: this round-to-nearest code is not completely consistent about
1291 * rounding values that are exactly halfway between integral values.
1292 * On most platforms, rint() will implement round-to-nearest-even, but
1293 * the integer code always rounds up (away from zero). Is it worth
1294 * trying to be consistent?
1296 #ifdef HAVE_INT64_TIMESTAMP
1297 if (*time >= INT64CONST(0))
1298 *time = ((*time + TimeOffsets[typmod]) / TimeScales[typmod]) *
1301 *time = -((((-*time) + TimeOffsets[typmod]) / TimeScales[typmod]) *
1302 TimeScales[typmod]);
1304 *time = rint((double) *time * TimeScales[typmod]) / TimeScales[typmod];
1311 time_eq(PG_FUNCTION_ARGS)
1313 TimeADT time1 = PG_GETARG_TIMEADT(0);
1314 TimeADT time2 = PG_GETARG_TIMEADT(1);
1316 PG_RETURN_BOOL(time1 == time2);
1320 time_ne(PG_FUNCTION_ARGS)
1322 TimeADT time1 = PG_GETARG_TIMEADT(0);
1323 TimeADT time2 = PG_GETARG_TIMEADT(1);
1325 PG_RETURN_BOOL(time1 != time2);
1329 time_lt(PG_FUNCTION_ARGS)
1331 TimeADT time1 = PG_GETARG_TIMEADT(0);
1332 TimeADT time2 = PG_GETARG_TIMEADT(1);
1334 PG_RETURN_BOOL(time1 < time2);
1338 time_le(PG_FUNCTION_ARGS)
1340 TimeADT time1 = PG_GETARG_TIMEADT(0);
1341 TimeADT time2 = PG_GETARG_TIMEADT(1);
1343 PG_RETURN_BOOL(time1 <= time2);
1347 time_gt(PG_FUNCTION_ARGS)
1349 TimeADT time1 = PG_GETARG_TIMEADT(0);
1350 TimeADT time2 = PG_GETARG_TIMEADT(1);
1352 PG_RETURN_BOOL(time1 > time2);
1356 time_ge(PG_FUNCTION_ARGS)
1358 TimeADT time1 = PG_GETARG_TIMEADT(0);
1359 TimeADT time2 = PG_GETARG_TIMEADT(1);
1361 PG_RETURN_BOOL(time1 >= time2);
1365 time_cmp(PG_FUNCTION_ARGS)
1367 TimeADT time1 = PG_GETARG_TIMEADT(0);
1368 TimeADT time2 = PG_GETARG_TIMEADT(1);
1371 PG_RETURN_INT32(-1);
1378 time_hash(PG_FUNCTION_ARGS)
1380 /* We can use either hashint8 or hashfloat8 directly */
1381 #ifdef HAVE_INT64_TIMESTAMP
1382 return hashint8(fcinfo);
1384 return hashfloat8(fcinfo);
1389 time_larger(PG_FUNCTION_ARGS)
1391 TimeADT time1 = PG_GETARG_TIMEADT(0);
1392 TimeADT time2 = PG_GETARG_TIMEADT(1);
1394 PG_RETURN_TIMEADT((time1 > time2) ? time1 : time2);
1398 time_smaller(PG_FUNCTION_ARGS)
1400 TimeADT time1 = PG_GETARG_TIMEADT(0);
1401 TimeADT time2 = PG_GETARG_TIMEADT(1);
1403 PG_RETURN_TIMEADT((time1 < time2) ? time1 : time2);
1406 /* overlaps_time() --- implements the SQL OVERLAPS operator.
1408 * Algorithm is per SQL spec. This is much harder than you'd think
1409 * because the spec requires us to deliver a non-null answer in some cases
1410 * where some of the inputs are null.
1413 overlaps_time(PG_FUNCTION_ARGS)
1416 * The arguments are TimeADT, but we leave them as generic Datums to avoid
1417 * dereferencing nulls (TimeADT is pass-by-reference!)
1419 Datum ts1 = PG_GETARG_DATUM(0);
1420 Datum te1 = PG_GETARG_DATUM(1);
1421 Datum ts2 = PG_GETARG_DATUM(2);
1422 Datum te2 = PG_GETARG_DATUM(3);
1423 bool ts1IsNull = PG_ARGISNULL(0);
1424 bool te1IsNull = PG_ARGISNULL(1);
1425 bool ts2IsNull = PG_ARGISNULL(2);
1426 bool te2IsNull = PG_ARGISNULL(3);
1428 #define TIMEADT_GT(t1,t2) \
1429 (DatumGetTimeADT(t1) > DatumGetTimeADT(t2))
1430 #define TIMEADT_LT(t1,t2) \
1431 (DatumGetTimeADT(t1) < DatumGetTimeADT(t2))
1434 * If both endpoints of interval 1 are null, the result is null (unknown).
1435 * If just one endpoint is null, take ts1 as the non-null one. Otherwise,
1436 * take ts1 as the lesser endpoint.
1442 /* swap null for non-null */
1446 else if (!te1IsNull)
1448 if (TIMEADT_GT(ts1, te1))
1457 /* Likewise for interval 2. */
1462 /* swap null for non-null */
1466 else if (!te2IsNull)
1468 if (TIMEADT_GT(ts2, te2))
1478 * At this point neither ts1 nor ts2 is null, so we can consider three
1479 * cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
1481 if (TIMEADT_GT(ts1, ts2))
1484 * This case is ts1 < te2 OR te1 < te2, which may look redundant but
1485 * in the presence of nulls it's not quite completely so.
1489 if (TIMEADT_LT(ts1, te2))
1490 PG_RETURN_BOOL(true);
1495 * If te1 is not null then we had ts1 <= te1 above, and we just found
1496 * ts1 >= te2, hence te1 >= te2.
1498 PG_RETURN_BOOL(false);
1500 else if (TIMEADT_LT(ts1, ts2))
1502 /* This case is ts2 < te1 OR te2 < te1 */
1505 if (TIMEADT_LT(ts2, te1))
1506 PG_RETURN_BOOL(true);
1511 * If te2 is not null then we had ts2 <= te2 above, and we just found
1512 * ts2 >= te1, hence te2 >= te1.
1514 PG_RETURN_BOOL(false);
1519 * For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
1520 * rather silly way of saying "true if both are nonnull, else null".
1522 if (te1IsNull || te2IsNull)
1524 PG_RETURN_BOOL(true);
1532 * Convert timestamp to time data type.
1535 timestamp_time(PG_FUNCTION_ARGS)
1537 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
1543 if (TIMESTAMP_NOT_FINITE(timestamp))
1546 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
1548 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1549 errmsg("timestamp out of range")));
1551 #ifdef HAVE_INT64_TIMESTAMP
1554 * Could also do this with time = (timestamp / USECS_PER_DAY *
1555 * USECS_PER_DAY) - timestamp;
1557 result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
1558 USECS_PER_SEC) + fsec;
1560 result = ((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec + fsec;
1563 PG_RETURN_TIMEADT(result);
1566 /* timestamptz_time()
1567 * Convert timestamptz to time data type.
1570 timestamptz_time(PG_FUNCTION_ARGS)
1572 TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
1579 if (TIMESTAMP_NOT_FINITE(timestamp))
1582 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
1584 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1585 errmsg("timestamp out of range")));
1587 #ifdef HAVE_INT64_TIMESTAMP
1590 * Could also do this with time = (timestamp / USECS_PER_DAY *
1591 * USECS_PER_DAY) - timestamp;
1593 result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
1594 USECS_PER_SEC) + fsec;
1596 result = ((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec + fsec;
1599 PG_RETURN_TIMEADT(result);
1602 /* datetime_timestamp()
1603 * Convert date and time to timestamp data type.
1606 datetime_timestamp(PG_FUNCTION_ARGS)
1608 DateADT date = PG_GETARG_DATEADT(0);
1609 TimeADT time = PG_GETARG_TIMEADT(1);
1612 result = date2timestamp(date);
1613 if (!TIMESTAMP_NOT_FINITE(result))
1616 PG_RETURN_TIMESTAMP(result);
1620 * Convert time to interval data type.
1623 time_interval(PG_FUNCTION_ARGS)
1625 TimeADT time = PG_GETARG_TIMEADT(0);
1628 result = (Interval *) palloc(sizeof(Interval));
1630 result->time = time;
1634 PG_RETURN_INTERVAL_P(result);
1638 * Convert interval to time data type.
1640 * This is defined as producing the fractional-day portion of the interval.
1641 * Therefore, we can just ignore the months field. It is not real clear
1642 * what to do with negative intervals, but we choose to subtract the floor,
1643 * so that, say, '-2 hours' becomes '22:00:00'.
1646 interval_time(PG_FUNCTION_ARGS)
1648 Interval *span = PG_GETARG_INTERVAL_P(0);
1651 #ifdef HAVE_INT64_TIMESTAMP
1654 result = span->time;
1655 if (result >= USECS_PER_DAY)
1657 days = result / USECS_PER_DAY;
1658 result -= days * USECS_PER_DAY;
1660 else if (result < 0)
1662 days = (-result + USECS_PER_DAY - 1) / USECS_PER_DAY;
1663 result += days * USECS_PER_DAY;
1666 result = span->time;
1667 if (result >= (double) SECS_PER_DAY || result < 0)
1668 result -= floor(result / (double) SECS_PER_DAY) * (double) SECS_PER_DAY;
1671 PG_RETURN_TIMEADT(result);
1675 * Subtract two times to produce an interval.
1678 time_mi_time(PG_FUNCTION_ARGS)
1680 TimeADT time1 = PG_GETARG_TIMEADT(0);
1681 TimeADT time2 = PG_GETARG_TIMEADT(1);
1684 result = (Interval *) palloc(sizeof(Interval));
1688 result->time = time1 - time2;
1690 PG_RETURN_INTERVAL_P(result);
1693 /* time_pl_interval()
1694 * Add interval to time.
1697 time_pl_interval(PG_FUNCTION_ARGS)
1699 TimeADT time = PG_GETARG_TIMEADT(0);
1700 Interval *span = PG_GETARG_INTERVAL_P(1);
1703 #ifdef HAVE_INT64_TIMESTAMP
1704 result = time + span->time;
1705 result -= result / USECS_PER_DAY * USECS_PER_DAY;
1706 if (result < INT64CONST(0))
1707 result += USECS_PER_DAY;
1711 result = time + span->time;
1712 TMODULO(result, time1, (double) SECS_PER_DAY);
1714 result += SECS_PER_DAY;
1717 PG_RETURN_TIMEADT(result);
1720 /* time_mi_interval()
1721 * Subtract interval from time.
1724 time_mi_interval(PG_FUNCTION_ARGS)
1726 TimeADT time = PG_GETARG_TIMEADT(0);
1727 Interval *span = PG_GETARG_INTERVAL_P(1);
1730 #ifdef HAVE_INT64_TIMESTAMP
1731 result = time - span->time;
1732 result -= result / USECS_PER_DAY * USECS_PER_DAY;
1733 if (result < INT64CONST(0))
1734 result += USECS_PER_DAY;
1738 result = time - span->time;
1739 TMODULO(result, time1, (double) SECS_PER_DAY);
1741 result += SECS_PER_DAY;
1744 PG_RETURN_TIMEADT(result);
1749 * Extract specified field from time type.
1752 time_part(PG_FUNCTION_ARGS)
1754 text *units = PG_GETARG_TEXT_PP(0);
1755 TimeADT time = PG_GETARG_TIMEADT(1);
1761 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
1762 VARSIZE_ANY_EXHDR(units),
1765 type = DecodeUnits(0, lowunits, &val);
1766 if (type == UNKNOWN_FIELD)
1767 type = DecodeSpecial(0, lowunits, &val);
1775 time2tm(time, tm, &fsec);
1780 #ifdef HAVE_INT64_TIMESTAMP
1781 result = tm->tm_sec * 1000000.0 + fsec;
1783 result = (tm->tm_sec + fsec) * 1000000;
1788 #ifdef HAVE_INT64_TIMESTAMP
1789 result = tm->tm_sec * 1000.0 + fsec / 1000.0;
1791 result = (tm->tm_sec + fsec) * 1000;
1796 #ifdef HAVE_INT64_TIMESTAMP
1797 result = tm->tm_sec + fsec / 1000000.0;
1799 result = tm->tm_sec + fsec;
1804 result = tm->tm_min;
1808 result = tm->tm_hour;
1820 case DTK_MILLENNIUM:
1824 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1825 errmsg("\"time\" units \"%s\" not recognized",
1830 else if (type == RESERV && val == DTK_EPOCH)
1832 #ifdef HAVE_INT64_TIMESTAMP
1833 result = time / 1000000.0;
1841 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1842 errmsg("\"time\" units \"%s\" not recognized",
1847 PG_RETURN_FLOAT8(result);
1851 /*****************************************************************************
1852 * Time With Time Zone ADT
1853 *****************************************************************************/
1856 * Convert a tm structure to a time data type.
1859 tm2timetz(struct pg_tm * tm, fsec_t fsec, int tz, TimeTzADT *result)
1861 #ifdef HAVE_INT64_TIMESTAMP
1862 result->time = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
1863 USECS_PER_SEC) + fsec;
1865 result->time = ((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec + fsec;
1873 timetz_in(PG_FUNCTION_ARGS)
1875 char *str = PG_GETARG_CSTRING(0);
1878 Oid typelem = PG_GETARG_OID(1);
1880 int32 typmod = PG_GETARG_INT32(2);
1888 char workbuf[MAXDATELEN + 1];
1889 char *field[MAXDATEFIELDS];
1891 int ftype[MAXDATEFIELDS];
1893 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
1894 field, ftype, MAXDATEFIELDS, &nf);
1896 dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz);
1898 DateTimeParseError(dterr, str, "time with time zone");
1900 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
1901 tm2timetz(tm, fsec, tz, result);
1902 AdjustTimeForTypmod(&(result->time), typmod);
1904 PG_RETURN_TIMETZADT_P(result);
1908 timetz_out(PG_FUNCTION_ARGS)
1910 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
1916 char buf[MAXDATELEN + 1];
1918 timetz2tm(time, tm, &fsec, &tz);
1919 EncodeTimeOnly(tm, fsec, true, tz, DateStyle, buf);
1921 result = pstrdup(buf);
1922 PG_RETURN_CSTRING(result);
1926 * timetz_recv - converts external binary format to timetz
1929 timetz_recv(PG_FUNCTION_ARGS)
1931 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
1934 Oid typelem = PG_GETARG_OID(1);
1936 int32 typmod = PG_GETARG_INT32(2);
1939 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
1941 #ifdef HAVE_INT64_TIMESTAMP
1942 result->time = pq_getmsgint64(buf);
1944 if (result->time < INT64CONST(0) || result->time > USECS_PER_DAY)
1946 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1947 errmsg("time out of range")));
1949 result->time = pq_getmsgfloat8(buf);
1951 if (result->time < 0 || result->time > (double) SECS_PER_DAY)
1953 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1954 errmsg("time out of range")));
1957 result->zone = pq_getmsgint(buf, sizeof(result->zone));
1959 /* Check for sane GMT displacement; see notes in datatype/timestamp.h */
1960 if (result->zone <= -TZDISP_LIMIT || result->zone >= TZDISP_LIMIT)
1962 (errcode(ERRCODE_INVALID_TIME_ZONE_DISPLACEMENT_VALUE),
1963 errmsg("time zone displacement out of range")));
1965 AdjustTimeForTypmod(&(result->time), typmod);
1967 PG_RETURN_TIMETZADT_P(result);
1971 * timetz_send - converts timetz to binary format
1974 timetz_send(PG_FUNCTION_ARGS)
1976 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
1979 pq_begintypsend(&buf);
1980 #ifdef HAVE_INT64_TIMESTAMP
1981 pq_sendint64(&buf, time->time);
1983 pq_sendfloat8(&buf, time->time);
1985 pq_sendint(&buf, time->zone, sizeof(time->zone));
1986 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
1990 timetztypmodin(PG_FUNCTION_ARGS)
1992 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
1994 PG_RETURN_INT32(anytime_typmodin(true, ta));
1998 timetztypmodout(PG_FUNCTION_ARGS)
2000 int32 typmod = PG_GETARG_INT32(0);
2002 PG_RETURN_CSTRING(anytime_typmodout(true, typmod));
2007 * Convert TIME WITH TIME ZONE data type to POSIX time structure.
2010 timetz2tm(TimeTzADT *time, struct pg_tm * tm, fsec_t *fsec, int *tzp)
2012 TimeOffset trem = time->time;
2014 #ifdef HAVE_INT64_TIMESTAMP
2015 tm->tm_hour = trem / USECS_PER_HOUR;
2016 trem -= tm->tm_hour * USECS_PER_HOUR;
2017 tm->tm_min = trem / USECS_PER_MINUTE;
2018 trem -= tm->tm_min * USECS_PER_MINUTE;
2019 tm->tm_sec = trem / USECS_PER_SEC;
2020 *fsec = trem - tm->tm_sec * USECS_PER_SEC;
2023 TMODULO(trem, tm->tm_hour, (double) SECS_PER_HOUR);
2024 TMODULO(trem, tm->tm_min, (double) SECS_PER_MINUTE);
2025 TMODULO(trem, tm->tm_sec, 1.0);
2026 trem = TIMEROUND(trem);
2027 /* roundoff may need to propagate to higher-order fields */
2030 trem = ceil(time->time);
2043 * Adjust time type for specified scale factor.
2044 * Used by PostgreSQL type system to stuff columns.
2047 timetz_scale(PG_FUNCTION_ARGS)
2049 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
2050 int32 typmod = PG_GETARG_INT32(1);
2053 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2055 result->time = time->time;
2056 result->zone = time->zone;
2058 AdjustTimeForTypmod(&(result->time), typmod);
2060 PG_RETURN_TIMETZADT_P(result);
2065 timetz_cmp_internal(TimeTzADT *time1, TimeTzADT *time2)
2070 /* Primary sort is by true (GMT-equivalent) time */
2071 #ifdef HAVE_INT64_TIMESTAMP
2072 t1 = time1->time + (time1->zone * USECS_PER_SEC);
2073 t2 = time2->time + (time2->zone * USECS_PER_SEC);
2075 t1 = time1->time + time1->zone;
2076 t2 = time2->time + time2->zone;
2085 * If same GMT time, sort by timezone; we only want to say that two
2086 * timetz's are equal if both the time and zone parts are equal.
2088 if (time1->zone > time2->zone)
2090 if (time1->zone < time2->zone)
2097 timetz_eq(PG_FUNCTION_ARGS)
2099 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2100 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2102 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) == 0);
2106 timetz_ne(PG_FUNCTION_ARGS)
2108 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2109 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2111 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) != 0);
2115 timetz_lt(PG_FUNCTION_ARGS)
2117 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2118 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2120 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) < 0);
2124 timetz_le(PG_FUNCTION_ARGS)
2126 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2127 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2129 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) <= 0);
2133 timetz_gt(PG_FUNCTION_ARGS)
2135 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2136 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2138 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) > 0);
2142 timetz_ge(PG_FUNCTION_ARGS)
2144 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2145 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2147 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) >= 0);
2151 timetz_cmp(PG_FUNCTION_ARGS)
2153 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2154 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2156 PG_RETURN_INT32(timetz_cmp_internal(time1, time2));
2160 timetz_hash(PG_FUNCTION_ARGS)
2162 TimeTzADT *key = PG_GETARG_TIMETZADT_P(0);
2166 * To avoid any problems with padding bytes in the struct, we figure the
2167 * field hashes separately and XOR them. This also provides a convenient
2168 * framework for dealing with the fact that the time field might be either
2171 #ifdef HAVE_INT64_TIMESTAMP
2172 thash = DatumGetUInt32(DirectFunctionCall1(hashint8,
2173 Int64GetDatumFast(key->time)));
2175 thash = DatumGetUInt32(DirectFunctionCall1(hashfloat8,
2176 Float8GetDatumFast(key->time)));
2178 thash ^= DatumGetUInt32(hash_uint32(key->zone));
2179 PG_RETURN_UINT32(thash);
2183 timetz_larger(PG_FUNCTION_ARGS)
2185 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2186 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2189 if (timetz_cmp_internal(time1, time2) > 0)
2193 PG_RETURN_TIMETZADT_P(result);
2197 timetz_smaller(PG_FUNCTION_ARGS)
2199 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2200 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2203 if (timetz_cmp_internal(time1, time2) < 0)
2207 PG_RETURN_TIMETZADT_P(result);
2210 /* timetz_pl_interval()
2211 * Add interval to timetz.
2214 timetz_pl_interval(PG_FUNCTION_ARGS)
2216 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
2217 Interval *span = PG_GETARG_INTERVAL_P(1);
2220 #ifndef HAVE_INT64_TIMESTAMP
2224 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2226 #ifdef HAVE_INT64_TIMESTAMP
2227 result->time = time->time + span->time;
2228 result->time -= result->time / USECS_PER_DAY * USECS_PER_DAY;
2229 if (result->time < INT64CONST(0))
2230 result->time += USECS_PER_DAY;
2232 result->time = time->time + span->time;
2233 TMODULO(result->time, time1.time, (double) SECS_PER_DAY);
2234 if (result->time < 0)
2235 result->time += SECS_PER_DAY;
2238 result->zone = time->zone;
2240 PG_RETURN_TIMETZADT_P(result);
2243 /* timetz_mi_interval()
2244 * Subtract interval from timetz.
2247 timetz_mi_interval(PG_FUNCTION_ARGS)
2249 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
2250 Interval *span = PG_GETARG_INTERVAL_P(1);
2253 #ifndef HAVE_INT64_TIMESTAMP
2257 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2259 #ifdef HAVE_INT64_TIMESTAMP
2260 result->time = time->time - span->time;
2261 result->time -= result->time / USECS_PER_DAY * USECS_PER_DAY;
2262 if (result->time < INT64CONST(0))
2263 result->time += USECS_PER_DAY;
2265 result->time = time->time - span->time;
2266 TMODULO(result->time, time1.time, (double) SECS_PER_DAY);
2267 if (result->time < 0)
2268 result->time += SECS_PER_DAY;
2271 result->zone = time->zone;
2273 PG_RETURN_TIMETZADT_P(result);
2276 /* overlaps_timetz() --- implements the SQL OVERLAPS operator.
2278 * Algorithm is per SQL spec. This is much harder than you'd think
2279 * because the spec requires us to deliver a non-null answer in some cases
2280 * where some of the inputs are null.
2283 overlaps_timetz(PG_FUNCTION_ARGS)
2286 * The arguments are TimeTzADT *, but we leave them as generic Datums for
2287 * convenience of notation --- and to avoid dereferencing nulls.
2289 Datum ts1 = PG_GETARG_DATUM(0);
2290 Datum te1 = PG_GETARG_DATUM(1);
2291 Datum ts2 = PG_GETARG_DATUM(2);
2292 Datum te2 = PG_GETARG_DATUM(3);
2293 bool ts1IsNull = PG_ARGISNULL(0);
2294 bool te1IsNull = PG_ARGISNULL(1);
2295 bool ts2IsNull = PG_ARGISNULL(2);
2296 bool te2IsNull = PG_ARGISNULL(3);
2298 #define TIMETZ_GT(t1,t2) \
2299 DatumGetBool(DirectFunctionCall2(timetz_gt,t1,t2))
2300 #define TIMETZ_LT(t1,t2) \
2301 DatumGetBool(DirectFunctionCall2(timetz_lt,t1,t2))
2304 * If both endpoints of interval 1 are null, the result is null (unknown).
2305 * If just one endpoint is null, take ts1 as the non-null one. Otherwise,
2306 * take ts1 as the lesser endpoint.
2312 /* swap null for non-null */
2316 else if (!te1IsNull)
2318 if (TIMETZ_GT(ts1, te1))
2327 /* Likewise for interval 2. */
2332 /* swap null for non-null */
2336 else if (!te2IsNull)
2338 if (TIMETZ_GT(ts2, te2))
2348 * At this point neither ts1 nor ts2 is null, so we can consider three
2349 * cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
2351 if (TIMETZ_GT(ts1, ts2))
2354 * This case is ts1 < te2 OR te1 < te2, which may look redundant but
2355 * in the presence of nulls it's not quite completely so.
2359 if (TIMETZ_LT(ts1, te2))
2360 PG_RETURN_BOOL(true);
2365 * If te1 is not null then we had ts1 <= te1 above, and we just found
2366 * ts1 >= te2, hence te1 >= te2.
2368 PG_RETURN_BOOL(false);
2370 else if (TIMETZ_LT(ts1, ts2))
2372 /* This case is ts2 < te1 OR te2 < te1 */
2375 if (TIMETZ_LT(ts2, te1))
2376 PG_RETURN_BOOL(true);
2381 * If te2 is not null then we had ts2 <= te2 above, and we just found
2382 * ts2 >= te1, hence te2 >= te1.
2384 PG_RETURN_BOOL(false);
2389 * For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
2390 * rather silly way of saying "true if both are nonnull, else null".
2392 if (te1IsNull || te2IsNull)
2394 PG_RETURN_BOOL(true);
2403 timetz_time(PG_FUNCTION_ARGS)
2405 TimeTzADT *timetz = PG_GETARG_TIMETZADT_P(0);
2408 /* swallow the time zone and just return the time */
2409 result = timetz->time;
2411 PG_RETURN_TIMEADT(result);
2416 time_timetz(PG_FUNCTION_ARGS)
2418 TimeADT time = PG_GETARG_TIMEADT(0);
2425 GetCurrentDateTime(tm);
2426 time2tm(time, tm, &fsec);
2427 tz = DetermineTimeZoneOffset(tm, session_timezone);
2429 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2431 result->time = time;
2434 PG_RETURN_TIMETZADT_P(result);
2438 /* timestamptz_timetz()
2439 * Convert timestamp to timetz data type.
2442 timestamptz_timetz(PG_FUNCTION_ARGS)
2444 TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
2451 if (TIMESTAMP_NOT_FINITE(timestamp))
2454 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
2456 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2457 errmsg("timestamp out of range")));
2459 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2461 tm2timetz(tm, fsec, tz, result);
2463 PG_RETURN_TIMETZADT_P(result);
2467 /* datetimetz_timestamptz()
2468 * Convert date and timetz to timestamp with time zone data type.
2469 * Timestamp is stored in GMT, so add the time zone
2470 * stored with the timetz to the result.
2471 * - thomas 2000-03-10
2474 datetimetz_timestamptz(PG_FUNCTION_ARGS)
2476 DateADT date = PG_GETARG_DATEADT(0);
2477 TimeTzADT *time = PG_GETARG_TIMETZADT_P(1);
2480 if (DATE_IS_NOBEGIN(date))
2481 TIMESTAMP_NOBEGIN(result);
2482 else if (DATE_IS_NOEND(date))
2483 TIMESTAMP_NOEND(result);
2486 #ifdef HAVE_INT64_TIMESTAMP
2487 result = date * USECS_PER_DAY + time->time + time->zone * USECS_PER_SEC;
2489 result = date * (double) SECS_PER_DAY + time->time + time->zone;
2493 PG_RETURN_TIMESTAMP(result);
2498 * Extract specified field from time type.
2501 timetz_part(PG_FUNCTION_ARGS)
2503 text *units = PG_GETARG_TEXT_PP(0);
2504 TimeTzADT *time = PG_GETARG_TIMETZADT_P(1);
2510 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
2511 VARSIZE_ANY_EXHDR(units),
2514 type = DecodeUnits(0, lowunits, &val);
2515 if (type == UNKNOWN_FIELD)
2516 type = DecodeSpecial(0, lowunits, &val);
2526 timetz2tm(time, tm, &fsec, &tz);
2536 result /= SECS_PER_MINUTE;
2537 FMODULO(result, dummy, (double) SECS_PER_MINUTE);
2542 FMODULO(dummy, result, (double) SECS_PER_HOUR);
2546 #ifdef HAVE_INT64_TIMESTAMP
2547 result = tm->tm_sec * 1000000.0 + fsec;
2549 result = (tm->tm_sec + fsec) * 1000000;
2554 #ifdef HAVE_INT64_TIMESTAMP
2555 result = tm->tm_sec * 1000.0 + fsec / 1000.0;
2557 result = (tm->tm_sec + fsec) * 1000;
2562 #ifdef HAVE_INT64_TIMESTAMP
2563 result = tm->tm_sec + fsec / 1000000.0;
2565 result = tm->tm_sec + fsec;
2570 result = tm->tm_min;
2574 result = tm->tm_hour;
2583 case DTK_MILLENNIUM:
2586 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2587 errmsg("\"time with time zone\" units \"%s\" not recognized",
2592 else if (type == RESERV && val == DTK_EPOCH)
2594 #ifdef HAVE_INT64_TIMESTAMP
2595 result = time->time / 1000000.0 + time->zone;
2597 result = time->time + time->zone;
2603 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2604 errmsg("\"time with time zone\" units \"%s\" not recognized",
2609 PG_RETURN_FLOAT8(result);
2613 * Encode time with time zone type with specified time zone.
2614 * Applies DST rules as of the current date.
2617 timetz_zone(PG_FUNCTION_ARGS)
2619 text *zone = PG_GETARG_TEXT_PP(0);
2620 TimeTzADT *t = PG_GETARG_TIMETZADT_P(1);
2623 char tzname[TZ_STRLEN_MAX + 1];
2630 * Look up the requested timezone. First we look in the date token table
2631 * (to handle cases like "EST"), and if that fails, we look in the
2632 * timezone database (to handle cases like "America/New_York"). (This
2633 * matches the order in which timestamp input checks the cases; it's
2634 * important because the timezone database unwisely uses a few zone names
2635 * that are identical to offset abbreviations.)
2637 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
2638 lowzone = downcase_truncate_identifier(tzname,
2642 type = DecodeSpecial(0, lowzone, &val);
2644 if (type == TZ || type == DTZ)
2645 tz = val * MINS_PER_HOUR;
2648 tzp = pg_tzset(tzname);
2651 /* Get the offset-from-GMT that is valid today for the zone */
2652 pg_time_t now = (pg_time_t) time(NULL);
2655 tm = pg_localtime(&now, tzp);
2656 tz = -tm->tm_gmtoff;
2661 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2662 errmsg("time zone \"%s\" not recognized", tzname)));
2663 tz = 0; /* keep compiler quiet */
2667 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2669 #ifdef HAVE_INT64_TIMESTAMP
2670 result->time = t->time + (t->zone - tz) * USECS_PER_SEC;
2671 while (result->time < INT64CONST(0))
2672 result->time += USECS_PER_DAY;
2673 while (result->time >= USECS_PER_DAY)
2674 result->time -= USECS_PER_DAY;
2676 result->time = t->time + (t->zone - tz);
2677 while (result->time < 0)
2678 result->time += SECS_PER_DAY;
2679 while (result->time >= SECS_PER_DAY)
2680 result->time -= SECS_PER_DAY;
2685 PG_RETURN_TIMETZADT_P(result);
2689 * Encode time with time zone type with specified time interval as time zone.
2692 timetz_izone(PG_FUNCTION_ARGS)
2694 Interval *zone = PG_GETARG_INTERVAL_P(0);
2695 TimeTzADT *time = PG_GETARG_TIMETZADT_P(1);
2699 if (zone->month != 0 || zone->day != 0)
2701 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2702 errmsg("interval time zone \"%s\" must not include months or days",
2703 DatumGetCString(DirectFunctionCall1(interval_out,
2704 PointerGetDatum(zone))))));
2706 #ifdef HAVE_INT64_TIMESTAMP
2707 tz = -(zone->time / USECS_PER_SEC);
2712 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2714 #ifdef HAVE_INT64_TIMESTAMP
2715 result->time = time->time + (time->zone - tz) * USECS_PER_SEC;
2716 while (result->time < INT64CONST(0))
2717 result->time += USECS_PER_DAY;
2718 while (result->time >= USECS_PER_DAY)
2719 result->time -= USECS_PER_DAY;
2721 result->time = time->time + (time->zone - tz);
2722 while (result->time < 0)
2723 result->time += SECS_PER_DAY;
2724 while (result->time >= SECS_PER_DAY)
2725 result->time -= SECS_PER_DAY;
2730 PG_RETURN_TIMETZADT_P(result);