1 /*-------------------------------------------------------------------------
4 * implements DATE and TIME data types specified in SQL standard
6 * Portions Copyright (c) 1996-2017, 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 "access/xact.h"
25 #include "libpq/pqformat.h"
26 #include "miscadmin.h"
27 #include "parser/scansup.h"
28 #include "utils/array.h"
29 #include "utils/builtins.h"
30 #include "utils/date.h"
31 #include "utils/datetime.h"
32 #include "utils/nabstime.h"
33 #include "utils/sortsupport.h"
36 * gcc's -ffast-math switch breaks routines that expect exact results from
37 * expressions like timeval / SECS_PER_HOUR, where timeval is double.
40 #error -ffast-math is known to break this code
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)
58 tl = ArrayGetIntegerTypmods(ta, &n);
61 * we're not too tense about good error message here because grammar
62 * shouldn't allow wrong number of modifiers for TIME
66 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
67 errmsg("invalid type modifier")));
69 return anytime_typmod_check(istz, tl[0]);
72 /* exported so parse_expr.c can use it */
74 anytime_typmod_check(bool istz, int32 typmod)
78 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
79 errmsg("TIME(%d)%s precision must not be negative",
80 typmod, (istz ? " WITH TIME ZONE" : ""))));
81 if (typmod > MAX_TIME_PRECISION)
84 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
85 errmsg("TIME(%d)%s precision reduced to maximum allowed, %d",
86 typmod, (istz ? " WITH TIME ZONE" : ""),
87 MAX_TIME_PRECISION)));
88 typmod = MAX_TIME_PRECISION;
94 /* common code for timetypmodout and timetztypmodout */
96 anytime_typmodout(bool istz, int32 typmod)
98 const char *tz = istz ? " with time zone" : " without time zone";
101 return psprintf("(%d)%s", (int) typmod, tz);
103 return psprintf("%s", tz);
107 /*****************************************************************************
109 *****************************************************************************/
113 * Given date text string, convert to internal date format.
116 date_in(PG_FUNCTION_ARGS)
118 char *str = PG_GETARG_CSTRING(0);
127 char *field[MAXDATEFIELDS];
128 int ftype[MAXDATEFIELDS];
129 char workbuf[MAXDATELEN + 1];
131 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
132 field, ftype, MAXDATEFIELDS, &nf);
134 dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp);
136 DateTimeParseError(dterr, str, "date");
145 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
146 errmsg("date/time value \"current\" is no longer supported")));
148 GetCurrentDateTime(tm);
157 PG_RETURN_DATEADT(date);
161 PG_RETURN_DATEADT(date);
164 DateTimeParseError(DTERR_BAD_FORMAT, str, "date");
168 /* Prevent overflow in Julian-day routines */
169 if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
171 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
172 errmsg("date out of range: \"%s\"", str)));
174 date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
176 /* Now check for just-out-of-range dates */
177 if (!IS_VALID_DATE(date))
179 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
180 errmsg("date out of range: \"%s\"", str)));
182 PG_RETURN_DATEADT(date);
186 * Given internal format date, convert to text string.
189 date_out(PG_FUNCTION_ARGS)
191 DateADT date = PG_GETARG_DATEADT(0);
195 char buf[MAXDATELEN + 1];
197 if (DATE_NOT_FINITE(date))
198 EncodeSpecialDate(date, buf);
201 j2date(date + POSTGRES_EPOCH_JDATE,
202 &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
203 EncodeDateOnly(tm, DateStyle, buf);
206 result = pstrdup(buf);
207 PG_RETURN_CSTRING(result);
211 * date_recv - converts external binary format to date
214 date_recv(PG_FUNCTION_ARGS)
216 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
219 result = (DateADT) pq_getmsgint(buf, sizeof(DateADT));
221 /* Limit to the same range that date_in() accepts. */
222 if (DATE_NOT_FINITE(result))
224 else if (!IS_VALID_DATE(result))
226 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
227 errmsg("date out of range")));
229 PG_RETURN_DATEADT(result);
233 * date_send - converts date to binary format
236 date_send(PG_FUNCTION_ARGS)
238 DateADT date = PG_GETARG_DATEADT(0);
241 pq_begintypsend(&buf);
242 pq_sendint(&buf, date, sizeof(date));
243 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
247 * make_date - date constructor
250 make_date(PG_FUNCTION_ARGS)
257 tm.tm_year = PG_GETARG_INT32(0);
258 tm.tm_mon = PG_GETARG_INT32(1);
259 tm.tm_mday = PG_GETARG_INT32(2);
261 /* Handle negative years as BC */
265 tm.tm_year = -tm.tm_year;
268 dterr = ValidateDate(DTK_DATE_M, false, false, bc, &tm);
272 (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
273 errmsg("date field value out of range: %d-%02d-%02d",
274 tm.tm_year, tm.tm_mon, tm.tm_mday)));
276 /* Prevent overflow in Julian-day routines */
277 if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
279 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
280 errmsg("date out of range: %d-%02d-%02d",
281 tm.tm_year, tm.tm_mon, tm.tm_mday)));
283 date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
285 /* Now check for just-out-of-range dates */
286 if (!IS_VALID_DATE(date))
288 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
289 errmsg("date out of range: %d-%02d-%02d",
290 tm.tm_year, tm.tm_mon, tm.tm_mday)));
292 PG_RETURN_DATEADT(date);
296 * Convert reserved date values to string.
299 EncodeSpecialDate(DateADT dt, char *str)
301 if (DATE_IS_NOBEGIN(dt))
303 else if (DATE_IS_NOEND(dt))
305 else /* shouldn't happen */
306 elog(ERROR, "invalid argument for EncodeSpecialDate");
311 * GetSQLCurrentDate -- implements CURRENT_DATE
314 GetSQLCurrentDate(void)
322 ts = GetCurrentTransactionStartTimestamp();
324 if (timestamp2tm(ts, &tz, tm, &fsec, NULL, NULL) != 0)
326 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
327 errmsg("timestamp out of range")));
329 return date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
333 * GetSQLCurrentTime -- implements CURRENT_TIME, CURRENT_TIME(n)
336 GetSQLCurrentTime(int32 typmod)
345 ts = GetCurrentTransactionStartTimestamp();
347 if (timestamp2tm(ts, &tz, tm, &fsec, NULL, NULL) != 0)
349 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
350 errmsg("timestamp out of range")));
352 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
353 tm2timetz(tm, fsec, tz, result);
354 AdjustTimeForTypmod(&(result->time), typmod);
359 * GetSQLLocalTime -- implements LOCALTIME, LOCALTIME(n)
362 GetSQLLocalTime(int32 typmod)
371 ts = GetCurrentTransactionStartTimestamp();
373 if (timestamp2tm(ts, &tz, tm, &fsec, NULL, NULL) != 0)
375 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
376 errmsg("timestamp out of range")));
378 tm2time(tm, fsec, &result);
379 AdjustTimeForTypmod(&result, typmod);
385 * Comparison functions for dates
389 date_eq(PG_FUNCTION_ARGS)
391 DateADT dateVal1 = PG_GETARG_DATEADT(0);
392 DateADT dateVal2 = PG_GETARG_DATEADT(1);
394 PG_RETURN_BOOL(dateVal1 == dateVal2);
398 date_ne(PG_FUNCTION_ARGS)
400 DateADT dateVal1 = PG_GETARG_DATEADT(0);
401 DateADT dateVal2 = PG_GETARG_DATEADT(1);
403 PG_RETURN_BOOL(dateVal1 != dateVal2);
407 date_lt(PG_FUNCTION_ARGS)
409 DateADT dateVal1 = PG_GETARG_DATEADT(0);
410 DateADT dateVal2 = PG_GETARG_DATEADT(1);
412 PG_RETURN_BOOL(dateVal1 < dateVal2);
416 date_le(PG_FUNCTION_ARGS)
418 DateADT dateVal1 = PG_GETARG_DATEADT(0);
419 DateADT dateVal2 = PG_GETARG_DATEADT(1);
421 PG_RETURN_BOOL(dateVal1 <= dateVal2);
425 date_gt(PG_FUNCTION_ARGS)
427 DateADT dateVal1 = PG_GETARG_DATEADT(0);
428 DateADT dateVal2 = PG_GETARG_DATEADT(1);
430 PG_RETURN_BOOL(dateVal1 > dateVal2);
434 date_ge(PG_FUNCTION_ARGS)
436 DateADT dateVal1 = PG_GETARG_DATEADT(0);
437 DateADT dateVal2 = PG_GETARG_DATEADT(1);
439 PG_RETURN_BOOL(dateVal1 >= dateVal2);
443 date_cmp(PG_FUNCTION_ARGS)
445 DateADT dateVal1 = PG_GETARG_DATEADT(0);
446 DateADT dateVal2 = PG_GETARG_DATEADT(1);
448 if (dateVal1 < dateVal2)
450 else if (dateVal1 > dateVal2)
456 date_fastcmp(Datum x, Datum y, SortSupport ssup)
458 DateADT a = DatumGetDateADT(x);
459 DateADT b = DatumGetDateADT(y);
469 date_sortsupport(PG_FUNCTION_ARGS)
471 SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0);
473 ssup->comparator = date_fastcmp;
478 date_finite(PG_FUNCTION_ARGS)
480 DateADT date = PG_GETARG_DATEADT(0);
482 PG_RETURN_BOOL(!DATE_NOT_FINITE(date));
486 date_larger(PG_FUNCTION_ARGS)
488 DateADT dateVal1 = PG_GETARG_DATEADT(0);
489 DateADT dateVal2 = PG_GETARG_DATEADT(1);
491 PG_RETURN_DATEADT((dateVal1 > dateVal2) ? dateVal1 : dateVal2);
495 date_smaller(PG_FUNCTION_ARGS)
497 DateADT dateVal1 = PG_GETARG_DATEADT(0);
498 DateADT dateVal2 = PG_GETARG_DATEADT(1);
500 PG_RETURN_DATEADT((dateVal1 < dateVal2) ? dateVal1 : dateVal2);
503 /* Compute difference between two dates in days.
506 date_mi(PG_FUNCTION_ARGS)
508 DateADT dateVal1 = PG_GETARG_DATEADT(0);
509 DateADT dateVal2 = PG_GETARG_DATEADT(1);
511 if (DATE_NOT_FINITE(dateVal1) || DATE_NOT_FINITE(dateVal2))
513 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
514 errmsg("cannot subtract infinite dates")));
516 PG_RETURN_INT32((int32) (dateVal1 - dateVal2));
519 /* Add a number of days to a date, giving a new date.
520 * Must handle both positive and negative numbers of days.
523 date_pli(PG_FUNCTION_ARGS)
525 DateADT dateVal = PG_GETARG_DATEADT(0);
526 int32 days = PG_GETARG_INT32(1);
529 if (DATE_NOT_FINITE(dateVal))
530 PG_RETURN_DATEADT(dateVal); /* can't change infinity */
532 result = dateVal + days;
534 /* Check for integer overflow and out-of-allowed-range */
535 if ((days >= 0 ? (result < dateVal) : (result > dateVal)) ||
536 !IS_VALID_DATE(result))
538 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
539 errmsg("date out of range")));
541 PG_RETURN_DATEADT(result);
544 /* Subtract a number of days from a date, giving a new date.
547 date_mii(PG_FUNCTION_ARGS)
549 DateADT dateVal = PG_GETARG_DATEADT(0);
550 int32 days = PG_GETARG_INT32(1);
553 if (DATE_NOT_FINITE(dateVal))
554 PG_RETURN_DATEADT(dateVal); /* can't change infinity */
556 result = dateVal - days;
558 /* Check for integer overflow and out-of-allowed-range */
559 if ((days >= 0 ? (result > dateVal) : (result < dateVal)) ||
560 !IS_VALID_DATE(result))
562 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
563 errmsg("date out of range")));
565 PG_RETURN_DATEADT(result);
569 * Internal routines for promoting date to timestamp and timestamp with
574 date2timestamp(DateADT dateVal)
578 if (DATE_IS_NOBEGIN(dateVal))
579 TIMESTAMP_NOBEGIN(result);
580 else if (DATE_IS_NOEND(dateVal))
581 TIMESTAMP_NOEND(result);
585 * Date's range is wider than timestamp's, so check for boundaries.
586 * Since dates have the same minimum values as timestamps, only upper
587 * boundary need be checked for overflow.
589 if (dateVal >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
591 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
592 errmsg("date out of range for timestamp")));
594 /* date is days since 2000, timestamp is microseconds since same... */
595 result = dateVal * USECS_PER_DAY;
602 date2timestamptz(DateADT dateVal)
609 if (DATE_IS_NOBEGIN(dateVal))
610 TIMESTAMP_NOBEGIN(result);
611 else if (DATE_IS_NOEND(dateVal))
612 TIMESTAMP_NOEND(result);
616 * Date's range is wider than timestamp's, so check for boundaries.
617 * Since dates have the same minimum values as timestamps, only upper
618 * boundary need be checked for overflow.
620 if (dateVal >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
622 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
623 errmsg("date out of range for timestamp")));
625 j2date(dateVal + POSTGRES_EPOCH_JDATE,
626 &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
630 tz = DetermineTimeZoneOffset(tm, session_timezone);
632 result = dateVal * USECS_PER_DAY + tz * USECS_PER_SEC;
635 * Since it is possible to go beyond allowed timestamptz range because
636 * of time zone, check for allowed timestamp range after adding tz.
638 if (!IS_VALID_TIMESTAMP(result))
640 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
641 errmsg("date out of range for timestamp")));
648 * date2timestamp_no_overflow
650 * This is chartered to produce a double value that is numerically
651 * equivalent to the corresponding Timestamp value, if the date is in the
652 * valid range of Timestamps, but in any case not throw an overflow error.
653 * We can do this since the numerical range of double is greater than
654 * that of non-erroneous timestamps. The results are currently only
655 * used for statistical estimation purposes.
658 date2timestamp_no_overflow(DateADT dateVal)
662 if (DATE_IS_NOBEGIN(dateVal))
664 else if (DATE_IS_NOEND(dateVal))
668 /* date is days since 2000, timestamp is microseconds since same... */
669 result = dateVal * (double) USECS_PER_DAY;
677 * Crosstype comparison functions for dates
681 date_eq_timestamp(PG_FUNCTION_ARGS)
683 DateADT dateVal = PG_GETARG_DATEADT(0);
684 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
687 dt1 = date2timestamp(dateVal);
689 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
693 date_ne_timestamp(PG_FUNCTION_ARGS)
695 DateADT dateVal = PG_GETARG_DATEADT(0);
696 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
699 dt1 = date2timestamp(dateVal);
701 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
705 date_lt_timestamp(PG_FUNCTION_ARGS)
707 DateADT dateVal = PG_GETARG_DATEADT(0);
708 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
711 dt1 = date2timestamp(dateVal);
713 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
717 date_gt_timestamp(PG_FUNCTION_ARGS)
719 DateADT dateVal = PG_GETARG_DATEADT(0);
720 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
723 dt1 = date2timestamp(dateVal);
725 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
729 date_le_timestamp(PG_FUNCTION_ARGS)
731 DateADT dateVal = PG_GETARG_DATEADT(0);
732 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
735 dt1 = date2timestamp(dateVal);
737 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
741 date_ge_timestamp(PG_FUNCTION_ARGS)
743 DateADT dateVal = PG_GETARG_DATEADT(0);
744 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
747 dt1 = date2timestamp(dateVal);
749 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
753 date_cmp_timestamp(PG_FUNCTION_ARGS)
755 DateADT dateVal = PG_GETARG_DATEADT(0);
756 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
759 dt1 = date2timestamp(dateVal);
761 PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
765 date_eq_timestamptz(PG_FUNCTION_ARGS)
767 DateADT dateVal = PG_GETARG_DATEADT(0);
768 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
771 dt1 = date2timestamptz(dateVal);
773 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) == 0);
777 date_ne_timestamptz(PG_FUNCTION_ARGS)
779 DateADT dateVal = PG_GETARG_DATEADT(0);
780 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
783 dt1 = date2timestamptz(dateVal);
785 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) != 0);
789 date_lt_timestamptz(PG_FUNCTION_ARGS)
791 DateADT dateVal = PG_GETARG_DATEADT(0);
792 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
795 dt1 = date2timestamptz(dateVal);
797 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) < 0);
801 date_gt_timestamptz(PG_FUNCTION_ARGS)
803 DateADT dateVal = PG_GETARG_DATEADT(0);
804 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
807 dt1 = date2timestamptz(dateVal);
809 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) > 0);
813 date_le_timestamptz(PG_FUNCTION_ARGS)
815 DateADT dateVal = PG_GETARG_DATEADT(0);
816 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
819 dt1 = date2timestamptz(dateVal);
821 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) <= 0);
825 date_ge_timestamptz(PG_FUNCTION_ARGS)
827 DateADT dateVal = PG_GETARG_DATEADT(0);
828 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
831 dt1 = date2timestamptz(dateVal);
833 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) >= 0);
837 date_cmp_timestamptz(PG_FUNCTION_ARGS)
839 DateADT dateVal = PG_GETARG_DATEADT(0);
840 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
843 dt1 = date2timestamptz(dateVal);
845 PG_RETURN_INT32(timestamptz_cmp_internal(dt1, dt2));
849 timestamp_eq_date(PG_FUNCTION_ARGS)
851 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
852 DateADT dateVal = PG_GETARG_DATEADT(1);
855 dt2 = date2timestamp(dateVal);
857 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
861 timestamp_ne_date(PG_FUNCTION_ARGS)
863 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
864 DateADT dateVal = PG_GETARG_DATEADT(1);
867 dt2 = date2timestamp(dateVal);
869 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
873 timestamp_lt_date(PG_FUNCTION_ARGS)
875 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
876 DateADT dateVal = PG_GETARG_DATEADT(1);
879 dt2 = date2timestamp(dateVal);
881 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
885 timestamp_gt_date(PG_FUNCTION_ARGS)
887 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
888 DateADT dateVal = PG_GETARG_DATEADT(1);
891 dt2 = date2timestamp(dateVal);
893 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
897 timestamp_le_date(PG_FUNCTION_ARGS)
899 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
900 DateADT dateVal = PG_GETARG_DATEADT(1);
903 dt2 = date2timestamp(dateVal);
905 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
909 timestamp_ge_date(PG_FUNCTION_ARGS)
911 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
912 DateADT dateVal = PG_GETARG_DATEADT(1);
915 dt2 = date2timestamp(dateVal);
917 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
921 timestamp_cmp_date(PG_FUNCTION_ARGS)
923 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
924 DateADT dateVal = PG_GETARG_DATEADT(1);
927 dt2 = date2timestamp(dateVal);
929 PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
933 timestamptz_eq_date(PG_FUNCTION_ARGS)
935 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
936 DateADT dateVal = PG_GETARG_DATEADT(1);
939 dt2 = date2timestamptz(dateVal);
941 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) == 0);
945 timestamptz_ne_date(PG_FUNCTION_ARGS)
947 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
948 DateADT dateVal = PG_GETARG_DATEADT(1);
951 dt2 = date2timestamptz(dateVal);
953 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) != 0);
957 timestamptz_lt_date(PG_FUNCTION_ARGS)
959 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
960 DateADT dateVal = PG_GETARG_DATEADT(1);
963 dt2 = date2timestamptz(dateVal);
965 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) < 0);
969 timestamptz_gt_date(PG_FUNCTION_ARGS)
971 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
972 DateADT dateVal = PG_GETARG_DATEADT(1);
975 dt2 = date2timestamptz(dateVal);
977 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) > 0);
981 timestamptz_le_date(PG_FUNCTION_ARGS)
983 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
984 DateADT dateVal = PG_GETARG_DATEADT(1);
987 dt2 = date2timestamptz(dateVal);
989 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) <= 0);
993 timestamptz_ge_date(PG_FUNCTION_ARGS)
995 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
996 DateADT dateVal = PG_GETARG_DATEADT(1);
999 dt2 = date2timestamptz(dateVal);
1001 PG_RETURN_BOOL(timestamptz_cmp_internal(dt1, dt2) >= 0);
1005 timestamptz_cmp_date(PG_FUNCTION_ARGS)
1007 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
1008 DateADT dateVal = PG_GETARG_DATEADT(1);
1011 dt2 = date2timestamptz(dateVal);
1013 PG_RETURN_INT32(timestamptz_cmp_internal(dt1, dt2));
1017 /* Add an interval to a date, giving a new date.
1018 * Must handle both positive and negative intervals.
1020 * We implement this by promoting the date to timestamp (without time zone)
1021 * and then using the timestamp plus interval function.
1024 date_pl_interval(PG_FUNCTION_ARGS)
1026 DateADT dateVal = PG_GETARG_DATEADT(0);
1027 Interval *span = PG_GETARG_INTERVAL_P(1);
1028 Timestamp dateStamp;
1030 dateStamp = date2timestamp(dateVal);
1032 return DirectFunctionCall2(timestamp_pl_interval,
1033 TimestampGetDatum(dateStamp),
1034 PointerGetDatum(span));
1037 /* Subtract an interval from a date, giving a new date.
1038 * Must handle both positive and negative intervals.
1040 * We implement this by promoting the date to timestamp (without time zone)
1041 * and then using the timestamp minus interval function.
1044 date_mi_interval(PG_FUNCTION_ARGS)
1046 DateADT dateVal = PG_GETARG_DATEADT(0);
1047 Interval *span = PG_GETARG_INTERVAL_P(1);
1048 Timestamp dateStamp;
1050 dateStamp = date2timestamp(dateVal);
1052 return DirectFunctionCall2(timestamp_mi_interval,
1053 TimestampGetDatum(dateStamp),
1054 PointerGetDatum(span));
1058 * Convert date to timestamp data type.
1061 date_timestamp(PG_FUNCTION_ARGS)
1063 DateADT dateVal = PG_GETARG_DATEADT(0);
1066 result = date2timestamp(dateVal);
1068 PG_RETURN_TIMESTAMP(result);
1072 * Convert timestamp to date data type.
1075 timestamp_date(PG_FUNCTION_ARGS)
1077 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
1083 if (TIMESTAMP_IS_NOBEGIN(timestamp))
1084 DATE_NOBEGIN(result);
1085 else if (TIMESTAMP_IS_NOEND(timestamp))
1089 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
1091 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1092 errmsg("timestamp out of range")));
1094 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
1097 PG_RETURN_DATEADT(result);
1101 /* date_timestamptz()
1102 * Convert date to timestamp with time zone data type.
1105 date_timestamptz(PG_FUNCTION_ARGS)
1107 DateADT dateVal = PG_GETARG_DATEADT(0);
1110 result = date2timestamptz(dateVal);
1112 PG_RETURN_TIMESTAMP(result);
1116 /* timestamptz_date()
1117 * Convert timestamp with time zone to date data type.
1120 timestamptz_date(PG_FUNCTION_ARGS)
1122 TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
1129 if (TIMESTAMP_IS_NOBEGIN(timestamp))
1130 DATE_NOBEGIN(result);
1131 else if (TIMESTAMP_IS_NOEND(timestamp))
1135 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
1137 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1138 errmsg("timestamp out of range")));
1140 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
1143 PG_RETURN_DATEADT(result);
1148 * Convert abstime to date data type.
1151 abstime_date(PG_FUNCTION_ARGS)
1153 AbsoluteTime abstime = PG_GETARG_ABSOLUTETIME(0);
1161 case INVALID_ABSTIME:
1163 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1164 errmsg("cannot convert reserved abstime value to date")));
1165 result = 0; /* keep compiler quiet */
1168 case NOSTART_ABSTIME:
1169 DATE_NOBEGIN(result);
1177 abstime2tm(abstime, &tz, tm, NULL);
1178 /* Prevent overflow in Julian-day routines */
1179 if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
1181 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1182 errmsg("abstime out of range for date")));
1183 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
1184 /* Now check for just-out-of-range dates */
1185 if (!IS_VALID_DATE(result))
1187 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1188 errmsg("abstime out of range for date")));
1192 PG_RETURN_DATEADT(result);
1196 /*****************************************************************************
1198 *****************************************************************************/
1201 time_in(PG_FUNCTION_ARGS)
1203 char *str = PG_GETARG_CSTRING(0);
1206 Oid typelem = PG_GETARG_OID(1);
1208 int32 typmod = PG_GETARG_INT32(2);
1216 char workbuf[MAXDATELEN + 1];
1217 char *field[MAXDATEFIELDS];
1219 int ftype[MAXDATEFIELDS];
1221 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
1222 field, ftype, MAXDATEFIELDS, &nf);
1224 dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz);
1226 DateTimeParseError(dterr, str, "time");
1228 tm2time(tm, fsec, &result);
1229 AdjustTimeForTypmod(&result, typmod);
1231 PG_RETURN_TIMEADT(result);
1235 * Convert a tm structure to a time data type.
1238 tm2time(struct pg_tm *tm, fsec_t fsec, TimeADT *result)
1240 *result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec)
1241 * USECS_PER_SEC) + fsec;
1246 * Convert time data type to POSIX time structure.
1248 * For dates within the range of pg_time_t, convert to the local time zone.
1249 * If out of this range, leave as UTC (in practice that could only happen
1250 * if pg_time_t is just 32 bits) - thomas 97/05/27
1253 time2tm(TimeADT time, struct pg_tm *tm, fsec_t *fsec)
1255 tm->tm_hour = time / USECS_PER_HOUR;
1256 time -= tm->tm_hour * USECS_PER_HOUR;
1257 tm->tm_min = time / USECS_PER_MINUTE;
1258 time -= tm->tm_min * USECS_PER_MINUTE;
1259 tm->tm_sec = time / USECS_PER_SEC;
1260 time -= tm->tm_sec * USECS_PER_SEC;
1266 time_out(PG_FUNCTION_ARGS)
1268 TimeADT time = PG_GETARG_TIMEADT(0);
1273 char buf[MAXDATELEN + 1];
1275 time2tm(time, tm, &fsec);
1276 EncodeTimeOnly(tm, fsec, false, 0, DateStyle, buf);
1278 result = pstrdup(buf);
1279 PG_RETURN_CSTRING(result);
1283 * time_recv - converts external binary format to time
1286 time_recv(PG_FUNCTION_ARGS)
1288 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
1291 Oid typelem = PG_GETARG_OID(1);
1293 int32 typmod = PG_GETARG_INT32(2);
1296 result = pq_getmsgint64(buf);
1298 if (result < INT64CONST(0) || result > USECS_PER_DAY)
1300 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1301 errmsg("time out of range")));
1303 AdjustTimeForTypmod(&result, typmod);
1305 PG_RETURN_TIMEADT(result);
1309 * time_send - converts time to binary format
1312 time_send(PG_FUNCTION_ARGS)
1314 TimeADT time = PG_GETARG_TIMEADT(0);
1317 pq_begintypsend(&buf);
1318 pq_sendint64(&buf, time);
1319 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
1323 timetypmodin(PG_FUNCTION_ARGS)
1325 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
1327 PG_RETURN_INT32(anytime_typmodin(false, ta));
1331 timetypmodout(PG_FUNCTION_ARGS)
1333 int32 typmod = PG_GETARG_INT32(0);
1335 PG_RETURN_CSTRING(anytime_typmodout(false, typmod));
1339 * make_time - time constructor
1342 make_time(PG_FUNCTION_ARGS)
1344 int tm_hour = PG_GETARG_INT32(0);
1345 int tm_min = PG_GETARG_INT32(1);
1346 double sec = PG_GETARG_FLOAT8(2);
1349 /* This should match the checks in DecodeTimeOnly */
1350 if (tm_hour < 0 || tm_min < 0 || tm_min > MINS_PER_HOUR - 1 ||
1351 sec < 0 || sec > SECS_PER_MINUTE ||
1352 tm_hour > HOURS_PER_DAY ||
1353 /* test for > 24:00:00 */
1354 (tm_hour == HOURS_PER_DAY && (tm_min > 0 || sec > 0)))
1356 (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
1357 errmsg("time field value out of range: %d:%02d:%02g",
1358 tm_hour, tm_min, sec)));
1360 /* This should match tm2time */
1361 time = (((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE)
1362 * USECS_PER_SEC) + rint(sec * USECS_PER_SEC);
1364 PG_RETURN_TIMEADT(time);
1369 * Flatten calls to time_scale() and timetz_scale() that solely represent
1370 * increases in allowed precision.
1373 time_transform(PG_FUNCTION_ARGS)
1375 PG_RETURN_POINTER(TemporalTransform(MAX_TIME_PRECISION,
1376 (Node *) PG_GETARG_POINTER(0)));
1380 * Adjust time type for specified scale factor.
1381 * Used by PostgreSQL type system to stuff columns.
1384 time_scale(PG_FUNCTION_ARGS)
1386 TimeADT time = PG_GETARG_TIMEADT(0);
1387 int32 typmod = PG_GETARG_INT32(1);
1391 AdjustTimeForTypmod(&result, typmod);
1393 PG_RETURN_TIMEADT(result);
1396 /* AdjustTimeForTypmod()
1397 * Force the precision of the time value to a specified value.
1398 * Uses *exactly* the same code as in AdjustTimestampForTypemod()
1399 * but we make a separate copy because those types do not
1400 * have a fundamental tie together but rather a coincidence of
1401 * implementation. - thomas
1404 AdjustTimeForTypmod(TimeADT *time, int32 typmod)
1406 static const int64 TimeScales[MAX_TIME_PRECISION + 1] = {
1407 INT64CONST(1000000),
1416 static const int64 TimeOffsets[MAX_TIME_PRECISION + 1] = {
1426 if (typmod >= 0 && typmod <= MAX_TIME_PRECISION)
1428 if (*time >= INT64CONST(0))
1429 *time = ((*time + TimeOffsets[typmod]) / TimeScales[typmod]) *
1432 *time = -((((-*time) + TimeOffsets[typmod]) / TimeScales[typmod]) *
1433 TimeScales[typmod]);
1439 time_eq(PG_FUNCTION_ARGS)
1441 TimeADT time1 = PG_GETARG_TIMEADT(0);
1442 TimeADT time2 = PG_GETARG_TIMEADT(1);
1444 PG_RETURN_BOOL(time1 == time2);
1448 time_ne(PG_FUNCTION_ARGS)
1450 TimeADT time1 = PG_GETARG_TIMEADT(0);
1451 TimeADT time2 = PG_GETARG_TIMEADT(1);
1453 PG_RETURN_BOOL(time1 != time2);
1457 time_lt(PG_FUNCTION_ARGS)
1459 TimeADT time1 = PG_GETARG_TIMEADT(0);
1460 TimeADT time2 = PG_GETARG_TIMEADT(1);
1462 PG_RETURN_BOOL(time1 < time2);
1466 time_le(PG_FUNCTION_ARGS)
1468 TimeADT time1 = PG_GETARG_TIMEADT(0);
1469 TimeADT time2 = PG_GETARG_TIMEADT(1);
1471 PG_RETURN_BOOL(time1 <= time2);
1475 time_gt(PG_FUNCTION_ARGS)
1477 TimeADT time1 = PG_GETARG_TIMEADT(0);
1478 TimeADT time2 = PG_GETARG_TIMEADT(1);
1480 PG_RETURN_BOOL(time1 > time2);
1484 time_ge(PG_FUNCTION_ARGS)
1486 TimeADT time1 = PG_GETARG_TIMEADT(0);
1487 TimeADT time2 = PG_GETARG_TIMEADT(1);
1489 PG_RETURN_BOOL(time1 >= time2);
1493 time_cmp(PG_FUNCTION_ARGS)
1495 TimeADT time1 = PG_GETARG_TIMEADT(0);
1496 TimeADT time2 = PG_GETARG_TIMEADT(1);
1499 PG_RETURN_INT32(-1);
1506 time_hash(PG_FUNCTION_ARGS)
1508 return hashint8(fcinfo);
1512 time_larger(PG_FUNCTION_ARGS)
1514 TimeADT time1 = PG_GETARG_TIMEADT(0);
1515 TimeADT time2 = PG_GETARG_TIMEADT(1);
1517 PG_RETURN_TIMEADT((time1 > time2) ? time1 : time2);
1521 time_smaller(PG_FUNCTION_ARGS)
1523 TimeADT time1 = PG_GETARG_TIMEADT(0);
1524 TimeADT time2 = PG_GETARG_TIMEADT(1);
1526 PG_RETURN_TIMEADT((time1 < time2) ? time1 : time2);
1529 /* overlaps_time() --- implements the SQL OVERLAPS operator.
1531 * Algorithm is per SQL spec. This is much harder than you'd think
1532 * because the spec requires us to deliver a non-null answer in some cases
1533 * where some of the inputs are null.
1536 overlaps_time(PG_FUNCTION_ARGS)
1539 * The arguments are TimeADT, but we leave them as generic Datums to avoid
1540 * dereferencing nulls (TimeADT is pass-by-reference!)
1542 Datum ts1 = PG_GETARG_DATUM(0);
1543 Datum te1 = PG_GETARG_DATUM(1);
1544 Datum ts2 = PG_GETARG_DATUM(2);
1545 Datum te2 = PG_GETARG_DATUM(3);
1546 bool ts1IsNull = PG_ARGISNULL(0);
1547 bool te1IsNull = PG_ARGISNULL(1);
1548 bool ts2IsNull = PG_ARGISNULL(2);
1549 bool te2IsNull = PG_ARGISNULL(3);
1551 #define TIMEADT_GT(t1,t2) \
1552 (DatumGetTimeADT(t1) > DatumGetTimeADT(t2))
1553 #define TIMEADT_LT(t1,t2) \
1554 (DatumGetTimeADT(t1) < DatumGetTimeADT(t2))
1557 * If both endpoints of interval 1 are null, the result is null (unknown).
1558 * If just one endpoint is null, take ts1 as the non-null one. Otherwise,
1559 * take ts1 as the lesser endpoint.
1565 /* swap null for non-null */
1569 else if (!te1IsNull)
1571 if (TIMEADT_GT(ts1, te1))
1580 /* Likewise for interval 2. */
1585 /* swap null for non-null */
1589 else if (!te2IsNull)
1591 if (TIMEADT_GT(ts2, te2))
1601 * At this point neither ts1 nor ts2 is null, so we can consider three
1602 * cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
1604 if (TIMEADT_GT(ts1, ts2))
1607 * This case is ts1 < te2 OR te1 < te2, which may look redundant but
1608 * in the presence of nulls it's not quite completely so.
1612 if (TIMEADT_LT(ts1, te2))
1613 PG_RETURN_BOOL(true);
1618 * If te1 is not null then we had ts1 <= te1 above, and we just found
1619 * ts1 >= te2, hence te1 >= te2.
1621 PG_RETURN_BOOL(false);
1623 else if (TIMEADT_LT(ts1, ts2))
1625 /* This case is ts2 < te1 OR te2 < te1 */
1628 if (TIMEADT_LT(ts2, te1))
1629 PG_RETURN_BOOL(true);
1634 * If te2 is not null then we had ts2 <= te2 above, and we just found
1635 * ts2 >= te1, hence te2 >= te1.
1637 PG_RETURN_BOOL(false);
1642 * For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
1643 * rather silly way of saying "true if both are nonnull, else null".
1645 if (te1IsNull || te2IsNull)
1647 PG_RETURN_BOOL(true);
1655 * Convert timestamp to time data type.
1658 timestamp_time(PG_FUNCTION_ARGS)
1660 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
1666 if (TIMESTAMP_NOT_FINITE(timestamp))
1669 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
1671 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1672 errmsg("timestamp out of range")));
1675 * Could also do this with time = (timestamp / USECS_PER_DAY *
1676 * USECS_PER_DAY) - timestamp;
1678 result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
1679 USECS_PER_SEC) + fsec;
1681 PG_RETURN_TIMEADT(result);
1684 /* timestamptz_time()
1685 * Convert timestamptz to time data type.
1688 timestamptz_time(PG_FUNCTION_ARGS)
1690 TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
1697 if (TIMESTAMP_NOT_FINITE(timestamp))
1700 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
1702 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1703 errmsg("timestamp out of range")));
1706 * Could also do this with time = (timestamp / USECS_PER_DAY *
1707 * USECS_PER_DAY) - timestamp;
1709 result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
1710 USECS_PER_SEC) + fsec;
1712 PG_RETURN_TIMEADT(result);
1715 /* datetime_timestamp()
1716 * Convert date and time to timestamp data type.
1719 datetime_timestamp(PG_FUNCTION_ARGS)
1721 DateADT date = PG_GETARG_DATEADT(0);
1722 TimeADT time = PG_GETARG_TIMEADT(1);
1725 result = date2timestamp(date);
1726 if (!TIMESTAMP_NOT_FINITE(result))
1729 if (!IS_VALID_TIMESTAMP(result))
1731 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1732 errmsg("timestamp out of range")));
1735 PG_RETURN_TIMESTAMP(result);
1739 * Convert time to interval data type.
1742 time_interval(PG_FUNCTION_ARGS)
1744 TimeADT time = PG_GETARG_TIMEADT(0);
1747 result = (Interval *) palloc(sizeof(Interval));
1749 result->time = time;
1753 PG_RETURN_INTERVAL_P(result);
1757 * Convert interval to time data type.
1759 * This is defined as producing the fractional-day portion of the interval.
1760 * Therefore, we can just ignore the months field. It is not real clear
1761 * what to do with negative intervals, but we choose to subtract the floor,
1762 * so that, say, '-2 hours' becomes '22:00:00'.
1765 interval_time(PG_FUNCTION_ARGS)
1767 Interval *span = PG_GETARG_INTERVAL_P(0);
1771 result = span->time;
1772 if (result >= USECS_PER_DAY)
1774 days = result / USECS_PER_DAY;
1775 result -= days * USECS_PER_DAY;
1777 else if (result < 0)
1779 days = (-result + USECS_PER_DAY - 1) / USECS_PER_DAY;
1780 result += days * USECS_PER_DAY;
1783 PG_RETURN_TIMEADT(result);
1787 * Subtract two times to produce an interval.
1790 time_mi_time(PG_FUNCTION_ARGS)
1792 TimeADT time1 = PG_GETARG_TIMEADT(0);
1793 TimeADT time2 = PG_GETARG_TIMEADT(1);
1796 result = (Interval *) palloc(sizeof(Interval));
1800 result->time = time1 - time2;
1802 PG_RETURN_INTERVAL_P(result);
1805 /* time_pl_interval()
1806 * Add interval to time.
1809 time_pl_interval(PG_FUNCTION_ARGS)
1811 TimeADT time = PG_GETARG_TIMEADT(0);
1812 Interval *span = PG_GETARG_INTERVAL_P(1);
1815 result = time + span->time;
1816 result -= result / USECS_PER_DAY * USECS_PER_DAY;
1817 if (result < INT64CONST(0))
1818 result += USECS_PER_DAY;
1820 PG_RETURN_TIMEADT(result);
1823 /* time_mi_interval()
1824 * Subtract interval from time.
1827 time_mi_interval(PG_FUNCTION_ARGS)
1829 TimeADT time = PG_GETARG_TIMEADT(0);
1830 Interval *span = PG_GETARG_INTERVAL_P(1);
1833 result = time - span->time;
1834 result -= result / USECS_PER_DAY * USECS_PER_DAY;
1835 if (result < INT64CONST(0))
1836 result += USECS_PER_DAY;
1838 PG_RETURN_TIMEADT(result);
1843 * Extract specified field from time type.
1846 time_part(PG_FUNCTION_ARGS)
1848 text *units = PG_GETARG_TEXT_PP(0);
1849 TimeADT time = PG_GETARG_TIMEADT(1);
1855 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
1856 VARSIZE_ANY_EXHDR(units),
1859 type = DecodeUnits(0, lowunits, &val);
1860 if (type == UNKNOWN_FIELD)
1861 type = DecodeSpecial(0, lowunits, &val);
1869 time2tm(time, tm, &fsec);
1874 result = tm->tm_sec * 1000000.0 + fsec;
1878 result = tm->tm_sec * 1000.0 + fsec / 1000.0;
1882 result = tm->tm_sec + fsec / 1000000.0;
1886 result = tm->tm_min;
1890 result = tm->tm_hour;
1902 case DTK_MILLENNIUM:
1906 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1907 errmsg("\"time\" units \"%s\" not recognized",
1912 else if (type == RESERV && val == DTK_EPOCH)
1914 result = time / 1000000.0;
1919 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1920 errmsg("\"time\" units \"%s\" not recognized",
1925 PG_RETURN_FLOAT8(result);
1929 /*****************************************************************************
1930 * Time With Time Zone ADT
1931 *****************************************************************************/
1934 * Convert a tm structure to a time data type.
1937 tm2timetz(struct pg_tm *tm, fsec_t fsec, int tz, TimeTzADT *result)
1939 result->time = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
1940 USECS_PER_SEC) + fsec;
1947 timetz_in(PG_FUNCTION_ARGS)
1949 char *str = PG_GETARG_CSTRING(0);
1952 Oid typelem = PG_GETARG_OID(1);
1954 int32 typmod = PG_GETARG_INT32(2);
1962 char workbuf[MAXDATELEN + 1];
1963 char *field[MAXDATEFIELDS];
1965 int ftype[MAXDATEFIELDS];
1967 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
1968 field, ftype, MAXDATEFIELDS, &nf);
1970 dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz);
1972 DateTimeParseError(dterr, str, "time with time zone");
1974 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
1975 tm2timetz(tm, fsec, tz, result);
1976 AdjustTimeForTypmod(&(result->time), typmod);
1978 PG_RETURN_TIMETZADT_P(result);
1982 timetz_out(PG_FUNCTION_ARGS)
1984 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
1990 char buf[MAXDATELEN + 1];
1992 timetz2tm(time, tm, &fsec, &tz);
1993 EncodeTimeOnly(tm, fsec, true, tz, DateStyle, buf);
1995 result = pstrdup(buf);
1996 PG_RETURN_CSTRING(result);
2000 * timetz_recv - converts external binary format to timetz
2003 timetz_recv(PG_FUNCTION_ARGS)
2005 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
2008 Oid typelem = PG_GETARG_OID(1);
2010 int32 typmod = PG_GETARG_INT32(2);
2013 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2015 result->time = pq_getmsgint64(buf);
2017 if (result->time < INT64CONST(0) || result->time > USECS_PER_DAY)
2019 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2020 errmsg("time out of range")));
2022 result->zone = pq_getmsgint(buf, sizeof(result->zone));
2024 /* Check for sane GMT displacement; see notes in datatype/timestamp.h */
2025 if (result->zone <= -TZDISP_LIMIT || result->zone >= TZDISP_LIMIT)
2027 (errcode(ERRCODE_INVALID_TIME_ZONE_DISPLACEMENT_VALUE),
2028 errmsg("time zone displacement out of range")));
2030 AdjustTimeForTypmod(&(result->time), typmod);
2032 PG_RETURN_TIMETZADT_P(result);
2036 * timetz_send - converts timetz to binary format
2039 timetz_send(PG_FUNCTION_ARGS)
2041 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
2044 pq_begintypsend(&buf);
2045 pq_sendint64(&buf, time->time);
2046 pq_sendint(&buf, time->zone, sizeof(time->zone));
2047 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
2051 timetztypmodin(PG_FUNCTION_ARGS)
2053 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
2055 PG_RETURN_INT32(anytime_typmodin(true, ta));
2059 timetztypmodout(PG_FUNCTION_ARGS)
2061 int32 typmod = PG_GETARG_INT32(0);
2063 PG_RETURN_CSTRING(anytime_typmodout(true, typmod));
2068 * Convert TIME WITH TIME ZONE data type to POSIX time structure.
2071 timetz2tm(TimeTzADT *time, struct pg_tm *tm, fsec_t *fsec, int *tzp)
2073 TimeOffset trem = time->time;
2075 tm->tm_hour = trem / USECS_PER_HOUR;
2076 trem -= tm->tm_hour * USECS_PER_HOUR;
2077 tm->tm_min = trem / USECS_PER_MINUTE;
2078 trem -= tm->tm_min * USECS_PER_MINUTE;
2079 tm->tm_sec = trem / USECS_PER_SEC;
2080 *fsec = trem - tm->tm_sec * USECS_PER_SEC;
2089 * Adjust time type for specified scale factor.
2090 * Used by PostgreSQL type system to stuff columns.
2093 timetz_scale(PG_FUNCTION_ARGS)
2095 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
2096 int32 typmod = PG_GETARG_INT32(1);
2099 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2101 result->time = time->time;
2102 result->zone = time->zone;
2104 AdjustTimeForTypmod(&(result->time), typmod);
2106 PG_RETURN_TIMETZADT_P(result);
2111 timetz_cmp_internal(TimeTzADT *time1, TimeTzADT *time2)
2116 /* Primary sort is by true (GMT-equivalent) time */
2117 t1 = time1->time + (time1->zone * USECS_PER_SEC);
2118 t2 = time2->time + (time2->zone * USECS_PER_SEC);
2126 * If same GMT time, sort by timezone; we only want to say that two
2127 * timetz's are equal if both the time and zone parts are equal.
2129 if (time1->zone > time2->zone)
2131 if (time1->zone < time2->zone)
2138 timetz_eq(PG_FUNCTION_ARGS)
2140 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2141 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2143 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) == 0);
2147 timetz_ne(PG_FUNCTION_ARGS)
2149 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2150 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2152 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) != 0);
2156 timetz_lt(PG_FUNCTION_ARGS)
2158 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2159 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2161 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) < 0);
2165 timetz_le(PG_FUNCTION_ARGS)
2167 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2168 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2170 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) <= 0);
2174 timetz_gt(PG_FUNCTION_ARGS)
2176 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2177 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2179 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) > 0);
2183 timetz_ge(PG_FUNCTION_ARGS)
2185 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2186 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2188 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) >= 0);
2192 timetz_cmp(PG_FUNCTION_ARGS)
2194 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2195 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2197 PG_RETURN_INT32(timetz_cmp_internal(time1, time2));
2201 timetz_hash(PG_FUNCTION_ARGS)
2203 TimeTzADT *key = PG_GETARG_TIMETZADT_P(0);
2207 * To avoid any problems with padding bytes in the struct, we figure the
2208 * field hashes separately and XOR them.
2210 thash = DatumGetUInt32(DirectFunctionCall1(hashint8,
2211 Int64GetDatumFast(key->time)));
2212 thash ^= DatumGetUInt32(hash_uint32(key->zone));
2213 PG_RETURN_UINT32(thash);
2217 timetz_larger(PG_FUNCTION_ARGS)
2219 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2220 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2223 if (timetz_cmp_internal(time1, time2) > 0)
2227 PG_RETURN_TIMETZADT_P(result);
2231 timetz_smaller(PG_FUNCTION_ARGS)
2233 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2234 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2237 if (timetz_cmp_internal(time1, time2) < 0)
2241 PG_RETURN_TIMETZADT_P(result);
2244 /* timetz_pl_interval()
2245 * Add interval to timetz.
2248 timetz_pl_interval(PG_FUNCTION_ARGS)
2250 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
2251 Interval *span = PG_GETARG_INTERVAL_P(1);
2254 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2256 result->time = time->time + span->time;
2257 result->time -= result->time / USECS_PER_DAY * USECS_PER_DAY;
2258 if (result->time < INT64CONST(0))
2259 result->time += USECS_PER_DAY;
2261 result->zone = time->zone;
2263 PG_RETURN_TIMETZADT_P(result);
2266 /* timetz_mi_interval()
2267 * Subtract interval from timetz.
2270 timetz_mi_interval(PG_FUNCTION_ARGS)
2272 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
2273 Interval *span = PG_GETARG_INTERVAL_P(1);
2276 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2278 result->time = time->time - span->time;
2279 result->time -= result->time / USECS_PER_DAY * USECS_PER_DAY;
2280 if (result->time < INT64CONST(0))
2281 result->time += USECS_PER_DAY;
2283 result->zone = time->zone;
2285 PG_RETURN_TIMETZADT_P(result);
2288 /* overlaps_timetz() --- implements the SQL OVERLAPS operator.
2290 * Algorithm is per SQL spec. This is much harder than you'd think
2291 * because the spec requires us to deliver a non-null answer in some cases
2292 * where some of the inputs are null.
2295 overlaps_timetz(PG_FUNCTION_ARGS)
2298 * The arguments are TimeTzADT *, but we leave them as generic Datums for
2299 * convenience of notation --- and to avoid dereferencing nulls.
2301 Datum ts1 = PG_GETARG_DATUM(0);
2302 Datum te1 = PG_GETARG_DATUM(1);
2303 Datum ts2 = PG_GETARG_DATUM(2);
2304 Datum te2 = PG_GETARG_DATUM(3);
2305 bool ts1IsNull = PG_ARGISNULL(0);
2306 bool te1IsNull = PG_ARGISNULL(1);
2307 bool ts2IsNull = PG_ARGISNULL(2);
2308 bool te2IsNull = PG_ARGISNULL(3);
2310 #define TIMETZ_GT(t1,t2) \
2311 DatumGetBool(DirectFunctionCall2(timetz_gt,t1,t2))
2312 #define TIMETZ_LT(t1,t2) \
2313 DatumGetBool(DirectFunctionCall2(timetz_lt,t1,t2))
2316 * If both endpoints of interval 1 are null, the result is null (unknown).
2317 * If just one endpoint is null, take ts1 as the non-null one. Otherwise,
2318 * take ts1 as the lesser endpoint.
2324 /* swap null for non-null */
2328 else if (!te1IsNull)
2330 if (TIMETZ_GT(ts1, te1))
2339 /* Likewise for interval 2. */
2344 /* swap null for non-null */
2348 else if (!te2IsNull)
2350 if (TIMETZ_GT(ts2, te2))
2360 * At this point neither ts1 nor ts2 is null, so we can consider three
2361 * cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
2363 if (TIMETZ_GT(ts1, ts2))
2366 * This case is ts1 < te2 OR te1 < te2, which may look redundant but
2367 * in the presence of nulls it's not quite completely so.
2371 if (TIMETZ_LT(ts1, te2))
2372 PG_RETURN_BOOL(true);
2377 * If te1 is not null then we had ts1 <= te1 above, and we just found
2378 * ts1 >= te2, hence te1 >= te2.
2380 PG_RETURN_BOOL(false);
2382 else if (TIMETZ_LT(ts1, ts2))
2384 /* This case is ts2 < te1 OR te2 < te1 */
2387 if (TIMETZ_LT(ts2, te1))
2388 PG_RETURN_BOOL(true);
2393 * If te2 is not null then we had ts2 <= te2 above, and we just found
2394 * ts2 >= te1, hence te2 >= te1.
2396 PG_RETURN_BOOL(false);
2401 * For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
2402 * rather silly way of saying "true if both are nonnull, else null".
2404 if (te1IsNull || te2IsNull)
2406 PG_RETURN_BOOL(true);
2415 timetz_time(PG_FUNCTION_ARGS)
2417 TimeTzADT *timetz = PG_GETARG_TIMETZADT_P(0);
2420 /* swallow the time zone and just return the time */
2421 result = timetz->time;
2423 PG_RETURN_TIMEADT(result);
2428 time_timetz(PG_FUNCTION_ARGS)
2430 TimeADT time = PG_GETARG_TIMEADT(0);
2437 GetCurrentDateTime(tm);
2438 time2tm(time, tm, &fsec);
2439 tz = DetermineTimeZoneOffset(tm, session_timezone);
2441 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2443 result->time = time;
2446 PG_RETURN_TIMETZADT_P(result);
2450 /* timestamptz_timetz()
2451 * Convert timestamp to timetz data type.
2454 timestamptz_timetz(PG_FUNCTION_ARGS)
2456 TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
2463 if (TIMESTAMP_NOT_FINITE(timestamp))
2466 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
2468 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2469 errmsg("timestamp out of range")));
2471 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2473 tm2timetz(tm, fsec, tz, result);
2475 PG_RETURN_TIMETZADT_P(result);
2479 /* datetimetz_timestamptz()
2480 * Convert date and timetz to timestamp with time zone data type.
2481 * Timestamp is stored in GMT, so add the time zone
2482 * stored with the timetz to the result.
2483 * - thomas 2000-03-10
2486 datetimetz_timestamptz(PG_FUNCTION_ARGS)
2488 DateADT date = PG_GETARG_DATEADT(0);
2489 TimeTzADT *time = PG_GETARG_TIMETZADT_P(1);
2492 if (DATE_IS_NOBEGIN(date))
2493 TIMESTAMP_NOBEGIN(result);
2494 else if (DATE_IS_NOEND(date))
2495 TIMESTAMP_NOEND(result);
2499 * Date's range is wider than timestamp's, so check for boundaries.
2500 * Since dates have the same minimum values as timestamps, only upper
2501 * boundary need be checked for overflow.
2503 if (date >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
2505 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2506 errmsg("date out of range for timestamp")));
2507 result = date * USECS_PER_DAY + time->time + time->zone * USECS_PER_SEC;
2510 * Since it is possible to go beyond allowed timestamptz range because
2511 * of time zone, check for allowed timestamp range after adding tz.
2513 if (!IS_VALID_TIMESTAMP(result))
2515 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2516 errmsg("date out of range for timestamp")));
2519 PG_RETURN_TIMESTAMP(result);
2524 * Extract specified field from time type.
2527 timetz_part(PG_FUNCTION_ARGS)
2529 text *units = PG_GETARG_TEXT_PP(0);
2530 TimeTzADT *time = PG_GETARG_TIMETZADT_P(1);
2536 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
2537 VARSIZE_ANY_EXHDR(units),
2540 type = DecodeUnits(0, lowunits, &val);
2541 if (type == UNKNOWN_FIELD)
2542 type = DecodeSpecial(0, lowunits, &val);
2552 timetz2tm(time, tm, &fsec, &tz);
2562 result /= SECS_PER_MINUTE;
2563 FMODULO(result, dummy, (double) SECS_PER_MINUTE);
2568 FMODULO(dummy, result, (double) SECS_PER_HOUR);
2572 result = tm->tm_sec * 1000000.0 + fsec;
2576 result = tm->tm_sec * 1000.0 + fsec / 1000.0;
2580 result = tm->tm_sec + fsec / 1000000.0;
2584 result = tm->tm_min;
2588 result = tm->tm_hour;
2597 case DTK_MILLENNIUM:
2600 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2601 errmsg("\"time with time zone\" units \"%s\" not recognized",
2606 else if (type == RESERV && val == DTK_EPOCH)
2608 result = time->time / 1000000.0 + time->zone;
2613 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2614 errmsg("\"time with time zone\" units \"%s\" not recognized",
2619 PG_RETURN_FLOAT8(result);
2623 * Encode time with time zone type with specified time zone.
2624 * Applies DST rules as of the current date.
2627 timetz_zone(PG_FUNCTION_ARGS)
2629 text *zone = PG_GETARG_TEXT_PP(0);
2630 TimeTzADT *t = PG_GETARG_TIMETZADT_P(1);
2633 char tzname[TZ_STRLEN_MAX + 1];
2640 * Look up the requested timezone. First we look in the timezone
2641 * abbreviation table (to handle cases like "EST"), and if that fails, we
2642 * look in the timezone database (to handle cases like
2643 * "America/New_York"). (This matches the order in which timestamp input
2644 * checks the cases; it's important because the timezone database unwisely
2645 * uses a few zone names that are identical to offset abbreviations.)
2647 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
2649 /* DecodeTimezoneAbbrev requires lowercase input */
2650 lowzone = downcase_truncate_identifier(tzname,
2654 type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
2656 if (type == TZ || type == DTZ)
2658 /* fixed-offset abbreviation */
2661 else if (type == DYNTZ)
2663 /* dynamic-offset abbreviation, resolve using current time */
2664 pg_time_t now = (pg_time_t) time(NULL);
2667 tm = pg_localtime(&now, tzp);
2668 tz = DetermineTimeZoneAbbrevOffset(tm, tzname, tzp);
2672 /* try it as a full zone name */
2673 tzp = pg_tzset(tzname);
2676 /* Get the offset-from-GMT that is valid today for the zone */
2677 pg_time_t now = (pg_time_t) time(NULL);
2680 tm = pg_localtime(&now, tzp);
2681 tz = -tm->tm_gmtoff;
2686 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2687 errmsg("time zone \"%s\" not recognized", tzname)));
2688 tz = 0; /* keep compiler quiet */
2692 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2694 result->time = t->time + (t->zone - tz) * USECS_PER_SEC;
2695 while (result->time < INT64CONST(0))
2696 result->time += USECS_PER_DAY;
2697 while (result->time >= USECS_PER_DAY)
2698 result->time -= USECS_PER_DAY;
2702 PG_RETURN_TIMETZADT_P(result);
2706 * Encode time with time zone type with specified time interval as time zone.
2709 timetz_izone(PG_FUNCTION_ARGS)
2711 Interval *zone = PG_GETARG_INTERVAL_P(0);
2712 TimeTzADT *time = PG_GETARG_TIMETZADT_P(1);
2716 if (zone->month != 0 || zone->day != 0)
2718 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2719 errmsg("interval time zone \"%s\" must not include months or days",
2720 DatumGetCString(DirectFunctionCall1(interval_out,
2721 PointerGetDatum(zone))))));
2723 tz = -(zone->time / USECS_PER_SEC);
2725 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2727 result->time = time->time + (time->zone - tz) * USECS_PER_SEC;
2728 while (result->time < INT64CONST(0))
2729 result->time += USECS_PER_DAY;
2730 while (result->time >= USECS_PER_DAY)
2731 result->time -= USECS_PER_DAY;
2735 PG_RETURN_TIMETZADT_P(result);