1 /*-------------------------------------------------------------------------
4 * implements DATE and TIME data types specified in SQL-92 standard
6 * Copyright (c) 1994-5, Regents of the University of California
10 * $Header: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v 1.17 1997/10/25 05:16:09 thomas Exp $
12 *-------------------------------------------------------------------------
14 #include <stdio.h> /* for sprintf() */
22 #include "miscadmin.h"
23 #include "utils/builtins.h"
24 #include "utils/nabstime.h"
25 #include "utils/datetime.h"
26 #include "access/xact.h"
28 static int date2tm(DateADT dateVal, int *tzp, struct tm * tm, double *fsec, char **tzn);
31 static int day_tab[2][12] = {
32 {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31},
33 {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31}};
35 #define isleap(y) (((y % 4) == 0 && (y % 100) != 0) || (y % 400) == 0)
37 #define UTIME_MINYEAR (1901)
38 #define UTIME_MINMONTH (12)
39 #define UTIME_MINDAY (14)
40 #define UTIME_MAXYEAR (2038)
41 #define UTIME_MAXMONTH (01)
42 #define UTIME_MAXDAY (18)
44 #define IS_VALID_UTIME(y,m,d) (((y > UTIME_MINYEAR) \
45 || ((y == UTIME_MINYEAR) && ((m > UTIME_MINMONTH) \
46 || ((m == UTIME_MINMONTH) && (d >= UTIME_MINDAY))))) \
47 && ((y < UTIME_MAXYEAR) \
48 || ((y == UTIME_MAXYEAR) && ((m < UTIME_MAXMONTH) \
49 || ((m == UTIME_MAXMONTH) && (d <= UTIME_MAXDAY))))))
51 /*****************************************************************************
53 *****************************************************************************/
57 * Given date text string, convert to internal date format.
69 char *field[MAXDATEFIELDS];
70 int ftype[MAXDATEFIELDS];
71 char lowstr[MAXDATELEN + 1];
73 if (!PointerIsValid(str))
74 elog(WARN, "Bad (null) date external representation", NULL);
77 printf("date_in- input string is %s\n", str);
79 if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0)
80 || (DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp) != 0))
81 elog(WARN, "Bad date external representation %s", str);
99 elog(WARN, "Unrecognized date external representation %s", str);
102 if (tm->tm_year < 0 || tm->tm_year > 32767)
103 elog(WARN, "date_in: year must be limited to values 0 through 32767 in '%s'", str);
104 if (tm->tm_mon < 1 || tm->tm_mon > 12)
105 elog(WARN, "date_in: month must be limited to values 1 through 12 in '%s'", str);
106 if (tm->tm_mday < 1 || tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
107 elog(WARN, "date_in: day must be limited to values 1 through %d in '%s'",
108 day_tab[isleap(tm->tm_year)][tm->tm_mon - 1], str);
110 date = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(2000, 1, 1));
116 * Given internal format date, convert to text string.
119 date_out(DateADT date)
124 char buf[MAXDATELEN + 1];
126 j2date((date + date2j(2000, 1, 1)),
127 &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
129 EncodeDateOnly(tm, DateStyle, buf);
131 result = PALLOC(strlen(buf) + 1);
139 date_eq(DateADT dateVal1, DateADT dateVal2)
141 return (dateVal1 == dateVal2);
145 date_ne(DateADT dateVal1, DateADT dateVal2)
147 return (dateVal1 != dateVal2);
151 date_lt(DateADT dateVal1, DateADT dateVal2)
153 return (dateVal1 < dateVal2);
157 date_le(DateADT dateVal1, DateADT dateVal2)
159 return (dateVal1 <= dateVal2);
163 date_gt(DateADT dateVal1, DateADT dateVal2)
165 return (dateVal1 > dateVal2);
169 date_ge(DateADT dateVal1, DateADT dateVal2)
171 return (dateVal1 >= dateVal2);
175 date_cmp(DateADT dateVal1, DateADT dateVal2)
177 if (dateVal1 < dateVal2)
181 else if (dateVal1 > dateVal2)
189 date_larger(DateADT dateVal1, DateADT dateVal2)
191 return (date_gt(dateVal1, dateVal2) ? dateVal1 : dateVal2);
192 } /* date_larger() */
195 date_smaller(DateADT dateVal1, DateADT dateVal2)
197 return (date_lt(dateVal1, dateVal2) ? dateVal1 : dateVal2);
198 } /* date_smaller() */
200 /* Compute difference between two dates in days.
203 date_mi(DateADT dateVal1, DateADT dateVal2)
205 return (dateVal1 - dateVal2);
208 /* Add a number of days to a date, giving a new date.
209 * Must handle both positive and negative numbers of days.
212 date_pli(DateADT dateVal, int4 days)
214 return (dateVal + days);
217 /* Subtract a number of days from a date, giving a new date.
220 date_mii(DateADT dateVal, int4 days)
222 return (date_pli(dateVal, -days));
227 * Convert date to datetime data type.
230 date_datetime(DateADT dateVal)
239 result = PALLOCTYPE(DateTime);
241 if (date2tm(dateVal, &tz, tm, &fsec, &tzn) != 0)
242 elog(WARN, "Unable to convert date to datetime", NULL);
245 printf("date_datetime- date is %d.%02d.%02d\n", tm->tm_year, tm->tm_mon, tm->tm_mday);
246 printf("date_datetime- time is %02d:%02d:%02d %.7f\n", tm->tm_hour, tm->tm_min, tm->tm_sec, fsec);
249 if (tm2datetime(tm, fsec, &tz, result) != 0)
250 elog(WARN, "Datetime out of range", NULL);
253 } /* date_datetime() */
257 * Convert datetime to date data type.
260 datetime_date(DateTime *datetime)
269 if (!PointerIsValid(datetime))
270 elog(WARN, "Unable to convert null datetime to date", NULL);
272 if (DATETIME_NOT_FINITE(*datetime))
273 elog(WARN, "Unable to convert datetime to date", NULL);
275 if (DATETIME_IS_EPOCH(*datetime))
277 datetime2tm(SetDateTime(*datetime), NULL, tm, &fsec, NULL);
280 else if (DATETIME_IS_CURRENT(*datetime))
282 datetime2tm(SetDateTime(*datetime), &tz, tm, &fsec, &tzn);
287 if (datetime2tm(*datetime, &tz, tm, &fsec, &tzn) != 0)
288 elog(WARN, "Unable to convert datetime to date", NULL);
291 result = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(2000, 1, 1));
294 } /* datetime_date() */
298 * Convert abstime to date data type.
301 abstime_date(AbsoluteTime abstime)
310 case INVALID_ABSTIME:
311 case NOSTART_ABSTIME:
313 elog(WARN, "Unable to convert reserved abstime value to date", NULL);
316 * pretend to drop through to make compiler think that result
321 result = date2j(1970, 1, 1) - date2j(2000, 1, 1);
324 case CURRENT_ABSTIME:
326 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(2000, 1, 1);
330 abstime2tm(abstime, &tz, tm, NULL);
331 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - date2j(2000, 1, 1);
336 } /* abstime_date() */
340 * Convert date to time structure.
341 * Note that date is an implicit local time, but the system calls assume
342 * that everything is GMT. So, convert to GMT, rotate to local time,
343 * and then convert again to try to get the time zones correct.
346 date2tm(DateADT dateVal, int *tzp, struct tm * tm, double *fsec, char **tzn)
353 j2date((dateVal + date2j(2000, 1, 1)), &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
359 if (IS_VALID_UTIME(tm->tm_year, tm->tm_mon, tm->tm_mday))
362 /* convert to system time */
363 utime = ((dateVal + (date2j(2000, 1, 1) - date2j(1970, 1, 1))) * 86400);
364 utime += (12 * 60 * 60);/* rotate to noon to get the right day in
367 #ifdef USE_POSIX_TIME
368 tx = localtime(&utime);
371 #ifdef HAVE_INT_TIMEZONE
372 printf("date2tm- (localtime) %d.%02d.%02d %02d:%02d:%02.0f %s %s dst=%d\n",
373 tx->tm_year, tx->tm_mon, tx->tm_mday, tx->tm_hour, tx->tm_min, (double) tm->tm_sec,
374 tzname[0], tzname[1], tx->tm_isdst);
377 tm->tm_year = tx->tm_year + 1900;
378 tm->tm_mon = tx->tm_mon + 1;
379 tm->tm_mday = tx->tm_mday;
380 tm->tm_isdst = tx->tm_isdst;
382 #ifdef HAVE_INT_TIMEZONE
383 *tzp = (tm->tm_isdst ? (timezone - 3600) : timezone);
385 *tzn = tzname[(tm->tm_isdst > 0)];
387 #else /* !HAVE_INT_TIMEZONE */
388 tm->tm_gmtoff = tx->tm_gmtoff;
389 tm->tm_zone = tx->tm_zone;
391 *tzp = (tm->tm_isdst ? (tm->tm_gmtoff - 3600) : tm->tm_gmtoff); /* tm_gmtoff is
397 #else /* !USE_POSIX_TIME */
398 *tzp = CTimeZone; /* V7 conventions; don't know timezone? */
403 /* otherwise, outside of timezone range so convert to GMT... */
408 printf("date2tm- convert %d-%d-%d %d:%d%d to datetime\n",
409 tm->tm_year, tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min, tm->tm_sec);
422 /*****************************************************************************
424 *****************************************************************************/
437 char lowstr[MAXDATELEN + 1];
438 char *field[MAXDATEFIELDS];
440 int ftype[MAXDATEFIELDS];
442 if (!PointerIsValid(str))
443 elog(WARN, "Bad (null) time external representation", NULL);
445 if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0)
446 || (DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec) != 0))
447 elog(WARN, "Bad time external representation '%s'", str);
449 if ((tm->tm_hour < 0) || (tm->tm_hour > 23))
450 elog(WARN, "Hour must be limited to values 0 through 23 in '%s'", str);
451 if ((tm->tm_min < 0) || (tm->tm_min > 59))
452 elog(WARN, "Minute must be limited to values 0 through 59 in '%s'", str);
453 if ((tm->tm_sec < 0) || ((tm->tm_sec + fsec) >= 60))
454 elog(WARN, "Second must be limited to values 0 through < 60 in '%s'", str);
456 time = PALLOCTYPE(TimeADT);
458 *time = ((((tm->tm_hour * 60) + tm->tm_min) * 60) + tm->tm_sec + fsec);
465 time_out(TimeADT *time)
472 char buf[MAXDATELEN + 1];
474 if (!PointerIsValid(time))
477 tm->tm_hour = (*time / (60 * 60));
478 tm->tm_min = (((int) (*time / 60)) % 60);
479 tm->tm_sec = (((int) *time) % 60);
483 EncodeTimeOnly(tm, fsec, DateStyle, buf);
485 result = PALLOC(strlen(buf) + 1);
494 time_eq(TimeADT *time1, TimeADT *time2)
496 if (!PointerIsValid(time1) || !PointerIsValid(time2))
499 return (*time1 == *time2);
503 time_ne(TimeADT *time1, TimeADT *time2)
505 if (!PointerIsValid(time1) || !PointerIsValid(time2))
508 return (*time1 != *time2);
512 time_lt(TimeADT *time1, TimeADT *time2)
514 if (!PointerIsValid(time1) || !PointerIsValid(time2))
517 return (*time1 < *time2);
521 time_le(TimeADT *time1, TimeADT *time2)
523 if (!PointerIsValid(time1) || !PointerIsValid(time2))
526 return (*time1 <= *time2);
530 time_gt(TimeADT *time1, TimeADT *time2)
532 if (!PointerIsValid(time1) || !PointerIsValid(time2))
535 return (*time1 > *time2);
539 time_ge(TimeADT *time1, TimeADT *time2)
541 if (!PointerIsValid(time1) || !PointerIsValid(time2))
544 return (*time1 >= *time2);
548 time_cmp(TimeADT *time1, TimeADT *time2)
550 return ((*time1 < *time2) ? -1 : (((*time1 > *time2) ? 1 : 0)));
555 * Convert datetime to time data type.
558 datetime_time(DateTime *datetime)
567 if (!PointerIsValid(datetime))
568 elog(WARN, "Unable to convert null datetime to date", NULL);
570 if (DATETIME_NOT_FINITE(*datetime))
571 elog(WARN, "Unable to convert datetime to date", NULL);
573 if (DATETIME_IS_EPOCH(*datetime))
575 datetime2tm(SetDateTime(*datetime), NULL, tm, &fsec, NULL);
578 else if (DATETIME_IS_CURRENT(*datetime))
580 datetime2tm(SetDateTime(*datetime), &tz, tm, &fsec, &tzn);
585 if (datetime2tm(*datetime, &tz, tm, &fsec, &tzn) != 0)
586 elog(WARN, "Unable to convert datetime to date", NULL);
589 result = PALLOCTYPE(TimeADT);
591 *result = ((((tm->tm_hour * 60) + tm->tm_min) * 60) + tm->tm_sec + fsec);
594 } /* datetime_time() */
598 * Convert date and time to datetime data type.
599 * Should be called datetime_datetime()
600 * but need <= 16 characters for function names.
603 datet_datetime(DateADT date, TimeADT *time)
607 if (!PointerIsValid(time))
609 result = PALLOCTYPE(DateTime);
610 DATETIME_INVALID(*result);
612 result = date_datetime(date);
617 } /* datet_datetime() */
620 int32 /* RelativeTime */
621 int42reltime(int32 timevalue)