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-2003, 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.118 2003/11/29 19:51:58 pgsql Exp $
15 *-------------------------------------------------------------------------
25 #include "access/xact.h"
26 #include "libpq/pqformat.h"
27 #include "miscadmin.h"
28 #include "utils/builtins.h"
31 #define MIN_DAYNUM -24856 /* December 13, 1901 */
32 #define MAX_DAYNUM 24854 /* January 18, 2038 */
34 #define INVALID_RELTIME_STR "Undefined RelTime"
35 #define INVALID_RELTIME_STR_LEN (sizeof(INVALID_RELTIME_STR)-1)
36 #define RELTIME_LABEL '@'
37 #define RELTIME_PAST "ago"
38 #define DIRMAXLEN (sizeof(RELTIME_PAST)-1)
41 * Unix epoch is Jan 1 00:00:00 1970.
42 * Postgres knows about times sixty-eight years on either side of that
43 * for these 4-byte types.
45 * "tinterval" is two 4-byte fields.
46 * Definitions for parsing tinterval.
49 #define IsSpace(C) ((C) == ' ')
51 #define T_INTERVAL_INVAL 0 /* data represents no valid interval */
52 #define T_INTERVAL_VALID 1 /* data represents a valid interval */
54 * ['Mon May 10 23:59:12 1943 PST' 'Sun Jan 14 03:14:21 1973 PST']
56 * 1234567890123456789012345678901234567890123456789012345678901234
58 * we allocate some extra -- timezones are usually 3 characters but
59 * this is not in the POSIX standard...
61 #define T_INTERVAL_LEN 80
62 #define INVALID_INTERVAL_STR "Undefined Range"
63 #define INVALID_INTERVAL_STR_LEN (sizeof(INVALID_INTERVAL_STR)-1)
65 #define ABSTIMEMIN(t1, t2) \
66 (DatumGetBool(DirectFunctionCall2(abstimele, \
67 AbsoluteTimeGetDatum(t1), \
68 AbsoluteTimeGetDatum(t2))) ? (t1) : (t2))
69 #define ABSTIMEMAX(t1, t2) \
70 (DatumGetBool(DirectFunctionCall2(abstimelt, \
71 AbsoluteTimeGetDatum(t1), \
72 AbsoluteTimeGetDatum(t2))) ? (t2) : (t1))
76 * Function prototypes -- internal to this file only
79 static AbsoluteTime tm2abstime(struct tm * tm, int tz);
80 static void reltime2tm(RelativeTime time, struct tm * tm);
81 static int istinterval(char *i_string,
82 AbsoluteTime *i_start,
87 * GetCurrentAbsoluteTime()
89 * Get the current system time (relative to Unix epoch).
92 GetCurrentAbsoluteTime(void)
97 return (AbsoluteTime) now;
102 * GetCurrentAbsoluteTimeUsec()
104 * Get the current system time (relative to Unix epoch), including fractional
105 * seconds expressed as microseconds.
108 GetCurrentAbsoluteTimeUsec(int *usec)
113 gettimeofday(&tp, NULL);
116 return (AbsoluteTime) now;
121 * AbsoluteTimeUsecToTimestampTz()
123 * Convert system time including microseconds to TimestampTz representation.
126 AbsoluteTimeUsecToTimestampTz(AbsoluteTime sec, int usec)
130 #ifdef HAVE_INT64_TIMESTAMP
131 result = ((sec - ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * 86400))
132 * INT64CONST(1000000)) + usec;
134 result = sec - ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * 86400)
135 + (usec / 1000000.0);
143 * GetCurrentDateTime()
145 * Get the transaction start time ("now()") broken down as a struct tm.
148 GetCurrentDateTime(struct tm * tm)
152 abstime2tm(GetCurrentTransactionStartTime(), &tz, tm, NULL);
156 * GetCurrentTimeUsec()
158 * Get the transaction start time ("now()") broken down as a struct tm,
159 * including fractional seconds and timezone offset.
162 GetCurrentTimeUsec(struct tm * tm, fsec_t *fsec, int *tzp)
167 abstime2tm(GetCurrentTransactionStartTimeUsec(&usec), &tz, tm, NULL);
168 /* Note: don't pass NULL tzp to abstime2tm; affects behavior */
171 #ifdef HAVE_INT64_TIMESTAMP
174 *fsec = usec / 1000000.0;
180 abstime2tm(AbsoluteTime _time, int *tzp, struct tm * tm, char **tzn)
182 time_t time = (time_t) _time;
186 * If HasCTZSet is true then we have a brute force time zone
187 * specified. Go ahead and rotate to the local time zone since we will
188 * later bypass any calls which adjust the tm fields.
190 if (HasCTZSet && (tzp != NULL))
193 if ((!HasCTZSet) && (tzp != NULL))
194 tx = localtime((time_t *) &time);
196 tx = gmtime((time_t *) &time);
198 tm->tm_year = tx->tm_year + 1900;
199 tm->tm_mon = tx->tm_mon + 1;
200 tm->tm_mday = tx->tm_mday;
201 tm->tm_hour = tx->tm_hour;
202 tm->tm_min = tx->tm_min;
203 tm->tm_sec = tx->tm_sec;
204 tm->tm_isdst = tx->tm_isdst;
206 #if defined(HAVE_TM_ZONE)
207 tm->tm_gmtoff = tx->tm_gmtoff;
208 tm->tm_zone = tx->tm_zone;
213 * We have a brute force time zone per SQL99? Then use it without
214 * change since we have already rotated to the time zone.
219 tm->tm_gmtoff = CTimeZone;
227 *tzp = -tm->tm_gmtoff; /* tm_gmtoff is Sun/DEC-ism */
230 * XXX FreeBSD man pages indicate that this should work - tgl
236 * Copy no more than MAXTZLEN bytes of timezone to tzn, in
237 * case it contains an error message, which doesn't fit in
240 StrNCpy(*tzn, tm->tm_zone, MAXTZLEN + 1);
241 if (strlen(tm->tm_zone) > MAXTZLEN)
243 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
244 errmsg("invalid time zone name: \"%s\"",
251 #elif defined(HAVE_INT_TIMEZONE)
255 * We have a brute force time zone per SQL99? Then use it without
256 * change since we have already rotated to the time zone.
267 *tzp = ((tm->tm_isdst > 0) ? (TIMEZONE_GLOBAL - 3600) : TIMEZONE_GLOBAL);
272 * Copy no more than MAXTZLEN bytes of timezone to tzn, in
273 * case it contains an error message, which doesn't fit in
276 StrNCpy(*tzn, tzname[tm->tm_isdst], MAXTZLEN + 1);
277 if (strlen(tzname[tm->tm_isdst]) > MAXTZLEN)
279 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
280 errmsg("invalid time zone name: \"%s\"",
281 tzname[tm->tm_isdst])));
287 #else /* not (HAVE_TM_ZONE || HAVE_INT_TIMEZONE) */
291 * We have a brute force time zone per SQL99? Then use it without
292 * change since we have already rotated to the time zone.
315 * Convert a tm structure to abstime.
316 * Note that tm has full year (not 1900-based) and 1-based month.
319 tm2abstime(struct tm * tm, int tz)
324 /* validate, before going out of range on some members */
325 if (tm->tm_year < 1901 || tm->tm_year > 2038
326 || tm->tm_mon < 1 || tm->tm_mon > 12
327 || tm->tm_mday < 1 || tm->tm_mday > 31
328 || tm->tm_hour < 0 || tm->tm_hour > 23
329 || tm->tm_min < 0 || tm->tm_min > 59
330 || tm->tm_sec < 0 || tm->tm_sec > 60)
331 return INVALID_ABSTIME;
333 day = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - UNIX_EPOCH_JDATE;
335 /* check for time out of range */
336 if ((day < MIN_DAYNUM) || (day > MAX_DAYNUM))
337 return INVALID_ABSTIME;
339 /* convert to seconds */
340 sec = tm->tm_sec + tz + (tm->tm_min + (day * 24 + tm->tm_hour) * 60) * 60;
342 /* check for overflow */
343 if ((day == MAX_DAYNUM && sec < 0) ||
344 (day == MIN_DAYNUM && sec > 0))
345 return INVALID_ABSTIME;
347 /* check for reserved values (e.g. "current" on edge of usual range */
348 if (!AbsoluteTimeIsReal(sec))
349 return INVALID_ABSTIME;
356 * Decode date/time string and return abstime.
359 abstimein(PG_FUNCTION_ARGS)
361 char *str = PG_GETARG_CSTRING(0);
368 char *field[MAXDATEFIELDS];
369 char lowstr[MAXDATELEN + 1];
372 ftype[MAXDATEFIELDS];
374 if (strlen(str) >= sizeof(lowstr))
375 dterr = DTERR_BAD_FORMAT;
377 dterr = ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf);
379 dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
381 DateTimeParseError(dterr, str, "abstime");
386 result = tm2abstime(tm, tz);
392 * Don't bother retaining this as a reserved value, but
393 * instead just set to the actual epoch time (1970-01-01)
399 result = NOEND_ABSTIME;
403 result = NOSTART_ABSTIME;
407 result = INVALID_ABSTIME;
411 elog(ERROR, "unexpected dtype %d while parsing abstime \"%s\"",
413 result = INVALID_ABSTIME;
417 PG_RETURN_ABSOLUTETIME(result);
422 * Given an AbsoluteTime return the English text version of the date
425 abstimeout(PG_FUNCTION_ARGS)
427 AbsoluteTime time = PG_GETARG_ABSOLUTETIME(0);
433 char buf[MAXDATELEN + 1];
434 char zone[MAXDATELEN + 1],
440 * Note that timestamp no longer supports 'invalid'. Retain
441 * 'invalid' for abstime for now, but dump it someday.
443 case INVALID_ABSTIME:
444 strcpy(buf, INVALID);
449 case NOSTART_ABSTIME:
453 abstime2tm(time, &tz, tm, &tzn);
454 EncodeDateTime(tm, fsec, &tz, &tzn, DateStyle, buf);
458 result = pstrdup(buf);
459 PG_RETURN_CSTRING(result);
463 * abstimerecv - converts external binary format to abstime
466 abstimerecv(PG_FUNCTION_ARGS)
468 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
470 PG_RETURN_ABSOLUTETIME((AbsoluteTime) pq_getmsgint(buf, sizeof(AbsoluteTime)));
474 * abstimesend - converts abstime to binary format
477 abstimesend(PG_FUNCTION_ARGS)
479 AbsoluteTime time = PG_GETARG_ABSOLUTETIME(0);
482 pq_begintypsend(&buf);
483 pq_sendint(&buf, time, sizeof(time));
484 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
491 abstime_finite(PG_FUNCTION_ARGS)
493 AbsoluteTime abstime = PG_GETARG_ABSOLUTETIME(0);
495 PG_RETURN_BOOL((abstime != INVALID_ABSTIME) &&
496 (abstime != NOSTART_ABSTIME) &&
497 (abstime != NOEND_ABSTIME));
502 * abstime comparison routines
505 abstime_cmp_internal(AbsoluteTime a, AbsoluteTime b)
508 * We consider all INVALIDs to be equal and larger than any non-INVALID.
509 * This is somewhat arbitrary; the important thing is to have a
510 * consistent sort order.
512 if (a == INVALID_ABSTIME)
514 if (b == INVALID_ABSTIME)
515 return 0; /* INVALID = INVALID */
517 return 1; /* INVALID > non-INVALID */
520 if (b == INVALID_ABSTIME)
521 return -1; /* non-INVALID < INVALID */
524 /* CURRENT is no longer stored internally... */
525 /* XXX this is broken, should go away: */
526 if (a == CURRENT_ABSTIME)
527 a = GetCurrentTransactionStartTime();
528 if (b == CURRENT_ABSTIME)
529 b = GetCurrentTransactionStartTime();
541 abstimeeq(PG_FUNCTION_ARGS)
543 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
544 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
546 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) == 0);
550 abstimene(PG_FUNCTION_ARGS)
552 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
553 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
555 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) != 0);
559 abstimelt(PG_FUNCTION_ARGS)
561 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
562 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
564 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) < 0);
568 abstimegt(PG_FUNCTION_ARGS)
570 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
571 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
573 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) > 0);
577 abstimele(PG_FUNCTION_ARGS)
579 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
580 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
582 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) <= 0);
586 abstimege(PG_FUNCTION_ARGS)
588 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
589 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
591 PG_RETURN_BOOL(abstime_cmp_internal(t1, t2) >= 0);
595 btabstimecmp(PG_FUNCTION_ARGS)
597 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
598 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
600 PG_RETURN_INT32(abstime_cmp_internal(t1, t2));
604 /* timestamp_abstime()
605 * Convert timestamp to abstime.
608 timestamp_abstime(PG_FUNCTION_ARGS)
610 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
617 if (TIMESTAMP_IS_NOBEGIN(timestamp))
618 result = NOSTART_ABSTIME;
619 else if (TIMESTAMP_IS_NOEND(timestamp))
620 result = NOEND_ABSTIME;
621 else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL) == 0)
623 tz = DetermineLocalTimeZone(tm);
624 result = tm2abstime(tm, tz);
629 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
630 errmsg("timestamp out of range")));
631 result = INVALID_ABSTIME;
634 PG_RETURN_ABSOLUTETIME(result);
637 /* abstime_timestamp()
638 * Convert abstime to timestamp.
641 abstime_timestamp(PG_FUNCTION_ARGS)
643 AbsoluteTime abstime = PG_GETARG_ABSOLUTETIME(0);
648 char zone[MAXDATELEN + 1],
653 case INVALID_ABSTIME:
655 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
656 errmsg("cannot convert abstime \"invalid\" to timestamp")));
657 TIMESTAMP_NOBEGIN(result);
660 case NOSTART_ABSTIME:
661 TIMESTAMP_NOBEGIN(result);
665 TIMESTAMP_NOEND(result);
669 abstime2tm(abstime, &tz, tm, &tzn);
670 if (tm2timestamp(tm, 0, NULL, &result) != 0)
672 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
673 errmsg("timestamp out of range")));
677 PG_RETURN_TIMESTAMP(result);
681 /* timestamptz_abstime()
682 * Convert timestamp with time zone to abstime.
685 timestamptz_abstime(PG_FUNCTION_ARGS)
687 TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
693 if (TIMESTAMP_IS_NOBEGIN(timestamp))
694 result = NOSTART_ABSTIME;
695 else if (TIMESTAMP_IS_NOEND(timestamp))
696 result = NOEND_ABSTIME;
697 else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL) == 0)
698 result = tm2abstime(tm, 0);
702 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
703 errmsg("timestamp out of range")));
704 result = INVALID_ABSTIME;
707 PG_RETURN_ABSOLUTETIME(result);
710 /* abstime_timestamptz()
711 * Convert abstime to timestamp with time zone.
714 abstime_timestamptz(PG_FUNCTION_ARGS)
716 AbsoluteTime abstime = PG_GETARG_ABSOLUTETIME(0);
721 char zone[MAXDATELEN + 1],
726 case INVALID_ABSTIME:
728 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
729 errmsg("cannot convert abstime \"invalid\" to timestamp")));
730 TIMESTAMP_NOBEGIN(result);
733 case NOSTART_ABSTIME:
734 TIMESTAMP_NOBEGIN(result);
738 TIMESTAMP_NOEND(result);
742 abstime2tm(abstime, &tz, tm, &tzn);
743 if (tm2timestamp(tm, 0, &tz, &result) != 0)
745 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
746 errmsg("timestamp out of range")));
750 PG_RETURN_TIMESTAMP(result);
754 /*****************************************************************************
755 * USER I/O ROUTINES *
756 *****************************************************************************/
759 * reltimein - converts a reltime string in an internal format
762 reltimein(PG_FUNCTION_ARGS)
764 char *str = PG_GETARG_CSTRING(0);
771 char *field[MAXDATEFIELDS];
773 ftype[MAXDATEFIELDS];
774 char lowstr[MAXDATELEN + 1];
776 if (strlen(str) >= sizeof(lowstr))
777 dterr = DTERR_BAD_FORMAT;
779 dterr = ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf);
781 dterr = DecodeInterval(field, ftype, nf, &dtype, tm, &fsec);
784 if (dterr == DTERR_FIELD_OVERFLOW)
785 dterr = DTERR_INTERVAL_OVERFLOW;
786 DateTimeParseError(dterr, str, "reltime");
792 result = ((((tm->tm_hour * 60) + tm->tm_min) * 60) + tm->tm_sec);
793 result += ((tm->tm_year * 36525 * 864) + (((tm->tm_mon * 30) + tm->tm_mday) * 86400));
797 elog(ERROR, "unexpected dtype %d while parsing reltime \"%s\"",
799 result = INVALID_RELTIME;
803 PG_RETURN_RELATIVETIME(result);
807 * reltimeout - converts the internal format to a reltime string
810 reltimeout(PG_FUNCTION_ARGS)
812 RelativeTime time = PG_GETARG_RELATIVETIME(0);
816 char buf[MAXDATELEN + 1];
818 reltime2tm(time, tm);
819 EncodeInterval(tm, 0, DateStyle, buf);
821 result = pstrdup(buf);
822 PG_RETURN_CSTRING(result);
826 * reltimerecv - converts external binary format to reltime
829 reltimerecv(PG_FUNCTION_ARGS)
831 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
833 PG_RETURN_RELATIVETIME((RelativeTime) pq_getmsgint(buf, sizeof(RelativeTime)));
837 * reltimesend - converts reltime to binary format
840 reltimesend(PG_FUNCTION_ARGS)
842 RelativeTime time = PG_GETARG_RELATIVETIME(0);
845 pq_begintypsend(&buf);
846 pq_sendint(&buf, time, sizeof(time));
847 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
852 reltime2tm(RelativeTime time, struct tm * tm)
856 FMODULO(dtime, tm->tm_year, 31557600);
857 FMODULO(dtime, tm->tm_mon, 2592000);
858 FMODULO(dtime, tm->tm_mday, 86400);
859 FMODULO(dtime, tm->tm_hour, 3600);
860 FMODULO(dtime, tm->tm_min, 60);
861 FMODULO(dtime, tm->tm_sec, 1);
866 * tintervalin - converts an interval string to internal format
869 tintervalin(PG_FUNCTION_ARGS)
871 char *intervalstr = PG_GETARG_CSTRING(0);
872 TimeInterval interval;
873 AbsoluteTime i_start,
878 interval = (TimeInterval) palloc(sizeof(TimeIntervalData));
879 if (istinterval(intervalstr, &t1, &t2) == 0)
881 (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
882 errmsg("invalid input syntax for type tinterval: \"%s\"",
885 if (t1 == INVALID_ABSTIME || t2 == INVALID_ABSTIME)
886 interval->status = T_INTERVAL_INVAL; /* undefined */
888 interval->status = T_INTERVAL_VALID;
890 i_start = ABSTIMEMIN(t1, t2);
891 i_end = ABSTIMEMAX(t1, t2);
892 interval->data[0] = i_start;
893 interval->data[1] = i_end;
895 PG_RETURN_TIMEINTERVAL(interval);
900 * tintervalout - converts an internal interval format to a string
903 tintervalout(PG_FUNCTION_ARGS)
905 TimeInterval interval = PG_GETARG_TIMEINTERVAL(0);
909 i_str = (char *) palloc(T_INTERVAL_LEN); /* ["..." "..."] */
910 strcpy(i_str, "[\"");
911 if (interval->status == T_INTERVAL_INVAL)
912 strcat(i_str, INVALID_INTERVAL_STR);
915 p = DatumGetCString(DirectFunctionCall1(abstimeout,
916 AbsoluteTimeGetDatum(interval->data[0])));
919 strcat(i_str, "\" \"");
920 p = DatumGetCString(DirectFunctionCall1(abstimeout,
921 AbsoluteTimeGetDatum(interval->data[1])));
925 strcat(i_str, "\"]");
926 PG_RETURN_CSTRING(i_str);
930 * tintervalrecv - converts external binary format to tinterval
933 tintervalrecv(PG_FUNCTION_ARGS)
935 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
936 TimeInterval interval;
938 interval = (TimeInterval) palloc(sizeof(TimeIntervalData));
940 interval->status = pq_getmsgint(buf, sizeof(interval->status));
941 if (!(interval->status == T_INTERVAL_INVAL ||
942 interval->status == T_INTERVAL_VALID))
944 (errcode(ERRCODE_INVALID_BINARY_REPRESENTATION),
945 errmsg("invalid status in external \"tinterval\" value")));
947 interval->data[0] = pq_getmsgint(buf, sizeof(interval->data[0]));
948 interval->data[1] = pq_getmsgint(buf, sizeof(interval->data[1]));
950 PG_RETURN_TIMEINTERVAL(interval);
954 * tintervalsend - converts tinterval to binary format
957 tintervalsend(PG_FUNCTION_ARGS)
959 TimeInterval interval = PG_GETARG_TIMEINTERVAL(0);
962 pq_begintypsend(&buf);
963 pq_sendint(&buf, interval->status, sizeof(interval->status));
964 pq_sendint(&buf, interval->data[0], sizeof(interval->data[0]));
965 pq_sendint(&buf, interval->data[1], sizeof(interval->data[1]));
966 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
970 /*****************************************************************************
972 *****************************************************************************/
975 interval_reltime(PG_FUNCTION_ARGS)
977 Interval *interval = PG_GETARG_INTERVAL_P(0);
982 #ifdef HAVE_INT64_TIMESTAMP
989 if (interval->month == 0)
994 else if (abs(interval->month) >= 12)
996 year = (interval->month / 12);
997 month = (interval->month % 12);
1002 month = interval->month;
1005 #ifdef HAVE_INT64_TIMESTAMP
1006 span = ((((INT64CONST(365250000) * year) + (INT64CONST(30000000) * month))
1007 * INT64CONST(86400)) + interval->time);
1008 span /= INT64CONST(1000000);
1010 span = (((((double) 365.25 * year) + ((double) 30 * month)) * 86400) + interval->time);
1013 if ((span < INT_MIN) || (span > INT_MAX))
1014 time = INVALID_RELTIME;
1018 PG_RETURN_RELATIVETIME(time);
1023 reltime_interval(PG_FUNCTION_ARGS)
1025 RelativeTime reltime = PG_GETARG_RELATIVETIME(0);
1030 result = (Interval *) palloc(sizeof(Interval));
1034 case INVALID_RELTIME:
1036 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1037 errmsg("cannot convert reltime \"invalid\" to interval")));
1043 #ifdef HAVE_INT64_TIMESTAMP
1044 year = (reltime / (36525 * 864));
1045 reltime -= (year * (36525 * 864));
1046 month = (reltime / (30 * 86400));
1047 reltime -= (month * (30 * 86400));
1049 result->time = (reltime * INT64CONST(1000000));
1051 TMODULO(reltime, year, (36525 * 864));
1052 TMODULO(reltime, month, (30 * 86400));
1054 result->time = reltime;
1056 result->month = ((12 * year) + month);
1060 PG_RETURN_INTERVAL_P(result);
1065 * mktinterval - creates a time interval with endpoints t1 and t2
1068 mktinterval(PG_FUNCTION_ARGS)
1070 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
1071 AbsoluteTime t2 = PG_GETARG_ABSOLUTETIME(1);
1072 AbsoluteTime tstart = ABSTIMEMIN(t1, t2);
1073 AbsoluteTime tend = ABSTIMEMAX(t1, t2);
1074 TimeInterval interval;
1076 interval = (TimeInterval) palloc(sizeof(TimeIntervalData));
1078 if (t1 == INVALID_ABSTIME || t2 == INVALID_ABSTIME)
1079 interval->status = T_INTERVAL_INVAL;
1082 interval->status = T_INTERVAL_VALID;
1083 interval->data[0] = tstart;
1084 interval->data[1] = tend;
1087 PG_RETURN_TIMEINTERVAL(interval);
1091 * timepl, timemi and abstimemi use the formula
1092 * abstime + reltime = abstime
1093 * so abstime - reltime = abstime
1094 * and abstime - abstime = reltime
1098 * timepl - returns the value of (abstime t1 + reltime t2)
1101 timepl(PG_FUNCTION_ARGS)
1103 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
1104 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1106 if (AbsoluteTimeIsReal(t1) &&
1107 RelativeTimeIsValid(t2) &&
1108 ((t2 > 0) ? (t1 < NOEND_ABSTIME - t2)
1109 : (t1 > NOSTART_ABSTIME - t2))) /* prevent overflow */
1110 PG_RETURN_ABSOLUTETIME(t1 + t2);
1112 PG_RETURN_ABSOLUTETIME(INVALID_ABSTIME);
1117 * timemi - returns the value of (abstime t1 - reltime t2)
1120 timemi(PG_FUNCTION_ARGS)
1122 AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
1123 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1125 if (AbsoluteTimeIsReal(t1) &&
1126 RelativeTimeIsValid(t2) &&
1127 ((t2 > 0) ? (t1 > NOSTART_ABSTIME + t2)
1128 : (t1 < NOEND_ABSTIME + t2))) /* prevent overflow */
1129 PG_RETURN_ABSOLUTETIME(t1 - t2);
1131 PG_RETURN_ABSOLUTETIME(INVALID_ABSTIME);
1136 * intinterval - returns true iff absolute date is in the interval
1139 intinterval(PG_FUNCTION_ARGS)
1141 AbsoluteTime t = PG_GETARG_ABSOLUTETIME(0);
1142 TimeInterval interval = PG_GETARG_TIMEINTERVAL(1);
1144 if (interval->status == T_INTERVAL_VALID && t != INVALID_ABSTIME)
1146 if (DatumGetBool(DirectFunctionCall2(abstimege,
1147 AbsoluteTimeGetDatum(t),
1148 AbsoluteTimeGetDatum(interval->data[0]))) &&
1149 DatumGetBool(DirectFunctionCall2(abstimele,
1150 AbsoluteTimeGetDatum(t),
1151 AbsoluteTimeGetDatum(interval->data[1]))))
1152 PG_RETURN_BOOL(true);
1154 PG_RETURN_BOOL(false);
1158 * tintervalrel - returns relative time corresponding to interval
1161 tintervalrel(PG_FUNCTION_ARGS)
1163 TimeInterval interval = PG_GETARG_TIMEINTERVAL(0);
1164 AbsoluteTime t1 = interval->data[0];
1165 AbsoluteTime t2 = interval->data[1];
1167 if (interval->status != T_INTERVAL_VALID)
1168 PG_RETURN_RELATIVETIME(INVALID_RELTIME);
1170 if (AbsoluteTimeIsReal(t1) &&
1171 AbsoluteTimeIsReal(t2))
1172 PG_RETURN_RELATIVETIME(t2 - t1);
1174 PG_RETURN_RELATIVETIME(INVALID_RELTIME);
1179 * timenow - returns time "now", internal format
1181 * Now AbsoluteTime is time since Jan 1 1970 -mer 7 Feb 1992
1184 timenow(PG_FUNCTION_ARGS)
1189 PG_RETURN_ABSOLUTETIME(INVALID_ABSTIME);
1191 PG_RETURN_ABSOLUTETIME((AbsoluteTime) sec);
1195 * reltime comparison routines
1198 reltime_cmp_internal(RelativeTime a, RelativeTime b)
1201 * We consider all INVALIDs to be equal and larger than any non-INVALID.
1202 * This is somewhat arbitrary; the important thing is to have a
1203 * consistent sort order.
1205 if (a == INVALID_RELTIME)
1207 if (b == INVALID_RELTIME)
1208 return 0; /* INVALID = INVALID */
1210 return 1; /* INVALID > non-INVALID */
1213 if (b == INVALID_RELTIME)
1214 return -1; /* non-INVALID < INVALID */
1225 reltimeeq(PG_FUNCTION_ARGS)
1227 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1228 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1230 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) == 0);
1234 reltimene(PG_FUNCTION_ARGS)
1236 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1237 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1239 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) != 0);
1243 reltimelt(PG_FUNCTION_ARGS)
1245 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1246 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1248 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) < 0);
1252 reltimegt(PG_FUNCTION_ARGS)
1254 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1255 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1257 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) > 0);
1261 reltimele(PG_FUNCTION_ARGS)
1263 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1264 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1266 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) <= 0);
1270 reltimege(PG_FUNCTION_ARGS)
1272 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1273 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1275 PG_RETURN_BOOL(reltime_cmp_internal(t1, t2) >= 0);
1279 btreltimecmp(PG_FUNCTION_ARGS)
1281 RelativeTime t1 = PG_GETARG_RELATIVETIME(0);
1282 RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
1284 PG_RETURN_INT32(reltime_cmp_internal(t1, t2));
1289 * tintervalsame - returns true iff interval i1 is same as interval i2
1290 * Check begin and end time.
1293 tintervalsame(PG_FUNCTION_ARGS)
1295 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1296 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1298 if (i1->status == T_INTERVAL_INVAL || i2->status == T_INTERVAL_INVAL)
1299 PG_RETURN_BOOL(false);
1301 if (DatumGetBool(DirectFunctionCall2(abstimeeq,
1302 AbsoluteTimeGetDatum(i1->data[0]),
1303 AbsoluteTimeGetDatum(i2->data[0]))) &&
1304 DatumGetBool(DirectFunctionCall2(abstimeeq,
1305 AbsoluteTimeGetDatum(i1->data[1]),
1306 AbsoluteTimeGetDatum(i2->data[1]))))
1307 PG_RETURN_BOOL(true);
1308 PG_RETURN_BOOL(false);
1312 * tinterval comparison routines
1314 * Note: comparison is based on the lengths of the intervals, not on
1315 * endpoint value. This is pretty bogus, but since it's only a legacy
1316 * datatype I'm not going to propose changing it.
1319 tinterval_cmp_internal(TimeInterval a, TimeInterval b)
1327 * We consider all INVALIDs to be equal and larger than any non-INVALID.
1328 * This is somewhat arbitrary; the important thing is to have a
1329 * consistent sort order.
1331 a_invalid = ((a->status == T_INTERVAL_INVAL) ||
1332 (a->data[0] == INVALID_ABSTIME) ||
1333 (a->data[1] == INVALID_ABSTIME));
1334 b_invalid = ((b->status == T_INTERVAL_INVAL) ||
1335 (b->data[0] == INVALID_ABSTIME) ||
1336 (b->data[1] == INVALID_ABSTIME));
1341 return 0; /* INVALID = INVALID */
1343 return 1; /* INVALID > non-INVALID */
1347 return -1; /* non-INVALID < INVALID */
1349 a_len = a->data[1] - a->data[0];
1350 b_len = b->data[1] - b->data[0];
1354 else if (a_len == b_len)
1361 tintervaleq(PG_FUNCTION_ARGS)
1363 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1364 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1366 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) == 0);
1370 tintervalne(PG_FUNCTION_ARGS)
1372 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1373 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1375 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) != 0);
1379 tintervallt(PG_FUNCTION_ARGS)
1381 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1382 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1384 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) < 0);
1388 tintervalle(PG_FUNCTION_ARGS)
1390 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1391 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1393 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) <= 0);
1397 tintervalgt(PG_FUNCTION_ARGS)
1399 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1400 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1402 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) > 0);
1406 tintervalge(PG_FUNCTION_ARGS)
1408 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1409 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1411 PG_RETURN_BOOL(tinterval_cmp_internal(i1, i2) >= 0);
1415 bttintervalcmp(PG_FUNCTION_ARGS)
1417 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1418 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1420 PG_RETURN_INT32(tinterval_cmp_internal(i1, i2));
1425 * tintervalleneq - returns true iff length of interval i is equal to
1427 * tintervallenne - returns true iff length of interval i is not equal
1429 * tintervallenlt - returns true iff length of interval i is less than
1431 * tintervallengt - returns true iff length of interval i is greater
1433 * tintervallenle - returns true iff length of interval i is less or
1434 * equal than reltime t
1435 * tintervallenge - returns true iff length of interval i is greater or
1436 * equal than reltime t
1439 tintervalleneq(PG_FUNCTION_ARGS)
1441 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1442 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1445 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1446 PG_RETURN_BOOL(false);
1447 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1448 TimeIntervalGetDatum(i)));
1449 PG_RETURN_BOOL((rt != INVALID_RELTIME) && (rt == t));
1453 tintervallenne(PG_FUNCTION_ARGS)
1455 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1456 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1459 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1460 PG_RETURN_BOOL(false);
1461 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1462 TimeIntervalGetDatum(i)));
1463 PG_RETURN_BOOL((rt != INVALID_RELTIME) && (rt != t));
1467 tintervallenlt(PG_FUNCTION_ARGS)
1469 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1470 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1473 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1474 PG_RETURN_BOOL(false);
1475 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1476 TimeIntervalGetDatum(i)));
1477 PG_RETURN_BOOL((rt != INVALID_RELTIME) && (rt < t));
1481 tintervallengt(PG_FUNCTION_ARGS)
1483 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1484 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1487 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1488 PG_RETURN_BOOL(false);
1489 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1490 TimeIntervalGetDatum(i)));
1491 PG_RETURN_BOOL((rt != INVALID_RELTIME) && (rt > t));
1495 tintervallenle(PG_FUNCTION_ARGS)
1497 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1498 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1501 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1502 PG_RETURN_BOOL(false);
1503 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1504 TimeIntervalGetDatum(i)));
1505 PG_RETURN_BOOL((rt != INVALID_RELTIME) && (rt <= t));
1509 tintervallenge(PG_FUNCTION_ARGS)
1511 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1512 RelativeTime t = PG_GETARG_RELATIVETIME(1);
1515 if (i->status == T_INTERVAL_INVAL || t == INVALID_RELTIME)
1516 PG_RETURN_BOOL(false);
1517 rt = DatumGetRelativeTime(DirectFunctionCall1(tintervalrel,
1518 TimeIntervalGetDatum(i)));
1519 PG_RETURN_BOOL((rt != INVALID_RELTIME) && (rt >= t));
1523 * tintervalct - returns true iff interval i1 contains interval i2
1526 tintervalct(PG_FUNCTION_ARGS)
1528 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1529 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1531 if (i1->status == T_INTERVAL_INVAL || i2->status == T_INTERVAL_INVAL)
1532 PG_RETURN_BOOL(false);
1533 if (DatumGetBool(DirectFunctionCall2(abstimele,
1534 AbsoluteTimeGetDatum(i1->data[0]),
1535 AbsoluteTimeGetDatum(i2->data[0]))) &&
1536 DatumGetBool(DirectFunctionCall2(abstimege,
1537 AbsoluteTimeGetDatum(i1->data[1]),
1538 AbsoluteTimeGetDatum(i2->data[1]))))
1539 PG_RETURN_BOOL(true);
1540 PG_RETURN_BOOL(false);
1544 * tintervalov - returns true iff interval i1 (partially) overlaps i2
1547 tintervalov(PG_FUNCTION_ARGS)
1549 TimeInterval i1 = PG_GETARG_TIMEINTERVAL(0);
1550 TimeInterval i2 = PG_GETARG_TIMEINTERVAL(1);
1552 if (i1->status == T_INTERVAL_INVAL || i2->status == T_INTERVAL_INVAL)
1553 PG_RETURN_BOOL(false);
1554 if (DatumGetBool(DirectFunctionCall2(abstimelt,
1555 AbsoluteTimeGetDatum(i1->data[1]),
1556 AbsoluteTimeGetDatum(i2->data[0]))) ||
1557 DatumGetBool(DirectFunctionCall2(abstimegt,
1558 AbsoluteTimeGetDatum(i1->data[0]),
1559 AbsoluteTimeGetDatum(i2->data[1]))))
1560 PG_RETURN_BOOL(false);
1561 PG_RETURN_BOOL(true);
1565 * tintervalstart - returns the start of interval i
1568 tintervalstart(PG_FUNCTION_ARGS)
1570 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1572 if (i->status == T_INTERVAL_INVAL)
1573 PG_RETURN_ABSOLUTETIME(INVALID_ABSTIME);
1574 PG_RETURN_ABSOLUTETIME(i->data[0]);
1578 * tintervalend - returns the end of interval i
1581 tintervalend(PG_FUNCTION_ARGS)
1583 TimeInterval i = PG_GETARG_TIMEINTERVAL(0);
1585 if (i->status == T_INTERVAL_INVAL)
1586 PG_RETURN_ABSOLUTETIME(INVALID_ABSTIME);
1587 PG_RETURN_ABSOLUTETIME(i->data[1]);
1591 /*****************************************************************************
1592 * PRIVATE ROUTINES *
1593 *****************************************************************************/
1596 * istinterval - returns 1, iff i_string is a valid interval descr.
1597 * 0, iff i_string is NOT a valid interval desc.
1598 * 2, iff any time is INVALID_ABSTIME
1601 * i_start, i_end: interval margins
1604 * `[' {` '} `'' <AbsTime> `'' {` '} `'' <AbsTime> `'' {` '} `]'
1606 * OR `Undefined Range' (see also INVALID_INTERVAL_STR)
1608 * where <AbsTime> satisfies the syntax of absolute time.
1610 * e.g. [ ' Jan 18 1902' 'Jan 1 00:00:00 1970']
1613 istinterval(char *i_string,
1614 AbsoluteTime *i_start,
1615 AbsoluteTime *i_end)
1622 /* skip leading blanks up to '[' */
1623 while ((c = *p) != '\0')
1628 return 0; /* syntax error */
1633 /* skip leading blanks up to '"' */
1634 while ((c = *p) != '\0')
1639 return 0; /* syntax error */
1644 if (strncmp(INVALID_INTERVAL_STR, p, strlen(INVALID_INTERVAL_STR)) == 0)
1645 return 0; /* undefined range, handled like a syntax
1647 /* search for the end of the first date and change it to a NULL */
1649 while ((c = *p1) != '\0')
1658 /* get the first date */
1659 *i_start = DatumGetAbsoluteTime(DirectFunctionCall1(abstimein,
1660 CStringGetDatum(p)));
1661 /* rechange NULL at the end of the first date to a '"' */
1664 /* skip blanks up to '"', beginning of second date */
1665 while ((c = *p) != '\0')
1670 return 0; /* syntax error */
1675 /* search for the end of the second date and change it to a NULL */
1677 while ((c = *p1) != '\0')
1686 /* get the second date */
1687 *i_end = DatumGetAbsoluteTime(DirectFunctionCall1(abstimein,
1688 CStringGetDatum(p)));
1689 /* rechange NULL at the end of the first date to a '"' */
1692 /* skip blanks up to ']' */
1693 while ((c = *p) != '\0')
1698 return 0; /* syntax error */
1705 return 0; /* syntax error */
1706 /* it seems to be a valid interval */
1711 /*****************************************************************************
1713 *****************************************************************************/
1717 * returns the current time as a text. similar to timenow() but returns
1718 * seconds with more precision (up to microsecs). (I need this to compare
1719 * the Wisconsin benchmark with Illustra whose TimeNow() shows current
1720 * time with precision up to microsecs.) - ay 3/95
1723 timeofday(PG_FUNCTION_ARGS)
1726 struct timezone tpz;
1732 gettimeofday(&tp, &tpz);
1733 strftime(templ, sizeof(templ), "%a %b %d %H:%M:%S.%%06d %Y %Z",
1734 localtime((time_t *) &tp.tv_sec));
1735 snprintf(buf, sizeof(buf), templ, tp.tv_usec);
1737 len = VARHDRSZ + strlen(buf);
1738 result = (text *) palloc(len);
1739 VARATT_SIZEP(result) = len;
1740 memcpy(VARDATA(result), buf, strlen(buf));
1741 PG_RETURN_TEXT_P(result);