1 /*-------------------------------------------------------------------------
4 * Utilities for the built-in type "AbsoluteTime".
5 * Functions for the built-in type "RelativeTime".
6 * Functions for the built-in type "TimeInterval".
8 * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
9 * Portions Copyright (c) 1994, Regents of the University of California
13 * $PostgreSQL: pgsql/src/backend/utils/adt/nabstime.c,v 1.165 2010/08/03 16:31:02 tgl Exp $
15 *-------------------------------------------------------------------------
25 #include "libpq/pqformat.h"
26 #include "miscadmin.h"
27 #include "utils/builtins.h"
28 #include "utils/nabstime.h"
30 #define MIN_DAYNUM (-24856) /* December 13, 1901 */
31 #define MAX_DAYNUM 24854 /* January 18, 2038 */
34 * Unix epoch is Jan 1 00:00:00 1970.
35 * Postgres knows about times sixty-eight years on either side of that
36 * for these 4-byte types.
38 * "tinterval" is two 4-byte fields.
39 * Definitions for parsing tinterval.
42 #define IsSpace(C) ((C) == ' ')
44 #define T_INTERVAL_INVAL 0 /* data represents no valid tinterval */
45 #define T_INTERVAL_VALID 1 /* data represents a valid tinterval */
47 * ['Mon May 10 23:59:12 1943 PST' 'Sun Jan 14 03:14:21 1973 PST']
49 * 1234567890123456789012345678901234567890123456789012345678901234
51 * we allocate some extra -- timezones are usually 3 characters but
52 * this is not in the POSIX standard...
54 #define T_INTERVAL_LEN 80
55 #define INVALID_INTERVAL_STR "Undefined Range"
56 #define INVALID_INTERVAL_STR_LEN (sizeof(INVALID_INTERVAL_STR)-1)
58 #define ABSTIMEMIN(t1, t2) \
59 (DatumGetBool(DirectFunctionCall2(abstimele, \
60 AbsoluteTimeGetDatum(t1), \
61 AbsoluteTimeGetDatum(t2))) ? (t1) : (t2))
62 #define ABSTIMEMAX(t1, t2) \
63 (DatumGetBool(DirectFunctionCall2(abstimelt, \
64 AbsoluteTimeGetDatum(t1), \
65 AbsoluteTimeGetDatum(t2))) ? (t2) : (t1))
69 * Function prototypes -- internal to this file only
72 static AbsoluteTime tm2abstime(struct pg_tm * tm, int tz);
73 static void reltime2tm(RelativeTime time, struct pg_tm * tm);
74 static void parsetinterval(char *i_string,
75 AbsoluteTime *i_start,
80 * GetCurrentAbsoluteTime()
82 * Get the current system time (relative to Unix epoch).
84 * NB: this will overflow in 2038; it should be gone long before that.
87 GetCurrentAbsoluteTime(void)
92 return (AbsoluteTime) now;
97 abstime2tm(AbsoluteTime _time, int *tzp, struct pg_tm * tm, char **tzn)
99 pg_time_t time = (pg_time_t) _time;
103 * If HasCTZSet is true then we have a brute force time zone specified. Go
104 * ahead and rotate to the local time zone since we will later bypass any
105 * calls which adjust the tm fields.
107 if (HasCTZSet && (tzp != NULL))
110 if (!HasCTZSet && tzp != NULL)
111 tx = pg_localtime(&time, session_timezone);
113 tx = pg_gmtime(&time);
115 tm->tm_year = tx->tm_year + 1900;
116 tm->tm_mon = tx->tm_mon + 1;
117 tm->tm_mday = tx->tm_mday;
118 tm->tm_hour = tx->tm_hour;
119 tm->tm_min = tx->tm_min;
120 tm->tm_sec = tx->tm_sec;
121 tm->tm_isdst = tx->tm_isdst;
123 tm->tm_gmtoff = tx->tm_gmtoff;
124 tm->tm_zone = tx->tm_zone;
129 * We have a brute force time zone per SQL99? Then use it without
130 * change since we have already rotated to the time zone.
135 tm->tm_gmtoff = CTimeZone;
143 *tzp = -tm->tm_gmtoff; /* tm_gmtoff is Sun/DEC-ism */
146 * XXX FreeBSD man pages indicate that this should work - tgl
152 * Copy no more than MAXTZLEN bytes of timezone to tzn, in
153 * case it contains an error message, which doesn't fit in the
156 StrNCpy(*tzn, tm->tm_zone, MAXTZLEN + 1);
157 if (strlen(tm->tm_zone) > MAXTZLEN)
159 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
160 errmsg("invalid time zone name: \"%s\"",
171 * Convert a tm structure to abstime.
172 * Note that tm has full year (not 1900-based) and 1-based month.
175 tm2abstime(struct pg_tm * tm, int tz)
180 /* validate, before going out of range on some members */
181 if (tm->tm_year < 1901 || tm->tm_year > 2038 ||
182 tm->tm_mon < 1 || tm->tm_mon > 12 ||
183 tm->tm_mday < 1 || tm->tm_mday > 31 ||
185 tm->tm_hour > 24 || /* test for > 24:00:00 */
186 (tm->tm_hour == 24 && (tm->tm_min > 0 || tm->tm_sec > 0)) ||
187 tm->tm_min < 0 || tm->tm_min > 59 ||
188 tm->tm_sec < 0 || tm->tm_sec > 60)
189 return INVALID_ABSTIME;
191 day = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - UNIX_EPOCH_JDATE;
193 /* check for time out of range */
194 if (day < MIN_DAYNUM || day > MAX_DAYNUM)
195 return INVALID_ABSTIME;
197 /* convert to seconds */
198 sec = tm->tm_sec + tz + (tm->tm_min + (day * HOURS_PER_DAY + tm->tm_hour) * MINS_PER_HOUR) * SECS_PER_MINUTE;
201 * check for overflow. We need a little slop here because the H/M/S plus
202 * TZ offset could add up to more than 1 day.
204 if ((day >= MAX_DAYNUM - 10 && sec < 0) ||
205 (day <= MIN_DAYNUM + 10 && sec > 0))
206 return INVALID_ABSTIME;
208 /* check for reserved values (e.g. "current" on edge of usual range */
209 if (!AbsoluteTimeIsReal(sec))
210 return INVALID_ABSTIME;
217 * Decode date/time string and return abstime.
220 abstimein(PG_FUNCTION_ARGS)
222 char *str = PG_GETARG_CSTRING(0);
229 char *field[MAXDATEFIELDS];
230 char workbuf[MAXDATELEN + 1];
233 ftype[MAXDATEFIELDS];
235 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
236 field, ftype, MAXDATEFIELDS, &nf);
238 dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
240 DateTimeParseError(dterr, str, "abstime");
245 result = tm2abstime(tm, tz);
251 * Don't bother retaining this as a reserved value, but instead
252 * just set to the actual epoch time (1970-01-01)
258 result = NOEND_ABSTIME;
262 result = NOSTART_ABSTIME;
266 result = INVALID_ABSTIME;
270 elog(ERROR, "unexpected dtype %d while parsing abstime \"%s\"",
272 result = INVALID_ABSTIME;
276 PG_RETURN_ABSOLUTETIME(result);
281 * Given an AbsoluteTime return the English text version of the date
284 abstimeout(PG_FUNCTION_ARGS)
286 AbsoluteTime time = PG_GETARG_ABSOLUTETIME(0);
292 char buf[MAXDATELEN + 1];
293 char zone[MAXDATELEN + 1],
299 * Note that timestamp no longer supports 'invalid'. Retain
300 * 'invalid' for abstime for now, but dump it someday.
302 case INVALID_ABSTIME:
303 strcpy(buf, INVALID);
308 case NOSTART_ABSTIME:
312 abstime2tm(time, &tz, tm, &tzn);
313 EncodeDateTime(tm, fsec, &tz, &tzn, DateStyle, buf);
317 result = pstrdup(buf);
318 PG_RETURN_CSTRING(result);
322 * abstimerecv - converts external binary format to abstime
325 abstimerecv(PG_FUNCTION_ARGS)
327 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
329 PG_RETURN_ABSOLUTETIME((AbsoluteTime) pq_getmsgint(buf, sizeof(AbsoluteTime)));
333 * abstimesend - converts abstime to binary format
336 abstimesend(PG_FUNCTION_ARGS)
338 AbsoluteTime time = PG_GETARG_ABSOLUTETIME(0);
341 pq_begintypsend(&buf);
342 pq_sendint(&buf, time, sizeof(time));
343 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
350 abstime_finite(PG_FUNCTION_ARGS)
352 AbsoluteTime abstime = PG_GETARG_ABSOLUTETIME(0);
354 PG_RETURN_BOOL(abstime != INVALID_ABSTIME &&
355 abstime != NOSTART_ABSTIME &&
356 abstime != NOEND_ABSTIME);
361 * abstime comparison routines
364 abstime_cmp_internal(AbsoluteTime a, AbsoluteTime b)
367 * We consider all INVALIDs to be equal and larger than any non-INVALID.
368 * This is somewhat arbitrary; the important thing is to have a consistent
371 if (a == INVALID_ABSTIME)
373 if (b == INVALID_ABSTIME)
374 return 0; /* INVALID = INVALID */
376 return 1; /* INVALID > non-INVALID */
379 if (b == INVALID_ABSTIME)
380 return -1; /* non-INVALID < INVALID */
391 abstimeeq(PG_FUNCTION_ARGS)
393 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
394 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
396 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) == 0);
400 abstimene(PG_FUNCTION_ARGS)
402 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
403 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
405 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) != 0);
409 abstimelt(PG_FUNCTION_ARGS)
411 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
412 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
414 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) < 0);
418 abstimegt(PG_FUNCTION_ARGS)
420 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
421 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
423 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) > 0);
427 abstimele(PG_FUNCTION_ARGS)
429 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
430 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
432 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) <= 0);
436 abstimege(PG_FUNCTION_ARGS)
438 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
439 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
441 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) >= 0);
445 btabstimecmp(PG_FUNCTION_ARGS)
447 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
448 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
450 PG_RETURN_INT32(abstime_cmp_internal(t1, t2));
454 /* timestamp_abstime()
455 * Convert timestamp to abstime.
458 timestamp_abstime(PG_FUNCTION_ARGS)
460 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
467 if (TIMESTAMP_IS_NOBEGIN(timestamp))
468 result = NOSTART_ABSTIME;
469 else if (TIMESTAMP_IS_NOEND(timestamp))
470 result = NOEND_ABSTIME;
471 else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
473 tz = DetermineTimeZoneOffset(tm, session_timezone);
474 result = tm2abstime(tm, tz);
479 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
480 errmsg("timestamp out of range")));
481 result = INVALID_ABSTIME;
484 PG_RETURN_ABSOLUTETIME(result);
487 /* abstime_timestamp()
488 * Convert abstime to timestamp.
491 abstime_timestamp(PG_FUNCTION_ARGS)
493 AbsoluteTime abstime = PG_GETARG_ABSOLUTETIME(0);
498 char zone[MAXDATELEN + 1],
503 case INVALID_ABSTIME:
505 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
506 errmsg("cannot convert abstime \"invalid\" to timestamp")));
507 TIMESTAMP_NOBEGIN(result);
510 case NOSTART_ABSTIME:
511 TIMESTAMP_NOBEGIN(result);
515 TIMESTAMP_NOEND(result);
519 abstime2tm(abstime, &tz, tm, &tzn);
520 if (tm2timestamp(tm, 0, NULL, &result) != 0)
522 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
523 errmsg("timestamp out of range")));
527 PG_RETURN_TIMESTAMP(result);
531 /* timestamptz_abstime()
532 * Convert timestamp with time zone to abstime.
535 timestamptz_abstime(PG_FUNCTION_ARGS)
537 TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
543 if (TIMESTAMP_IS_NOBEGIN(timestamp))
544 result = NOSTART_ABSTIME;
545 else if (TIMESTAMP_IS_NOEND(timestamp))
546 result = NOEND_ABSTIME;
547 else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
548 result = tm2abstime(tm, 0);
552 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
553 errmsg("timestamp out of range")));
554 result = INVALID_ABSTIME;
557 PG_RETURN_ABSOLUTETIME(result);
560 /* abstime_timestamptz()
561 * Convert abstime to timestamp with time zone.
564 abstime_timestamptz(PG_FUNCTION_ARGS)
566 AbsoluteTime abstime = PG_GETARG_ABSOLUTETIME(0);
571 char zone[MAXDATELEN + 1],
576 case INVALID_ABSTIME:
578 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
579 errmsg("cannot convert abstime \"invalid\" to timestamp")));
580 TIMESTAMP_NOBEGIN(result);
583 case NOSTART_ABSTIME:
584 TIMESTAMP_NOBEGIN(result);
588 TIMESTAMP_NOEND(result);
592 abstime2tm(abstime, &tz, tm, &tzn);
593 if (tm2timestamp(tm, 0, &tz, &result) != 0)
595 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
596 errmsg("timestamp out of range")));
600 PG_RETURN_TIMESTAMP(result);
604 /*****************************************************************************
605 * USER I/O ROUTINES *
606 *****************************************************************************/
609 * reltimein - converts a reltime string in an internal format
612 reltimein(PG_FUNCTION_ARGS)
614 char *str = PG_GETARG_CSTRING(0);
621 char *field[MAXDATEFIELDS];
623 ftype[MAXDATEFIELDS];
624 char workbuf[MAXDATELEN + 1];
626 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
627 field, ftype, MAXDATEFIELDS, &nf);
629 dterr = DecodeInterval(field, ftype, nf, INTERVAL_FULL_RANGE,
632 /* if those functions think it's a bad format, try ISO8601 style */
633 if (dterr == DTERR_BAD_FORMAT)
634 dterr = DecodeISO8601Interval(str,
639 if (dterr == DTERR_FIELD_OVERFLOW)
640 dterr = DTERR_INTERVAL_OVERFLOW;
641 DateTimeParseError(dterr, str, "reltime");
647 result = ((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec;
648 result += tm->tm_year * SECS_PER_YEAR + ((tm->tm_mon * DAYS_PER_MONTH) + tm->tm_mday) * SECS_PER_DAY;
652 elog(ERROR, "unexpected dtype %d while parsing reltime \"%s\"",
654 result = INVALID_RELTIME;
658 PG_RETURN_RELATIVETIME(result);
662 * reltimeout - converts the internal format to a reltime string
665 reltimeout(PG_FUNCTION_ARGS)
667 RelativeTime time = PG_GETARG_RELATIVETIME(0);
671 char buf[MAXDATELEN + 1];
673 reltime2tm(time, tm);
674 EncodeInterval(tm, 0, IntervalStyle, buf);
676 result = pstrdup(buf);
677 PG_RETURN_CSTRING(result);
681 * reltimerecv - converts external binary format to reltime
684 reltimerecv(PG_FUNCTION_ARGS)
686 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
688 PG_RETURN_RELATIVETIME((RelativeTime) pq_getmsgint(buf, sizeof(RelativeTime)));
692 * reltimesend - converts reltime to binary format
695 reltimesend(PG_FUNCTION_ARGS)
697 RelativeTime time = PG_GETARG_RELATIVETIME(0);
700 pq_begintypsend(&buf);
701 pq_sendint(&buf, time, sizeof(time));
702 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
707 reltime2tm(RelativeTime time, struct pg_tm * tm)
711 FMODULO(dtime, tm->tm_year, 31557600);
712 FMODULO(dtime, tm->tm_mon, 2592000);
713 FMODULO(dtime, tm->tm_mday, SECS_PER_DAY);
714 FMODULO(dtime, tm->tm_hour, SECS_PER_HOUR);
715 FMODULO(dtime, tm->tm_min, SECS_PER_MINUTE);
716 FMODULO(dtime, tm->tm_sec, 1);
721 * tintervalin - converts an tinterval string to internal format
724 tintervalin(PG_FUNCTION_ARGS)
726 char *tintervalstr = PG_GETARG_CSTRING(0);
727 TimeInterval tinterval;
728 AbsoluteTime i_start,
733 parsetinterval(tintervalstr, &t1, &t2);
735 tinterval = (TimeInterval) palloc(sizeof(TimeIntervalData));
737 if (t1 == INVALID_ABSTIME || t2 == INVALID_ABSTIME)
738 tinterval->status = T_INTERVAL_INVAL; /* undefined */
740 tinterval->status = T_INTERVAL_VALID;
742 i_start = ABSTIMEMIN(t1, t2);
743 i_end = ABSTIMEMAX(t1, t2);
744 tinterval->data[0] = i_start;
745 tinterval->data[1] = i_end;
747 PG_RETURN_TIMEINTERVAL(tinterval);
752 * tintervalout - converts an internal tinterval format to a string
755 tintervalout(PG_FUNCTION_ARGS)
757 TimeInterval tinterval = PG_GETARG_TIMEINTERVAL(0);
761 i_str = (char *) palloc(T_INTERVAL_LEN); /* ["..." "..."] */
762 strcpy(i_str, "[\"");
763 if (tinterval->status == T_INTERVAL_INVAL)
764 strcat(i_str, INVALID_INTERVAL_STR);
767 p = DatumGetCString(DirectFunctionCall1(abstimeout,
768 AbsoluteTimeGetDatum(tinterval->data[0])));
771 strcat(i_str, "\" \"");
772 p = DatumGetCString(DirectFunctionCall1(abstimeout,
773 AbsoluteTimeGetDatum(tinterval->data[1])));
777 strcat(i_str, "\"]");
778 PG_RETURN_CSTRING(i_str);
782 * tintervalrecv - converts external binary format to tinterval
785 tintervalrecv(PG_FUNCTION_ARGS)
787 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
788 TimeInterval tinterval;
791 tinterval = (TimeInterval) palloc(sizeof(TimeIntervalData));
793 tinterval->status = pq_getmsgint(buf, sizeof(tinterval->status));
794 tinterval->data[0] = pq_getmsgint(buf, sizeof(tinterval->data[0]));
795 tinterval->data[1] = pq_getmsgint(buf, sizeof(tinterval->data[1]));
797 if (tinterval->data[0] == INVALID_ABSTIME ||
798 tinterval->data[1] == INVALID_ABSTIME)
799 status = T_INTERVAL_INVAL; /* undefined */
801 status = T_INTERVAL_VALID;
803 if (status != tinterval->status)
805 (errcode(ERRCODE_INVALID_BINARY_REPRESENTATION),
806 errmsg("invalid status in external \"tinterval\" value")));
808 PG_RETURN_TIMEINTERVAL(tinterval);
812 * tintervalsend - converts tinterval to binary format
815 tintervalsend(PG_FUNCTION_ARGS)
817 TimeInterval tinterval = PG_GETARG_TIMEINTERVAL(0);
820 pq_begintypsend(&buf);
821 pq_sendint(&buf, tinterval->status, sizeof(tinterval->status));
822 pq_sendint(&buf, tinterval->data[0], sizeof(tinterval->data[0]));
823 pq_sendint(&buf, tinterval->data[1], sizeof(tinterval->data[1]));
824 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
828 /*****************************************************************************
830 *****************************************************************************/
833 interval_reltime(PG_FUNCTION_ARGS)
835 Interval *interval = PG_GETARG_INTERVAL_P(0);
842 year = interval->month / MONTHS_PER_YEAR;
843 month = interval->month % MONTHS_PER_YEAR;
846 #ifdef HAVE_INT64_TIMESTAMP
847 span = ((INT64CONST(365250000) * year + INT64CONST(30000000) * month +
848 INT64CONST(1000000) * day) * INT64CONST(86400)) +
850 span /= USECS_PER_SEC;
852 span = (DAYS_PER_YEAR * year + (double) DAYS_PER_MONTH * month + day) * SECS_PER_DAY + interval->time;
855 if (span < INT_MIN || span > INT_MAX)
856 time = INVALID_RELTIME;
860 PG_RETURN_RELATIVETIME(time);
865 reltime_interval(PG_FUNCTION_ARGS)
867 RelativeTime reltime = PG_GETARG_RELATIVETIME(0);
873 result = (Interval *) palloc(sizeof(Interval));
877 case INVALID_RELTIME:
879 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
880 errmsg("cannot convert reltime \"invalid\" to interval")));
887 #ifdef HAVE_INT64_TIMESTAMP
888 year = reltime / SECS_PER_YEAR;
889 reltime -= year * SECS_PER_YEAR;
890 month = reltime / (DAYS_PER_MONTH * SECS_PER_DAY);
891 reltime -= month * (DAYS_PER_MONTH * SECS_PER_DAY);
892 day = reltime / SECS_PER_DAY;
893 reltime -= day * SECS_PER_DAY;
895 result->time = (reltime * USECS_PER_SEC);
897 TMODULO(reltime, year, SECS_PER_YEAR);
898 TMODULO(reltime, month, DAYS_PER_MONTH * SECS_PER_DAY);
899 TMODULO(reltime, day, SECS_PER_DAY);
901 result->time = reltime;
903 result->month = MONTHS_PER_YEAR * year + month;
908 PG_RETURN_INTERVAL_P(result);
913 * mktinterval - creates a time interval with endpoints t1 and t2
916 mktinterval(PG_FUNCTION_ARGS)
918 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
919 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
920 AbsoluteTime tstart = ABSTIMEMIN(t1, t2);
921 AbsoluteTime tend = ABSTIMEMAX(t1, t2);
922 TimeInterval tinterval;
924 tinterval = (TimeInterval) palloc(sizeof(TimeIntervalData));
926 if (t1 == INVALID_ABSTIME || t2 == INVALID_ABSTIME)
927 tinterval->status = T_INTERVAL_INVAL;
931 tinterval->status = T_INTERVAL_VALID;
932 tinterval->data[0] = tstart;
933 tinterval->data[1] = tend;
936 PG_RETURN_TIMEINTERVAL(tinterval);
940 * timepl, timemi and abstimemi use the formula
941 * abstime + reltime = abstime
942 * so abstime - reltime = abstime
943 * and abstime - abstime = reltime
947 * timepl - returns the value of (abstime t1 + reltime t2)
950 timepl(PG_FUNCTION_ARGS)
952 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
953 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
955 if (AbsoluteTimeIsReal(t1) &&
956 RelativeTimeIsValid(t2) &&
957 ((t2 > 0 && t1 < NOEND_ABSTIME - t2) ||
958 (t2 <= 0 && t1 > NOSTART_ABSTIME - t2))) /* prevent overflow */
959 PG_RETURN_ABSOLUTETIME(t1 + t2);
961 PG_RETURN_ABSOLUTETIME(INVALID_ABSTIME);
966 * timemi - returns the value of (abstime t1 - reltime t2)
969 timemi(PG_FUNCTION_ARGS)
971 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
972 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
974 if (AbsoluteTimeIsReal(t1) &&
975 RelativeTimeIsValid(t2) &&
976 ((t2 > 0 && t1 > NOSTART_ABSTIME + t2) ||
977 (t2 <= 0 && t1 < NOEND_ABSTIME + t2))) /* prevent overflow */
978 PG_RETURN_ABSOLUTETIME(t1 - t2);
980 PG_RETURN_ABSOLUTETIME(INVALID_ABSTIME);
985 * intinterval - returns true iff absolute date is in the tinterval
988 intinterval(PG_FUNCTION_ARGS)
990 AbsoluteTime t = PG_GETARG_ABSOLUTETIME(0);
991 TimeInterval tinterval = PG_GETARG_TIMEINTERVAL(1);
993 if (tinterval->status == T_INTERVAL_VALID && t != INVALID_ABSTIME)
995 if (DatumGetBool(DirectFunctionCall2(abstimege,
996 AbsoluteTimeGetDatum(t),
997 AbsoluteTimeGetDatum(tinterval->data[0]))) &&
998 DatumGetBool(DirectFunctionCall2(abstimele,
999 AbsoluteTimeGetDatum(t),
1000 AbsoluteTimeGetDatum(tinterval->data[1]))))
1001 PG_RETURN_BOOL(true);
1003 PG_RETURN_BOOL(false);
1007 * tintervalrel - returns relative time corresponding to tinterval
1010 tintervalrel(PG_FUNCTION_ARGS)
1012 TimeInterval tinterval = PG_GETARG_TIMEINTERVAL(0);
1013 AbsoluteTime t1 = tinterval->data[0];
1014 AbsoluteTime t2 = tinterval->data[1];
1016 if (tinterval->status != T_INTERVAL_VALID)
1017 PG_RETURN_RELATIVETIME(INVALID_RELTIME);
1019 if (AbsoluteTimeIsReal(t1) &&
1020 AbsoluteTimeIsReal(t2))
1021 PG_RETURN_RELATIVETIME(t2 - t1);
1023 PG_RETURN_RELATIVETIME(INVALID_RELTIME);
1028 * timenow - returns time "now", internal format
1030 * Now AbsoluteTime is time since Jan 1 1970 -mer 7 Feb 1992
1033 timenow(PG_FUNCTION_ARGS)
1035 PG_RETURN_ABSOLUTETIME(GetCurrentAbsoluteTime());
1039 * reltime comparison routines
1042 reltime_cmp_internal(RelativeTime a, RelativeTime b)
1045 * We consider all INVALIDs to be equal and larger than any non-INVALID.
1046 * This is somewhat arbitrary; the important thing is to have a consistent
1049 if (a == INVALID_RELTIME)
1051 if (b == INVALID_RELTIME)
1052 return 0; /* INVALID = INVALID */
1054 return 1; /* INVALID > non-INVALID */
1057 if (b == INVALID_RELTIME)
1058 return -1; /* non-INVALID < INVALID */
1069 reltimeeq(PG_FUNCTION_ARGS)
1071 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1072 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1074 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) == 0);
1078 reltimene(PG_FUNCTION_ARGS)
1080 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1081 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1083 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) != 0);
1087 reltimelt(PG_FUNCTION_ARGS)
1089 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1090 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1092 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) < 0);
1096 reltimegt(PG_FUNCTION_ARGS)
1098 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1099 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1101 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) > 0);
1105 reltimele(PG_FUNCTION_ARGS)
1107 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1108 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1110 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) <= 0);
1114 reltimege(PG_FUNCTION_ARGS)
1116 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1117 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1119 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) >= 0);
1123 btreltimecmp(PG_FUNCTION_ARGS)
1125 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1126 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1128 PG_RETURN_INT32(reltime_cmp_internal(t1, t2));
1133 * tintervalsame - returns true iff tinterval i1 is same as tinterval i2
1134 * Check begin and end time.
1137 tintervalsame(PG_FUNCTION_ARGS)
1139 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1140 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1142 if (i1->status == T_INTERVAL_INVAL || i2->status == T_INTERVAL_INVAL)
1143 PG_RETURN_BOOL(false);
1145 if (DatumGetBool(DirectFunctionCall2(abstimeeq,
1146 AbsoluteTimeGetDatum(i1->data[0]),
1147 AbsoluteTimeGetDatum(i2->data[0]))) &&
1148 DatumGetBool(DirectFunctionCall2(abstimeeq,
1149 AbsoluteTimeGetDatum(i1->data[1]),
1150 AbsoluteTimeGetDatum(i2->data[1]))))
1151 PG_RETURN_BOOL(true);
1152 PG_RETURN_BOOL(false);
1156 * tinterval comparison routines
1158 * Note: comparison is based only on the lengths of the tintervals, not on
1159 * endpoint values (as long as they're not INVALID). This is pretty bogus,
1160 * but since it's only a legacy datatype, we're not going to change it.
1162 * Some other bogus things that won't be changed for compatibility reasons:
1163 * 1. The interval length computations overflow at 2^31 seconds, causing
1164 * intervals longer than that to sort oddly compared to those shorter.
1165 * 2. infinity and minus infinity (NOEND_ABSTIME and NOSTART_ABSTIME) are
1166 * just ordinary integers. Since this code doesn't handle them specially,
1167 * it's possible for [a b] to be considered longer than [c infinity] for
1168 * finite abstimes a, b, c. In combination with the previous point, the
1169 * interval [-infinity infinity] is treated as being shorter than many finite
1172 * If tinterval is ever reimplemented atop timestamp, it'd be good to give
1173 * some consideration to avoiding these problems.
1176 tinterval_cmp_internal(TimeInterval a, TimeInterval b)
1184 * We consider all INVALIDs to be equal and larger than any non-INVALID.
1185 * This is somewhat arbitrary; the important thing is to have a consistent
1188 a_invalid = a->status == T_INTERVAL_INVAL ||
1189 a->data[0] == INVALID_ABSTIME ||
1190 a->data[1] == INVALID_ABSTIME;
1191 b_invalid = b->status == T_INTERVAL_INVAL ||
1192 b->data[0] == INVALID_ABSTIME ||
1193 b->data[1] == INVALID_ABSTIME;
1198 return 0; /* INVALID = INVALID */
1200 return 1; /* INVALID > non-INVALID */
1204 return -1; /* non-INVALID < INVALID */
1206 a_len = a->data[1] - a->data[0];
1207 b_len = b->data[1] - b->data[0];
1211 else if (a_len == b_len)
1218 tintervaleq(PG_FUNCTION_ARGS)
1220 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1221 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1223 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) == 0);
1227 tintervalne(PG_FUNCTION_ARGS)
1229 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1230 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1232 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) != 0);
1236 tintervallt(PG_FUNCTION_ARGS)
1238 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1239 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1241 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) < 0);
1245 tintervalle(PG_FUNCTION_ARGS)
1247 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1248 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1250 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) <= 0);
1254 tintervalgt(PG_FUNCTION_ARGS)
1256 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1257 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1259 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) > 0);
1263 tintervalge(PG_FUNCTION_ARGS)
1265 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1266 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1268 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) >= 0);
1272 bttintervalcmp(PG_FUNCTION_ARGS)
1274 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1275 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1277 PG_RETURN_INT32(tinterval_cmp_internal(i1, i2));
1282 * tintervalleneq - returns true iff length of tinterval i is equal to
1284 * tintervallenne - returns true iff length of tinterval i is not equal
1286 * tintervallenlt - returns true iff length of tinterval i is less than
1288 * tintervallengt - returns true iff length of tinterval i is greater
1290 * tintervallenle - returns true iff length of tinterval i is less or
1291 * equal than reltime t
1292 * tintervallenge - returns true iff length of tinterval i is greater or
1293 * equal than reltime t
1296 tintervalleneq(PG_FUNCTION_ARGS)
1298 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1299 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1302 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1303 PG_RETURN_BOOL(false);
1304 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1305 TimeIntervalGetDatum(i)));
1306 PG_RETURN_BOOL(rt != INVALID_RELTIME && rt == t);
1310 tintervallenne(PG_FUNCTION_ARGS)
1312 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1313 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1316 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1317 PG_RETURN_BOOL(false);
1318 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1319 TimeIntervalGetDatum(i)));
1320 PG_RETURN_BOOL(rt != INVALID_RELTIME && rt != t);
1324 tintervallenlt(PG_FUNCTION_ARGS)
1326 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1327 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1330 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1331 PG_RETURN_BOOL(false);
1332 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1333 TimeIntervalGetDatum(i)));
1334 PG_RETURN_BOOL(rt != INVALID_RELTIME && rt < t);
1338 tintervallengt(PG_FUNCTION_ARGS)
1340 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1341 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1344 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1345 PG_RETURN_BOOL(false);
1346 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1347 TimeIntervalGetDatum(i)));
1348 PG_RETURN_BOOL(rt != INVALID_RELTIME && rt > t);
1352 tintervallenle(PG_FUNCTION_ARGS)
1354 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1355 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1358 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1359 PG_RETURN_BOOL(false);
1360 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1361 TimeIntervalGetDatum(i)));
1362 PG_RETURN_BOOL(rt != INVALID_RELTIME && rt <= t);
1366 tintervallenge(PG_FUNCTION_ARGS)
1368 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1369 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1372 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1373 PG_RETURN_BOOL(false);
1374 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1375 TimeIntervalGetDatum(i)));
1376 PG_RETURN_BOOL(rt != INVALID_RELTIME && rt >= t);
1380 * tintervalct - returns true iff tinterval i1 contains tinterval i2
1383 tintervalct(PG_FUNCTION_ARGS)
1385 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1386 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1388 if (i1->status == T_INTERVAL_INVAL || i2->status == T_INTERVAL_INVAL)
1389 PG_RETURN_BOOL(false);
1390 if (DatumGetBool(DirectFunctionCall2(abstimele,
1391 AbsoluteTimeGetDatum(i1->data[0]),
1392 AbsoluteTimeGetDatum(i2->data[0]))) &&
1393 DatumGetBool(DirectFunctionCall2(abstimege,
1394 AbsoluteTimeGetDatum(i1->data[1]),
1395 AbsoluteTimeGetDatum(i2->data[1]))))
1396 PG_RETURN_BOOL(true);
1397 PG_RETURN_BOOL(false);
1401 * tintervalov - returns true iff tinterval i1 (partially) overlaps i2
1404 tintervalov(PG_FUNCTION_ARGS)
1406 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1407 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1409 if (i1->status == T_INTERVAL_INVAL || i2->status == T_INTERVAL_INVAL)
1410 PG_RETURN_BOOL(false);
1411 if (DatumGetBool(DirectFunctionCall2(abstimelt,
1412 AbsoluteTimeGetDatum(i1->data[1]),
1413 AbsoluteTimeGetDatum(i2->data[0]))) ||
1414 DatumGetBool(DirectFunctionCall2(abstimegt,
1415 AbsoluteTimeGetDatum(i1->data[0]),
1416 AbsoluteTimeGetDatum(i2->data[1]))))
1417 PG_RETURN_BOOL(false);
1418 PG_RETURN_BOOL(true);
1422 * tintervalstart - returns the start of tinterval i
1425 tintervalstart(PG_FUNCTION_ARGS)
1427 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1429 if (i->status == T_INTERVAL_INVAL)
1430 PG_RETURN_ABSOLUTETIME(INVALID_ABSTIME);
1431 PG_RETURN_ABSOLUTETIME(i->data[0]);
1435 * tintervalend - returns the end of tinterval i
1438 tintervalend(PG_FUNCTION_ARGS)
1440 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1442 if (i->status == T_INTERVAL_INVAL)
1443 PG_RETURN_ABSOLUTETIME(INVALID_ABSTIME);
1444 PG_RETURN_ABSOLUTETIME(i->data[1]);
1448 /*****************************************************************************
1449 * PRIVATE ROUTINES *
1450 *****************************************************************************/
1453 * parsetinterval -- parse a tinterval string
1455 * output parameters:
1456 * i_start, i_end: tinterval margins
1459 * `[' {` '} `'' <AbsTime> `'' {` '} `'' <AbsTime> `'' {` '} `]'
1461 * OR `Undefined Range' (see also INVALID_INTERVAL_STR)
1463 * where <AbsTime> satisfies the syntax of absolute time.
1465 * e.g. [ ' Jan 18 1902' 'Jan 1 00:00:00 1970']
1468 parsetinterval(char *i_string,
1469 AbsoluteTime *i_start,
1470 AbsoluteTime *i_end)
1477 /* skip leading blanks up to '[' */
1478 while ((c = *p) != '\0')
1483 goto bogus; /* syntax error */
1488 goto bogus; /* syntax error */
1490 /* skip leading blanks up to '"' */
1491 while ((c = *p) != '\0')
1496 goto bogus; /* syntax error */
1501 goto bogus; /* syntax error */
1503 if (strncmp(INVALID_INTERVAL_STR, p, strlen(INVALID_INTERVAL_STR)) == 0)
1504 goto bogus; /* undefined range, handled like a syntax err. */
1505 /* search for the end of the first date and change it to a \0 */
1507 while ((c = *p1) != '\0')
1514 goto bogus; /* syntax error */
1516 /* get the first date */
1517 *i_start = DatumGetAbsoluteTime(DirectFunctionCall1(abstimein,
1518 CStringGetDatum(p)));
1519 /* undo change to \0 */
1522 /* skip blanks up to '"', beginning of second date */
1523 while ((c = *p) != '\0')
1528 goto bogus; /* syntax error */
1533 goto bogus; /* syntax error */
1535 /* search for the end of the second date and change it to a \0 */
1537 while ((c = *p1) != '\0')
1544 goto bogus; /* syntax error */
1546 /* get the second date */
1547 *i_end = DatumGetAbsoluteTime(DirectFunctionCall1(abstimein,
1548 CStringGetDatum(p)));
1549 /* undo change to \0 */
1552 /* skip blanks up to ']' */
1553 while ((c = *p) != '\0')
1558 goto bogus; /* syntax error */
1563 goto bogus; /* syntax error */
1567 goto bogus; /* syntax error */
1569 /* it seems to be a valid tinterval */
1574 (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
1575 errmsg("invalid input syntax for type tinterval: \"%s\"",
1577 *i_start = *i_end = INVALID_ABSTIME; /* keep compiler quiet */
1581 /*****************************************************************************
1583 *****************************************************************************/
1587 * returns the current time as a text. similar to timenow() but returns
1588 * seconds with more precision (up to microsecs). (I need this to compare
1589 * the Wisconsin benchmark with Illustra whose TimeNow() shows current
1590 * time with precision up to microsecs.) - ay 3/95
1593 timeofday(PG_FUNCTION_ARGS)
1600 gettimeofday(&tp, NULL);
1601 tt = (pg_time_t) tp.tv_sec;
1602 pg_strftime(templ, sizeof(templ), "%a %b %d %H:%M:%S.%%06d %Y %Z",
1603 pg_localtime(&tt, session_timezone));
1604 snprintf(buf, sizeof(buf), templ, tp.tv_usec);
1606 PG_RETURN_TEXT_P(cstring_to_text(buf));