1 /*-------------------------------------------------------------------------
4 * implements DATE and TIME data types specified in SQL-92 standard
6 * Portions Copyright (c) 1996-2012, 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);
974 if (TIMESTAMP_IS_NOBEGIN(timestamp))
975 DATE_NOBEGIN(result);
976 else if (TIMESTAMP_IS_NOEND(timestamp))
980 if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) != 0)
982 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
983 errmsg("timestamp out of range")));
985 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
988 PG_RETURN_DATEADT(result);
993 * Convert abstime to date data type.
996 abstime_date(PG_FUNCTION_ARGS)
998 AbsoluteTime abstime = PG_GETARG_ABSOLUTETIME(0);
1006 case INVALID_ABSTIME:
1008 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1009 errmsg("cannot convert reserved abstime value to date")));
1010 result = 0; /* keep compiler quiet */
1013 case NOSTART_ABSTIME:
1014 DATE_NOBEGIN(result);
1022 abstime2tm(abstime, &tz, tm, NULL);
1023 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
1027 PG_RETURN_DATEADT(result);
1031 /*****************************************************************************
1033 *****************************************************************************/
1036 time_in(PG_FUNCTION_ARGS)
1038 char *str = PG_GETARG_CSTRING(0);
1041 Oid typelem = PG_GETARG_OID(1);
1043 int32 typmod = PG_GETARG_INT32(2);
1051 char workbuf[MAXDATELEN + 1];
1052 char *field[MAXDATEFIELDS];
1054 int ftype[MAXDATEFIELDS];
1056 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
1057 field, ftype, MAXDATEFIELDS, &nf);
1059 dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz);
1061 DateTimeParseError(dterr, str, "time");
1063 tm2time(tm, fsec, &result);
1064 AdjustTimeForTypmod(&result, typmod);
1066 PG_RETURN_TIMEADT(result);
1070 * Convert a tm structure to a time data type.
1073 tm2time(struct pg_tm * tm, fsec_t fsec, TimeADT *result)
1075 #ifdef HAVE_INT64_TIMESTAMP
1076 *result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec)
1077 * USECS_PER_SEC) + fsec;
1079 *result = ((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec + fsec;
1085 * Convert time data type to POSIX time structure.
1087 * For dates within the range of pg_time_t, convert to the local time zone.
1088 * If out of this range, leave as UTC (in practice that could only happen
1089 * if pg_time_t is just 32 bits) - thomas 97/05/27
1092 time2tm(TimeADT time, struct pg_tm * tm, fsec_t *fsec)
1094 #ifdef HAVE_INT64_TIMESTAMP
1095 tm->tm_hour = time / USECS_PER_HOUR;
1096 time -= tm->tm_hour * USECS_PER_HOUR;
1097 tm->tm_min = time / USECS_PER_MINUTE;
1098 time -= tm->tm_min * USECS_PER_MINUTE;
1099 tm->tm_sec = time / USECS_PER_SEC;
1100 time -= tm->tm_sec * USECS_PER_SEC;
1107 TMODULO(trem, tm->tm_hour, (double) SECS_PER_HOUR);
1108 TMODULO(trem, tm->tm_min, (double) SECS_PER_MINUTE);
1109 TMODULO(trem, tm->tm_sec, 1.0);
1110 trem = TIMEROUND(trem);
1111 /* roundoff may need to propagate to higher-order fields */
1124 time_out(PG_FUNCTION_ARGS)
1126 TimeADT time = PG_GETARG_TIMEADT(0);
1131 char buf[MAXDATELEN + 1];
1133 time2tm(time, tm, &fsec);
1134 EncodeTimeOnly(tm, fsec, NULL, DateStyle, buf);
1136 result = pstrdup(buf);
1137 PG_RETURN_CSTRING(result);
1141 * time_recv - converts external binary format to time
1143 * We make no attempt to provide compatibility between int and float
1144 * time representations ...
1147 time_recv(PG_FUNCTION_ARGS)
1149 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
1152 Oid typelem = PG_GETARG_OID(1);
1154 int32 typmod = PG_GETARG_INT32(2);
1157 #ifdef HAVE_INT64_TIMESTAMP
1158 result = pq_getmsgint64(buf);
1160 if (result < INT64CONST(0) || result > USECS_PER_DAY)
1162 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1163 errmsg("time out of range")));
1165 result = pq_getmsgfloat8(buf);
1167 if (result < 0 || result > (double) SECS_PER_DAY)
1169 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1170 errmsg("time out of range")));
1173 AdjustTimeForTypmod(&result, typmod);
1175 PG_RETURN_TIMEADT(result);
1179 * time_send - converts time to binary format
1182 time_send(PG_FUNCTION_ARGS)
1184 TimeADT time = PG_GETARG_TIMEADT(0);
1187 pq_begintypsend(&buf);
1188 #ifdef HAVE_INT64_TIMESTAMP
1189 pq_sendint64(&buf, time);
1191 pq_sendfloat8(&buf, time);
1193 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
1197 timetypmodin(PG_FUNCTION_ARGS)
1199 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
1201 PG_RETURN_INT32(anytime_typmodin(false, ta));
1205 timetypmodout(PG_FUNCTION_ARGS)
1207 int32 typmod = PG_GETARG_INT32(0);
1209 PG_RETURN_CSTRING(anytime_typmodout(false, typmod));
1214 * Adjust time type for specified scale factor.
1215 * Used by PostgreSQL type system to stuff columns.
1218 time_scale(PG_FUNCTION_ARGS)
1220 TimeADT time = PG_GETARG_TIMEADT(0);
1221 int32 typmod = PG_GETARG_INT32(1);
1225 AdjustTimeForTypmod(&result, typmod);
1227 PG_RETURN_TIMEADT(result);
1230 /* AdjustTimeForTypmod()
1231 * Force the precision of the time value to a specified value.
1232 * Uses *exactly* the same code as in AdjustTimestampForTypemod()
1233 * but we make a separate copy because those types do not
1234 * have a fundamental tie together but rather a coincidence of
1235 * implementation. - thomas
1238 AdjustTimeForTypmod(TimeADT *time, int32 typmod)
1240 #ifdef HAVE_INT64_TIMESTAMP
1241 static const int64 TimeScales[MAX_TIME_PRECISION + 1] = {
1242 INT64CONST(1000000),
1251 static const int64 TimeOffsets[MAX_TIME_PRECISION + 1] = {
1261 /* note MAX_TIME_PRECISION differs in this case */
1262 static const double TimeScales[MAX_TIME_PRECISION + 1] = {
1277 if (typmod >= 0 && typmod <= MAX_TIME_PRECISION)
1280 * Note: this round-to-nearest code is not completely consistent about
1281 * rounding values that are exactly halfway between integral values.
1282 * On most platforms, rint() will implement round-to-nearest-even, but
1283 * the integer code always rounds up (away from zero). Is it worth
1284 * trying to be consistent?
1286 #ifdef HAVE_INT64_TIMESTAMP
1287 if (*time >= INT64CONST(0))
1288 *time = ((*time + TimeOffsets[typmod]) / TimeScales[typmod]) *
1291 *time = -((((-*time) + TimeOffsets[typmod]) / TimeScales[typmod]) *
1292 TimeScales[typmod]);
1294 *time = rint((double) *time * TimeScales[typmod]) / TimeScales[typmod];
1301 time_eq(PG_FUNCTION_ARGS)
1303 TimeADT time1 = PG_GETARG_TIMEADT(0);
1304 TimeADT time2 = PG_GETARG_TIMEADT(1);
1306 PG_RETURN_BOOL(time1 == time2);
1310 time_ne(PG_FUNCTION_ARGS)
1312 TimeADT time1 = PG_GETARG_TIMEADT(0);
1313 TimeADT time2 = PG_GETARG_TIMEADT(1);
1315 PG_RETURN_BOOL(time1 != time2);
1319 time_lt(PG_FUNCTION_ARGS)
1321 TimeADT time1 = PG_GETARG_TIMEADT(0);
1322 TimeADT time2 = PG_GETARG_TIMEADT(1);
1324 PG_RETURN_BOOL(time1 < time2);
1328 time_le(PG_FUNCTION_ARGS)
1330 TimeADT time1 = PG_GETARG_TIMEADT(0);
1331 TimeADT time2 = PG_GETARG_TIMEADT(1);
1333 PG_RETURN_BOOL(time1 <= time2);
1337 time_gt(PG_FUNCTION_ARGS)
1339 TimeADT time1 = PG_GETARG_TIMEADT(0);
1340 TimeADT time2 = PG_GETARG_TIMEADT(1);
1342 PG_RETURN_BOOL(time1 > time2);
1346 time_ge(PG_FUNCTION_ARGS)
1348 TimeADT time1 = PG_GETARG_TIMEADT(0);
1349 TimeADT time2 = PG_GETARG_TIMEADT(1);
1351 PG_RETURN_BOOL(time1 >= time2);
1355 time_cmp(PG_FUNCTION_ARGS)
1357 TimeADT time1 = PG_GETARG_TIMEADT(0);
1358 TimeADT time2 = PG_GETARG_TIMEADT(1);
1361 PG_RETURN_INT32(-1);
1368 time_hash(PG_FUNCTION_ARGS)
1370 /* We can use either hashint8 or hashfloat8 directly */
1371 #ifdef HAVE_INT64_TIMESTAMP
1372 return hashint8(fcinfo);
1374 return hashfloat8(fcinfo);
1379 time_larger(PG_FUNCTION_ARGS)
1381 TimeADT time1 = PG_GETARG_TIMEADT(0);
1382 TimeADT time2 = PG_GETARG_TIMEADT(1);
1384 PG_RETURN_TIMEADT((time1 > time2) ? time1 : time2);
1388 time_smaller(PG_FUNCTION_ARGS)
1390 TimeADT time1 = PG_GETARG_TIMEADT(0);
1391 TimeADT time2 = PG_GETARG_TIMEADT(1);
1393 PG_RETURN_TIMEADT((time1 < time2) ? time1 : time2);
1396 /* overlaps_time() --- implements the SQL92 OVERLAPS operator.
1398 * Algorithm is per SQL92 spec. This is much harder than you'd think
1399 * because the spec requires us to deliver a non-null answer in some cases
1400 * where some of the inputs are null.
1403 overlaps_time(PG_FUNCTION_ARGS)
1406 * The arguments are TimeADT, but we leave them as generic Datums to avoid
1407 * dereferencing nulls (TimeADT is pass-by-reference!)
1409 Datum ts1 = PG_GETARG_DATUM(0);
1410 Datum te1 = PG_GETARG_DATUM(1);
1411 Datum ts2 = PG_GETARG_DATUM(2);
1412 Datum te2 = PG_GETARG_DATUM(3);
1413 bool ts1IsNull = PG_ARGISNULL(0);
1414 bool te1IsNull = PG_ARGISNULL(1);
1415 bool ts2IsNull = PG_ARGISNULL(2);
1416 bool te2IsNull = PG_ARGISNULL(3);
1418 #define TIMEADT_GT(t1,t2) \
1419 (DatumGetTimeADT(t1) > DatumGetTimeADT(t2))
1420 #define TIMEADT_LT(t1,t2) \
1421 (DatumGetTimeADT(t1) < DatumGetTimeADT(t2))
1424 * If both endpoints of interval 1 are null, the result is null (unknown).
1425 * If just one endpoint is null, take ts1 as the non-null one. Otherwise,
1426 * take ts1 as the lesser endpoint.
1432 /* swap null for non-null */
1436 else if (!te1IsNull)
1438 if (TIMEADT_GT(ts1, te1))
1447 /* Likewise for interval 2. */
1452 /* swap null for non-null */
1456 else if (!te2IsNull)
1458 if (TIMEADT_GT(ts2, te2))
1468 * At this point neither ts1 nor ts2 is null, so we can consider three
1469 * cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
1471 if (TIMEADT_GT(ts1, ts2))
1474 * This case is ts1 < te2 OR te1 < te2, which may look redundant but
1475 * in the presence of nulls it's not quite completely so.
1479 if (TIMEADT_LT(ts1, te2))
1480 PG_RETURN_BOOL(true);
1485 * If te1 is not null then we had ts1 <= te1 above, and we just found
1486 * ts1 >= te2, hence te1 >= te2.
1488 PG_RETURN_BOOL(false);
1490 else if (TIMEADT_LT(ts1, ts2))
1492 /* This case is ts2 < te1 OR te2 < te1 */
1495 if (TIMEADT_LT(ts2, te1))
1496 PG_RETURN_BOOL(true);
1501 * If te2 is not null then we had ts2 <= te2 above, and we just found
1502 * ts2 >= te1, hence te2 >= te1.
1504 PG_RETURN_BOOL(false);
1509 * For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
1510 * rather silly way of saying "true if both are nonnull, else null".
1512 if (te1IsNull || te2IsNull)
1514 PG_RETURN_BOOL(true);
1522 * Convert timestamp to time data type.
1525 timestamp_time(PG_FUNCTION_ARGS)
1527 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
1533 if (TIMESTAMP_NOT_FINITE(timestamp))
1536 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
1538 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1539 errmsg("timestamp out of range")));
1541 #ifdef HAVE_INT64_TIMESTAMP
1544 * Could also do this with time = (timestamp / USECS_PER_DAY *
1545 * USECS_PER_DAY) - timestamp;
1547 result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
1548 USECS_PER_SEC) + fsec;
1550 result = ((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec + fsec;
1553 PG_RETURN_TIMEADT(result);
1556 /* timestamptz_time()
1557 * Convert timestamptz to time data type.
1560 timestamptz_time(PG_FUNCTION_ARGS)
1562 TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
1570 if (TIMESTAMP_NOT_FINITE(timestamp))
1573 if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) != 0)
1575 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1576 errmsg("timestamp out of range")));
1578 #ifdef HAVE_INT64_TIMESTAMP
1581 * Could also do this with time = (timestamp / USECS_PER_DAY *
1582 * USECS_PER_DAY) - timestamp;
1584 result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
1585 USECS_PER_SEC) + fsec;
1587 result = ((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec + fsec;
1590 PG_RETURN_TIMEADT(result);
1593 /* datetime_timestamp()
1594 * Convert date and time to timestamp data type.
1597 datetime_timestamp(PG_FUNCTION_ARGS)
1599 DateADT date = PG_GETARG_DATEADT(0);
1600 TimeADT time = PG_GETARG_TIMEADT(1);
1603 result = date2timestamp(date);
1604 if (!TIMESTAMP_NOT_FINITE(result))
1607 PG_RETURN_TIMESTAMP(result);
1611 * Convert time to interval data type.
1614 time_interval(PG_FUNCTION_ARGS)
1616 TimeADT time = PG_GETARG_TIMEADT(0);
1619 result = (Interval *) palloc(sizeof(Interval));
1621 result->time = time;
1625 PG_RETURN_INTERVAL_P(result);
1629 * Convert interval to time data type.
1631 * This is defined as producing the fractional-day portion of the interval.
1632 * Therefore, we can just ignore the months field. It is not real clear
1633 * what to do with negative intervals, but we choose to subtract the floor,
1634 * so that, say, '-2 hours' becomes '22:00:00'.
1637 interval_time(PG_FUNCTION_ARGS)
1639 Interval *span = PG_GETARG_INTERVAL_P(0);
1642 #ifdef HAVE_INT64_TIMESTAMP
1645 result = span->time;
1646 if (result >= USECS_PER_DAY)
1648 days = result / USECS_PER_DAY;
1649 result -= days * USECS_PER_DAY;
1651 else if (result < 0)
1653 days = (-result + USECS_PER_DAY - 1) / USECS_PER_DAY;
1654 result += days * USECS_PER_DAY;
1657 result = span->time;
1658 if (result >= (double) SECS_PER_DAY || result < 0)
1659 result -= floor(result / (double) SECS_PER_DAY) * (double) SECS_PER_DAY;
1662 PG_RETURN_TIMEADT(result);
1666 * Subtract two times to produce an interval.
1669 time_mi_time(PG_FUNCTION_ARGS)
1671 TimeADT time1 = PG_GETARG_TIMEADT(0);
1672 TimeADT time2 = PG_GETARG_TIMEADT(1);
1675 result = (Interval *) palloc(sizeof(Interval));
1679 result->time = time1 - time2;
1681 PG_RETURN_INTERVAL_P(result);
1684 /* time_pl_interval()
1685 * Add interval to time.
1688 time_pl_interval(PG_FUNCTION_ARGS)
1690 TimeADT time = PG_GETARG_TIMEADT(0);
1691 Interval *span = PG_GETARG_INTERVAL_P(1);
1694 #ifdef HAVE_INT64_TIMESTAMP
1695 result = time + span->time;
1696 result -= result / USECS_PER_DAY * USECS_PER_DAY;
1697 if (result < INT64CONST(0))
1698 result += USECS_PER_DAY;
1702 result = time + span->time;
1703 TMODULO(result, time1, (double) SECS_PER_DAY);
1705 result += SECS_PER_DAY;
1708 PG_RETURN_TIMEADT(result);
1711 /* time_mi_interval()
1712 * Subtract interval from time.
1715 time_mi_interval(PG_FUNCTION_ARGS)
1717 TimeADT time = PG_GETARG_TIMEADT(0);
1718 Interval *span = PG_GETARG_INTERVAL_P(1);
1721 #ifdef HAVE_INT64_TIMESTAMP
1722 result = time - span->time;
1723 result -= result / USECS_PER_DAY * USECS_PER_DAY;
1724 if (result < INT64CONST(0))
1725 result += USECS_PER_DAY;
1729 result = time - span->time;
1730 TMODULO(result, time1, (double) SECS_PER_DAY);
1732 result += SECS_PER_DAY;
1735 PG_RETURN_TIMEADT(result);
1740 * Extract specified field from time type.
1743 time_part(PG_FUNCTION_ARGS)
1745 text *units = PG_GETARG_TEXT_PP(0);
1746 TimeADT time = PG_GETARG_TIMEADT(1);
1752 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
1753 VARSIZE_ANY_EXHDR(units),
1756 type = DecodeUnits(0, lowunits, &val);
1757 if (type == UNKNOWN_FIELD)
1758 type = DecodeSpecial(0, lowunits, &val);
1766 time2tm(time, tm, &fsec);
1771 #ifdef HAVE_INT64_TIMESTAMP
1772 result = tm->tm_sec * 1000000.0 + fsec;
1774 result = (tm->tm_sec + fsec) * 1000000;
1779 #ifdef HAVE_INT64_TIMESTAMP
1780 result = tm->tm_sec * 1000.0 + fsec / 1000.0;
1782 result = (tm->tm_sec + fsec) * 1000;
1787 #ifdef HAVE_INT64_TIMESTAMP
1788 result = tm->tm_sec + fsec / 1000000.0;
1790 result = tm->tm_sec + fsec;
1795 result = tm->tm_min;
1799 result = tm->tm_hour;
1811 case DTK_MILLENNIUM:
1815 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1816 errmsg("\"time\" units \"%s\" not recognized",
1821 else if (type == RESERV && val == DTK_EPOCH)
1823 #ifdef HAVE_INT64_TIMESTAMP
1824 result = time / 1000000.0;
1832 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1833 errmsg("\"time\" units \"%s\" not recognized",
1838 PG_RETURN_FLOAT8(result);
1842 /*****************************************************************************
1843 * Time With Time Zone ADT
1844 *****************************************************************************/
1847 * Convert a tm structure to a time data type.
1850 tm2timetz(struct pg_tm * tm, fsec_t fsec, int tz, TimeTzADT *result)
1852 #ifdef HAVE_INT64_TIMESTAMP
1853 result->time = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
1854 USECS_PER_SEC) + fsec;
1856 result->time = ((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec + fsec;
1864 timetz_in(PG_FUNCTION_ARGS)
1866 char *str = PG_GETARG_CSTRING(0);
1869 Oid typelem = PG_GETARG_OID(1);
1871 int32 typmod = PG_GETARG_INT32(2);
1879 char workbuf[MAXDATELEN + 1];
1880 char *field[MAXDATEFIELDS];
1882 int ftype[MAXDATEFIELDS];
1884 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
1885 field, ftype, MAXDATEFIELDS, &nf);
1887 dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz);
1889 DateTimeParseError(dterr, str, "time with time zone");
1891 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
1892 tm2timetz(tm, fsec, tz, result);
1893 AdjustTimeForTypmod(&(result->time), typmod);
1895 PG_RETURN_TIMETZADT_P(result);
1899 timetz_out(PG_FUNCTION_ARGS)
1901 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
1907 char buf[MAXDATELEN + 1];
1909 timetz2tm(time, tm, &fsec, &tz);
1910 EncodeTimeOnly(tm, fsec, &tz, DateStyle, buf);
1912 result = pstrdup(buf);
1913 PG_RETURN_CSTRING(result);
1917 * timetz_recv - converts external binary format to timetz
1920 timetz_recv(PG_FUNCTION_ARGS)
1922 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
1925 Oid typelem = PG_GETARG_OID(1);
1927 int32 typmod = PG_GETARG_INT32(2);
1930 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
1932 #ifdef HAVE_INT64_TIMESTAMP
1933 result->time = pq_getmsgint64(buf);
1935 if (result->time < INT64CONST(0) || result->time > USECS_PER_DAY)
1937 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1938 errmsg("time out of range")));
1940 result->time = pq_getmsgfloat8(buf);
1942 if (result->time < 0 || result->time > (double) SECS_PER_DAY)
1944 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1945 errmsg("time out of range")));
1948 result->zone = pq_getmsgint(buf, sizeof(result->zone));
1950 /* we allow GMT displacements up to 14:59:59, cf DecodeTimezone() */
1951 if (result->zone <= -15 * SECS_PER_HOUR ||
1952 result->zone >= 15 * SECS_PER_HOUR)
1954 (errcode(ERRCODE_INVALID_TIME_ZONE_DISPLACEMENT_VALUE),
1955 errmsg("time zone displacement out of range")));
1957 AdjustTimeForTypmod(&(result->time), typmod);
1959 PG_RETURN_TIMETZADT_P(result);
1963 * timetz_send - converts timetz to binary format
1966 timetz_send(PG_FUNCTION_ARGS)
1968 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
1971 pq_begintypsend(&buf);
1972 #ifdef HAVE_INT64_TIMESTAMP
1973 pq_sendint64(&buf, time->time);
1975 pq_sendfloat8(&buf, time->time);
1977 pq_sendint(&buf, time->zone, sizeof(time->zone));
1978 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
1982 timetztypmodin(PG_FUNCTION_ARGS)
1984 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
1986 PG_RETURN_INT32(anytime_typmodin(true, ta));
1990 timetztypmodout(PG_FUNCTION_ARGS)
1992 int32 typmod = PG_GETARG_INT32(0);
1994 PG_RETURN_CSTRING(anytime_typmodout(true, typmod));
1999 * Convert TIME WITH TIME ZONE data type to POSIX time structure.
2002 timetz2tm(TimeTzADT *time, struct pg_tm * tm, fsec_t *fsec, int *tzp)
2004 TimeOffset trem = time->time;
2006 #ifdef HAVE_INT64_TIMESTAMP
2007 tm->tm_hour = trem / USECS_PER_HOUR;
2008 trem -= tm->tm_hour * USECS_PER_HOUR;
2009 tm->tm_min = trem / USECS_PER_MINUTE;
2010 trem -= tm->tm_min * USECS_PER_MINUTE;
2011 tm->tm_sec = trem / USECS_PER_SEC;
2012 *fsec = trem - tm->tm_sec * USECS_PER_SEC;
2015 TMODULO(trem, tm->tm_hour, (double) SECS_PER_HOUR);
2016 TMODULO(trem, tm->tm_min, (double) SECS_PER_MINUTE);
2017 TMODULO(trem, tm->tm_sec, 1.0);
2018 trem = TIMEROUND(trem);
2019 /* roundoff may need to propagate to higher-order fields */
2022 trem = ceil(time->time);
2035 * Adjust time type for specified scale factor.
2036 * Used by PostgreSQL type system to stuff columns.
2039 timetz_scale(PG_FUNCTION_ARGS)
2041 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
2042 int32 typmod = PG_GETARG_INT32(1);
2045 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2047 result->time = time->time;
2048 result->zone = time->zone;
2050 AdjustTimeForTypmod(&(result->time), typmod);
2052 PG_RETURN_TIMETZADT_P(result);
2057 timetz_cmp_internal(TimeTzADT *time1, TimeTzADT *time2)
2062 /* Primary sort is by true (GMT-equivalent) time */
2063 #ifdef HAVE_INT64_TIMESTAMP
2064 t1 = time1->time + (time1->zone * USECS_PER_SEC);
2065 t2 = time2->time + (time2->zone * USECS_PER_SEC);
2067 t1 = time1->time + time1->zone;
2068 t2 = time2->time + time2->zone;
2077 * If same GMT time, sort by timezone; we only want to say that two
2078 * timetz's are equal if both the time and zone parts are equal.
2080 if (time1->zone > time2->zone)
2082 if (time1->zone < time2->zone)
2089 timetz_eq(PG_FUNCTION_ARGS)
2091 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2092 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2094 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) == 0);
2098 timetz_ne(PG_FUNCTION_ARGS)
2100 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2101 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2103 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) != 0);
2107 timetz_lt(PG_FUNCTION_ARGS)
2109 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2110 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2112 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) < 0);
2116 timetz_le(PG_FUNCTION_ARGS)
2118 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2119 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2121 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) <= 0);
2125 timetz_gt(PG_FUNCTION_ARGS)
2127 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2128 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2130 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) > 0);
2134 timetz_ge(PG_FUNCTION_ARGS)
2136 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2137 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2139 PG_RETURN_BOOL(timetz_cmp_internal(time1, time2) >= 0);
2143 timetz_cmp(PG_FUNCTION_ARGS)
2145 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2146 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2148 PG_RETURN_INT32(timetz_cmp_internal(time1, time2));
2152 timetz_hash(PG_FUNCTION_ARGS)
2154 TimeTzADT *key = PG_GETARG_TIMETZADT_P(0);
2158 * To avoid any problems with padding bytes in the struct, we figure the
2159 * field hashes separately and XOR them. This also provides a convenient
2160 * framework for dealing with the fact that the time field might be either
2163 #ifdef HAVE_INT64_TIMESTAMP
2164 thash = DatumGetUInt32(DirectFunctionCall1(hashint8,
2165 Int64GetDatumFast(key->time)));
2167 thash = DatumGetUInt32(DirectFunctionCall1(hashfloat8,
2168 Float8GetDatumFast(key->time)));
2170 thash ^= DatumGetUInt32(hash_uint32(key->zone));
2171 PG_RETURN_UINT32(thash);
2175 timetz_larger(PG_FUNCTION_ARGS)
2177 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2178 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2181 if (timetz_cmp_internal(time1, time2) > 0)
2185 PG_RETURN_TIMETZADT_P(result);
2189 timetz_smaller(PG_FUNCTION_ARGS)
2191 TimeTzADT *time1 = PG_GETARG_TIMETZADT_P(0);
2192 TimeTzADT *time2 = PG_GETARG_TIMETZADT_P(1);
2195 if (timetz_cmp_internal(time1, time2) < 0)
2199 PG_RETURN_TIMETZADT_P(result);
2202 /* timetz_pl_interval()
2203 * Add interval to timetz.
2206 timetz_pl_interval(PG_FUNCTION_ARGS)
2208 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
2209 Interval *span = PG_GETARG_INTERVAL_P(1);
2212 #ifndef HAVE_INT64_TIMESTAMP
2216 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2218 #ifdef HAVE_INT64_TIMESTAMP
2219 result->time = time->time + span->time;
2220 result->time -= result->time / USECS_PER_DAY * USECS_PER_DAY;
2221 if (result->time < INT64CONST(0))
2222 result->time += USECS_PER_DAY;
2224 result->time = time->time + span->time;
2225 TMODULO(result->time, time1.time, (double) SECS_PER_DAY);
2226 if (result->time < 0)
2227 result->time += SECS_PER_DAY;
2230 result->zone = time->zone;
2232 PG_RETURN_TIMETZADT_P(result);
2235 /* timetz_mi_interval()
2236 * Subtract interval from timetz.
2239 timetz_mi_interval(PG_FUNCTION_ARGS)
2241 TimeTzADT *time = PG_GETARG_TIMETZADT_P(0);
2242 Interval *span = PG_GETARG_INTERVAL_P(1);
2245 #ifndef HAVE_INT64_TIMESTAMP
2249 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2251 #ifdef HAVE_INT64_TIMESTAMP
2252 result->time = time->time - span->time;
2253 result->time -= result->time / USECS_PER_DAY * USECS_PER_DAY;
2254 if (result->time < INT64CONST(0))
2255 result->time += USECS_PER_DAY;
2257 result->time = time->time - span->time;
2258 TMODULO(result->time, time1.time, (double) SECS_PER_DAY);
2259 if (result->time < 0)
2260 result->time += SECS_PER_DAY;
2263 result->zone = time->zone;
2265 PG_RETURN_TIMETZADT_P(result);
2268 /* overlaps_timetz() --- implements the SQL92 OVERLAPS operator.
2270 * Algorithm is per SQL92 spec. This is much harder than you'd think
2271 * because the spec requires us to deliver a non-null answer in some cases
2272 * where some of the inputs are null.
2275 overlaps_timetz(PG_FUNCTION_ARGS)
2278 * The arguments are TimeTzADT *, but we leave them as generic Datums for
2279 * convenience of notation --- and to avoid dereferencing nulls.
2281 Datum ts1 = PG_GETARG_DATUM(0);
2282 Datum te1 = PG_GETARG_DATUM(1);
2283 Datum ts2 = PG_GETARG_DATUM(2);
2284 Datum te2 = PG_GETARG_DATUM(3);
2285 bool ts1IsNull = PG_ARGISNULL(0);
2286 bool te1IsNull = PG_ARGISNULL(1);
2287 bool ts2IsNull = PG_ARGISNULL(2);
2288 bool te2IsNull = PG_ARGISNULL(3);
2290 #define TIMETZ_GT(t1,t2) \
2291 DatumGetBool(DirectFunctionCall2(timetz_gt,t1,t2))
2292 #define TIMETZ_LT(t1,t2) \
2293 DatumGetBool(DirectFunctionCall2(timetz_lt,t1,t2))
2296 * If both endpoints of interval 1 are null, the result is null (unknown).
2297 * If just one endpoint is null, take ts1 as the non-null one. Otherwise,
2298 * take ts1 as the lesser endpoint.
2304 /* swap null for non-null */
2308 else if (!te1IsNull)
2310 if (TIMETZ_GT(ts1, te1))
2319 /* Likewise for interval 2. */
2324 /* swap null for non-null */
2328 else if (!te2IsNull)
2330 if (TIMETZ_GT(ts2, te2))
2340 * At this point neither ts1 nor ts2 is null, so we can consider three
2341 * cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
2343 if (TIMETZ_GT(ts1, ts2))
2346 * This case is ts1 < te2 OR te1 < te2, which may look redundant but
2347 * in the presence of nulls it's not quite completely so.
2351 if (TIMETZ_LT(ts1, te2))
2352 PG_RETURN_BOOL(true);
2357 * If te1 is not null then we had ts1 <= te1 above, and we just found
2358 * ts1 >= te2, hence te1 >= te2.
2360 PG_RETURN_BOOL(false);
2362 else if (TIMETZ_LT(ts1, ts2))
2364 /* This case is ts2 < te1 OR te2 < te1 */
2367 if (TIMETZ_LT(ts2, te1))
2368 PG_RETURN_BOOL(true);
2373 * If te2 is not null then we had ts2 <= te2 above, and we just found
2374 * ts2 >= te1, hence te2 >= te1.
2376 PG_RETURN_BOOL(false);
2381 * For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
2382 * rather silly way of saying "true if both are nonnull, else null".
2384 if (te1IsNull || te2IsNull)
2386 PG_RETURN_BOOL(true);
2395 timetz_time(PG_FUNCTION_ARGS)
2397 TimeTzADT *timetz = PG_GETARG_TIMETZADT_P(0);
2400 /* swallow the time zone and just return the time */
2401 result = timetz->time;
2403 PG_RETURN_TIMEADT(result);
2408 time_timetz(PG_FUNCTION_ARGS)
2410 TimeADT time = PG_GETARG_TIMEADT(0);
2417 GetCurrentDateTime(tm);
2418 time2tm(time, tm, &fsec);
2419 tz = DetermineTimeZoneOffset(tm, session_timezone);
2421 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2423 result->time = time;
2426 PG_RETURN_TIMETZADT_P(result);
2430 /* timestamptz_timetz()
2431 * Convert timestamp to timetz data type.
2434 timestamptz_timetz(PG_FUNCTION_ARGS)
2436 TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
2444 if (TIMESTAMP_NOT_FINITE(timestamp))
2447 if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) != 0)
2449 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2450 errmsg("timestamp out of range")));
2452 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2454 tm2timetz(tm, fsec, tz, result);
2456 PG_RETURN_TIMETZADT_P(result);
2460 /* datetimetz_timestamptz()
2461 * Convert date and timetz to timestamp with time zone data type.
2462 * Timestamp is stored in GMT, so add the time zone
2463 * stored with the timetz to the result.
2464 * - thomas 2000-03-10
2467 datetimetz_timestamptz(PG_FUNCTION_ARGS)
2469 DateADT date = PG_GETARG_DATEADT(0);
2470 TimeTzADT *time = PG_GETARG_TIMETZADT_P(1);
2473 if (DATE_IS_NOBEGIN(date))
2474 TIMESTAMP_NOBEGIN(result);
2475 else if (DATE_IS_NOEND(date))
2476 TIMESTAMP_NOEND(result);
2479 #ifdef HAVE_INT64_TIMESTAMP
2480 result = date * USECS_PER_DAY + time->time + time->zone * USECS_PER_SEC;
2482 result = date * (double) SECS_PER_DAY + time->time + time->zone;
2486 PG_RETURN_TIMESTAMP(result);
2491 * Extract specified field from time type.
2494 timetz_part(PG_FUNCTION_ARGS)
2496 text *units = PG_GETARG_TEXT_PP(0);
2497 TimeTzADT *time = PG_GETARG_TIMETZADT_P(1);
2503 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
2504 VARSIZE_ANY_EXHDR(units),
2507 type = DecodeUnits(0, lowunits, &val);
2508 if (type == UNKNOWN_FIELD)
2509 type = DecodeSpecial(0, lowunits, &val);
2519 timetz2tm(time, tm, &fsec, &tz);
2529 result /= SECS_PER_MINUTE;
2530 FMODULO(result, dummy, (double) SECS_PER_MINUTE);
2535 FMODULO(dummy, result, (double) SECS_PER_HOUR);
2539 #ifdef HAVE_INT64_TIMESTAMP
2540 result = tm->tm_sec * 1000000.0 + fsec;
2542 result = (tm->tm_sec + fsec) * 1000000;
2547 #ifdef HAVE_INT64_TIMESTAMP
2548 result = tm->tm_sec * 1000.0 + fsec / 1000.0;
2550 result = (tm->tm_sec + fsec) * 1000;
2555 #ifdef HAVE_INT64_TIMESTAMP
2556 result = tm->tm_sec + fsec / 1000000.0;
2558 result = tm->tm_sec + fsec;
2563 result = tm->tm_min;
2567 result = tm->tm_hour;
2576 case DTK_MILLENNIUM:
2579 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2580 errmsg("\"time with time zone\" units \"%s\" not recognized",
2585 else if (type == RESERV && val == DTK_EPOCH)
2587 #ifdef HAVE_INT64_TIMESTAMP
2588 result = time->time / 1000000.0 + time->zone;
2590 result = time->time + time->zone;
2596 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2597 errmsg("\"time with time zone\" units \"%s\" not recognized",
2602 PG_RETURN_FLOAT8(result);
2606 * Encode time with time zone type with specified time zone.
2607 * Applies DST rules as of the current date.
2610 timetz_zone(PG_FUNCTION_ARGS)
2612 text *zone = PG_GETARG_TEXT_PP(0);
2613 TimeTzADT *t = PG_GETARG_TIMETZADT_P(1);
2616 char tzname[TZ_STRLEN_MAX + 1];
2623 * Look up the requested timezone. First we look in the date token table
2624 * (to handle cases like "EST"), and if that fails, we look in the
2625 * timezone database (to handle cases like "America/New_York"). (This
2626 * matches the order in which timestamp input checks the cases; it's
2627 * important because the timezone database unwisely uses a few zone names
2628 * that are identical to offset abbreviations.)
2630 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
2631 lowzone = downcase_truncate_identifier(tzname,
2635 type = DecodeSpecial(0, lowzone, &val);
2637 if (type == TZ || type == DTZ)
2638 tz = val * MINS_PER_HOUR;
2641 tzp = pg_tzset(tzname);
2644 /* Get the offset-from-GMT that is valid today for the zone */
2645 pg_time_t now = (pg_time_t) time(NULL);
2648 tm = pg_localtime(&now, tzp);
2649 tz = -tm->tm_gmtoff;
2654 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2655 errmsg("time zone \"%s\" not recognized", tzname)));
2656 tz = 0; /* keep compiler quiet */
2660 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2662 #ifdef HAVE_INT64_TIMESTAMP
2663 result->time = t->time + (t->zone - tz) * USECS_PER_SEC;
2664 while (result->time < INT64CONST(0))
2665 result->time += USECS_PER_DAY;
2666 while (result->time >= USECS_PER_DAY)
2667 result->time -= USECS_PER_DAY;
2669 result->time = t->time + (t->zone - tz);
2670 while (result->time < 0)
2671 result->time += SECS_PER_DAY;
2672 while (result->time >= SECS_PER_DAY)
2673 result->time -= SECS_PER_DAY;
2678 PG_RETURN_TIMETZADT_P(result);
2682 * Encode time with time zone type with specified time interval as time zone.
2685 timetz_izone(PG_FUNCTION_ARGS)
2687 Interval *zone = PG_GETARG_INTERVAL_P(0);
2688 TimeTzADT *time = PG_GETARG_TIMETZADT_P(1);
2692 if (zone->month != 0)
2694 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2695 errmsg("\"interval\" time zone \"%s\" not valid",
2696 DatumGetCString(DirectFunctionCall1(interval_out,
2697 PointerGetDatum(zone))))));
2699 #ifdef HAVE_INT64_TIMESTAMP
2700 tz = -(zone->time / USECS_PER_SEC);
2705 result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
2707 #ifdef HAVE_INT64_TIMESTAMP
2708 result->time = time->time + (time->zone - tz) * USECS_PER_SEC;
2709 while (result->time < INT64CONST(0))
2710 result->time += USECS_PER_DAY;
2711 while (result->time >= USECS_PER_DAY)
2712 result->time -= USECS_PER_DAY;
2714 result->time = time->time + (time->zone - tz);
2715 while (result->time < 0)
2716 result->time += SECS_PER_DAY;
2717 while (result->time >= SECS_PER_DAY)
2718 result->time -= SECS_PER_DAY;
2723 PG_RETURN_TIMETZADT_P(result);