1 /*-------------------------------------------------------------------------
4 * Support functions for date/time types.
6 * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
7 * Portions Copyright (c) 1994, Regents of the University of California
11 * $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.187 2008/02/25 23:36:28 tgl Exp $
13 *-------------------------------------------------------------------------
22 #include "access/heapam.h"
23 #include "access/xact.h"
24 #include "catalog/pg_type.h"
26 #include "miscadmin.h"
27 #include "utils/builtins.h"
28 #include "utils/datetime.h"
29 #include "utils/memutils.h"
30 #include "utils/tzparser.h"
33 static int DecodeNumber(int flen, char *field, bool haveTextMonth,
34 int fmask, int *tmask,
35 struct pg_tm * tm, fsec_t *fsec, bool *is2digits);
36 static int DecodeNumberField(int len, char *str,
37 int fmask, int *tmask,
38 struct pg_tm * tm, fsec_t *fsec, bool *is2digits);
39 static int DecodeTime(char *str, int fmask, int *tmask,
40 struct pg_tm * tm, fsec_t *fsec);
41 static int DecodeTimezone(char *str, int *tzp);
42 static const datetkn *datebsearch(const char *key, const datetkn *base, int nel);
43 static int DecodeDate(char *str, int fmask, int *tmask, bool *is2digits,
45 static int ValidateDate(int fmask, bool is2digits, bool bc,
47 static void TrimTrailingZeros(char *str);
50 const int day_tab[2][13] =
52 {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0},
53 {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}
56 char *months[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun",
57 "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", NULL};
59 char *days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
60 "Thursday", "Friday", "Saturday", NULL};
63 /*****************************************************************************
65 *****************************************************************************/
68 * Definitions for squeezing values into "value"
69 * We set aside a high bit for a sign, and scale the timezone offsets
70 * in minutes by a factor of 15 (so can represent quarter-hour increments).
72 #define ABS_SIGNBIT ((char) 0200)
73 #define VALMASK ((char) 0177)
75 #define NEG(n) ((n)|ABS_SIGNBIT)
76 #define SIGNEDCHAR(c) ((c)&ABS_SIGNBIT? -((c)&VALMASK): (c))
77 #define FROMVAL(tp) (-SIGNEDCHAR((tp)->value) * 15) /* uncompress */
78 #define TOVAL(tp, v) ((tp)->value = ((v) < 0? NEG((-(v))/15): POS(v)/15))
81 * datetktbl holds date/time keywords.
83 * Note that this table must be strictly alphabetically ordered to allow an
84 * O(ln(N)) search algorithm to be used.
86 * The text field is NOT guaranteed to be NULL-terminated.
88 * To keep this table reasonably small, we divide the lexval for TZ and DTZ
89 * entries by 15 (so they are on 15 minute boundaries) and truncate the text
90 * field at TOKMAXLEN characters.
91 * Formerly, we divided by 10 rather than 15 but there are a few time zones
92 * which are 30 or 45 minutes away from an even hour, most are on an hour
93 * boundary, and none on other boundaries.
95 * The static table contains no TZ or DTZ entries, rather those are loaded
96 * from configuration files and stored in timezonetktbl, which has the same
97 * format as the static datetktbl.
99 static datetkn *timezonetktbl = NULL;
101 static int sztimezonetktbl = 0;
103 static const datetkn datetktbl[] = {
104 /* text, token, lexval */
105 {EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
106 {"abstime", IGNORE_DTF, 0}, /* for pre-v6.1 "Invalid Abstime" */
107 {DA_D, ADBC, AD}, /* "ad" for years > 0 */
108 {"allballs", RESERV, DTK_ZULU}, /* 00:00:00 */
112 {"at", IGNORE_DTF, 0}, /* "at" (throwaway) */
114 {"august", MONTH, 8},
115 {DB_C, ADBC, BC}, /* "bc" for years <= 0 */
116 {DCURRENT, RESERV, DTK_CURRENT}, /* "current" is always now */
117 {"d", UNITS, DTK_DAY}, /* "day of month" for ISO input */
119 {"december", MONTH, 12},
120 {"dow", RESERV, DTK_DOW}, /* day of week */
121 {"doy", RESERV, DTK_DOY}, /* day of year */
123 {EPOCH, RESERV, DTK_EPOCH}, /* "epoch" reserved for system epoch time */
125 {"february", MONTH, 2},
128 {"h", UNITS, DTK_HOUR}, /* "hour" */
129 {LATE, RESERV, DTK_LATE}, /* "infinity" reserved for "late time" */
130 {INVALID, RESERV, DTK_INVALID}, /* "invalid" reserved for bad time */
131 {"isodow", RESERV, DTK_ISODOW}, /* ISO day of week, Sunday == 7 */
132 {"isoyear", UNITS, DTK_ISOYEAR}, /* year in terms of the ISO week date */
133 {"j", UNITS, DTK_JULIAN},
135 {"january", MONTH, 1},
136 {"jd", UNITS, DTK_JULIAN},
138 {"julian", UNITS, DTK_JULIAN},
142 {"m", UNITS, DTK_MONTH}, /* "month" for ISO input */
146 {"mm", UNITS, DTK_MINUTE}, /* "minute" for ISO input */
150 {"november", MONTH, 11},
151 {NOW, RESERV, DTK_NOW}, /* current transaction time */
153 {"october", MONTH, 10},
154 {"on", IGNORE_DTF, 0}, /* "on" (throwaway) */
156 {"s", UNITS, DTK_SECOND}, /* "seconds" for ISO input */
158 {"saturday", DOW, 6},
161 {"september", MONTH, 9},
164 {"t", ISOTIME, DTK_TIME}, /* Filler for ISO time fields */
168 {"thursday", DOW, 4},
169 {TODAY, RESERV, DTK_TODAY}, /* midnight */
170 {TOMORROW, RESERV, DTK_TOMORROW}, /* tomorrow midnight */
174 {"undefined", RESERV, DTK_INVALID}, /* pre-v6.1 invalid time */
176 {"wednesday", DOW, 3},
178 {"y", UNITS, DTK_YEAR}, /* "year" for ISO input */
179 {YESTERDAY, RESERV, DTK_YESTERDAY} /* yesterday midnight */
182 static int szdatetktbl = sizeof datetktbl / sizeof datetktbl[0];
184 static datetkn deltatktbl[] = {
185 /* text, token, lexval */
186 {"@", IGNORE_DTF, 0}, /* postgres relative prefix */
187 {DAGO, AGO, 0}, /* "ago" indicates negative time offset */
188 {"c", UNITS, DTK_CENTURY}, /* "century" relative */
189 {"cent", UNITS, DTK_CENTURY}, /* "century" relative */
190 {"centuries", UNITS, DTK_CENTURY}, /* "centuries" relative */
191 {DCENTURY, UNITS, DTK_CENTURY}, /* "century" relative */
192 {"d", UNITS, DTK_DAY}, /* "day" relative */
193 {DDAY, UNITS, DTK_DAY}, /* "day" relative */
194 {"days", UNITS, DTK_DAY}, /* "days" relative */
195 {"dec", UNITS, DTK_DECADE}, /* "decade" relative */
196 {DDECADE, UNITS, DTK_DECADE}, /* "decade" relative */
197 {"decades", UNITS, DTK_DECADE}, /* "decades" relative */
198 {"decs", UNITS, DTK_DECADE}, /* "decades" relative */
199 {"h", UNITS, DTK_HOUR}, /* "hour" relative */
200 {DHOUR, UNITS, DTK_HOUR}, /* "hour" relative */
201 {"hours", UNITS, DTK_HOUR}, /* "hours" relative */
202 {"hr", UNITS, DTK_HOUR}, /* "hour" relative */
203 {"hrs", UNITS, DTK_HOUR}, /* "hours" relative */
204 {INVALID, RESERV, DTK_INVALID}, /* reserved for invalid time */
205 {"m", UNITS, DTK_MINUTE}, /* "minute" relative */
206 {"microsecon", UNITS, DTK_MICROSEC}, /* "microsecond" relative */
207 {"mil", UNITS, DTK_MILLENNIUM}, /* "millennium" relative */
208 {"millennia", UNITS, DTK_MILLENNIUM}, /* "millennia" relative */
209 {DMILLENNIUM, UNITS, DTK_MILLENNIUM}, /* "millennium" relative */
210 {"millisecon", UNITS, DTK_MILLISEC}, /* relative */
211 {"mils", UNITS, DTK_MILLENNIUM}, /* "millennia" relative */
212 {"min", UNITS, DTK_MINUTE}, /* "minute" relative */
213 {"mins", UNITS, DTK_MINUTE}, /* "minutes" relative */
214 {DMINUTE, UNITS, DTK_MINUTE}, /* "minute" relative */
215 {"minutes", UNITS, DTK_MINUTE}, /* "minutes" relative */
216 {"mon", UNITS, DTK_MONTH}, /* "months" relative */
217 {"mons", UNITS, DTK_MONTH}, /* "months" relative */
218 {DMONTH, UNITS, DTK_MONTH}, /* "month" relative */
219 {"months", UNITS, DTK_MONTH},
220 {"ms", UNITS, DTK_MILLISEC},
221 {"msec", UNITS, DTK_MILLISEC},
222 {DMILLISEC, UNITS, DTK_MILLISEC},
223 {"mseconds", UNITS, DTK_MILLISEC},
224 {"msecs", UNITS, DTK_MILLISEC},
225 {"qtr", UNITS, DTK_QUARTER}, /* "quarter" relative */
226 {DQUARTER, UNITS, DTK_QUARTER}, /* "quarter" relative */
227 {"reltime", IGNORE_DTF, 0}, /* pre-v6.1 "Undefined Reltime" */
228 {"s", UNITS, DTK_SECOND},
229 {"sec", UNITS, DTK_SECOND},
230 {DSECOND, UNITS, DTK_SECOND},
231 {"seconds", UNITS, DTK_SECOND},
232 {"secs", UNITS, DTK_SECOND},
233 {DTIMEZONE, UNITS, DTK_TZ}, /* "timezone" time offset */
234 {"timezone_h", UNITS, DTK_TZ_HOUR}, /* timezone hour units */
235 {"timezone_m", UNITS, DTK_TZ_MINUTE}, /* timezone minutes units */
236 {"undefined", RESERV, DTK_INVALID}, /* pre-v6.1 invalid time */
237 {"us", UNITS, DTK_MICROSEC}, /* "microsecond" relative */
238 {"usec", UNITS, DTK_MICROSEC}, /* "microsecond" relative */
239 {DMICROSEC, UNITS, DTK_MICROSEC}, /* "microsecond" relative */
240 {"useconds", UNITS, DTK_MICROSEC}, /* "microseconds" relative */
241 {"usecs", UNITS, DTK_MICROSEC}, /* "microseconds" relative */
242 {"w", UNITS, DTK_WEEK}, /* "week" relative */
243 {DWEEK, UNITS, DTK_WEEK}, /* "week" relative */
244 {"weeks", UNITS, DTK_WEEK}, /* "weeks" relative */
245 {"y", UNITS, DTK_YEAR}, /* "year" relative */
246 {DYEAR, UNITS, DTK_YEAR}, /* "year" relative */
247 {"years", UNITS, DTK_YEAR}, /* "years" relative */
248 {"yr", UNITS, DTK_YEAR}, /* "year" relative */
249 {"yrs", UNITS, DTK_YEAR} /* "years" relative */
252 static int szdeltatktbl = sizeof deltatktbl / sizeof deltatktbl[0];
254 static const datetkn *datecache[MAXDATEFIELDS] = {NULL};
256 static const datetkn *deltacache[MAXDATEFIELDS] = {NULL};
260 * Calendar time to Julian date conversions.
261 * Julian date is commonly used in astronomical applications,
262 * since it is numerically accurate and computationally simple.
263 * The algorithms here will accurately convert between Julian day
264 * and calendar date for all non-negative Julian days
265 * (i.e. from Nov 24, -4713 on).
267 * These routines will be used by other date/time packages
270 * Rewritten to eliminate overflow problems. This now allows the
271 * routines to work correctly for all Julian day counts from
272 * 0 to 2147483647 (Nov 24, -4713 to Jun 3, 5874898) assuming
273 * a 32-bit integer. Longer types should also work to the limits
274 * of their precision.
278 date2j(int y, int m, int d)
295 julian = y * 365 - 32167;
296 julian += y / 4 - century + century / 4;
297 julian += 7834 * m / 256 + d;
303 j2date(int jd, int *year, int *month, int *day)
312 quad = julian / 146097;
313 extra = (julian - quad * 146097) * 4 + 3;
314 julian += 60 + quad * 3 + extra / 146097;
315 quad = julian / 1461;
316 julian -= quad * 1461;
317 y = julian * 4 / 1461;
318 julian = ((y != 0) ? ((julian + 305) % 365) : ((julian + 306) % 366))
322 quad = julian * 2141 / 65536;
323 *day = julian - 7834 * quad / 256;
324 *month = (quad + 10) % 12 + 1;
331 * j2day - convert Julian date to day-of-week (0..6 == Sun..Sat)
333 * Note: various places use the locution j2day(date - 1) to produce a
334 * result according to the convention 0..6 = Mon..Sun. This is a bit of
335 * a crock, but will work as long as the computation here is just a modulo.
352 * GetCurrentDateTime()
354 * Get the transaction start time ("now()") broken down as a struct pg_tm.
357 GetCurrentDateTime(struct pg_tm * tm)
362 timestamp2tm(GetCurrentTransactionStartTimestamp(), &tz, tm, &fsec,
364 /* Note: don't pass NULL tzp to timestamp2tm; affects behavior */
368 * GetCurrentTimeUsec()
370 * Get the transaction start time ("now()") broken down as a struct pg_tm,
371 * including fractional seconds and timezone offset.
374 GetCurrentTimeUsec(struct pg_tm * tm, fsec_t *fsec, int *tzp)
378 timestamp2tm(GetCurrentTransactionStartTimestamp(), &tz, tm, fsec,
380 /* Note: don't pass NULL tzp to timestamp2tm; affects behavior */
386 /* TrimTrailingZeros()
387 * ... resulting from printing numbers with full precision.
390 TrimTrailingZeros(char *str)
392 int len = strlen(str);
395 /* chop off trailing one to cope with interval rounding */
396 if (strcmp(str + len - 4, "0001") == 0)
403 /* chop off trailing zeros... but leave at least 2 fractional digits */
404 while (*(str + len - 1) == '0' && *(str + len - 3) != '.')
412 * Break string into tokens based on a date/time context.
413 * Returns 0 if successful, DTERR code if bogus input detected.
415 * timestr - the input string
416 * workbuf - workspace for field string storage. This must be
417 * larger than the largest legal input for this datetime type --
418 * some additional space will be needed to NUL terminate fields.
419 * buflen - the size of workbuf
420 * field[] - pointers to field strings are returned in this array
421 * ftype[] - field type indicators are returned in this array
422 * maxfields - dimensions of the above two arrays
423 * *numfields - set to the actual number of fields detected
425 * The fields extracted from the input are stored as separate,
426 * null-terminated strings in the workspace at workbuf. Any text is
427 * converted to lower case.
429 * Several field types are assigned:
430 * DTK_NUMBER - digits and (possibly) a decimal point
431 * DTK_DATE - digits and two delimiters, or digits and text
432 * DTK_TIME - digits, colon delimiters, and possibly a decimal point
433 * DTK_STRING - text (no digits or punctuation)
434 * DTK_SPECIAL - leading "+" or "-" followed by text
435 * DTK_TZ - leading "+" or "-" followed by digits (also eats ':' or '.')
437 * Note that some field types can hold unexpected items:
438 * DTK_NUMBER can hold date fields (yy.ddd)
439 * DTK_STRING can hold months (January) and time zones (PST)
440 * DTK_DATE can hold time zone names (America/New_York, GMT-8)
443 ParseDateTime(const char *timestr, char *workbuf, size_t buflen,
444 char **field, int *ftype, int maxfields, int *numfields)
447 const char *cp = timestr;
448 char *bufp = workbuf;
449 const char *bufend = workbuf + buflen;
452 * Set the character pointed-to by "bufptr" to "newchar", and increment
453 * "bufptr". "end" gives the end of the buffer -- we return an error if
454 * there is no space left to append a character to the buffer. Note that
455 * "bufptr" is evaluated twice.
457 #define APPEND_CHAR(bufptr, end, newchar) \
460 if (((bufptr) + 1) >= (end)) \
461 return DTERR_BAD_FORMAT; \
462 *(bufptr)++ = newchar; \
465 /* outer loop through fields */
468 /* Ignore spaces between fields */
469 if (isspace((unsigned char) *cp))
475 /* Record start of current field */
477 return DTERR_BAD_FORMAT;
480 /* leading digit? then date or time */
481 if (isdigit((unsigned char) *cp))
483 APPEND_CHAR(bufp, bufend, *cp++);
484 while (isdigit((unsigned char) *cp))
485 APPEND_CHAR(bufp, bufend, *cp++);
490 ftype[nf] = DTK_TIME;
491 APPEND_CHAR(bufp, bufend, *cp++);
492 while (isdigit((unsigned char) *cp) ||
493 (*cp == ':') || (*cp == '.'))
494 APPEND_CHAR(bufp, bufend, *cp++);
496 /* date field? allow embedded text month */
497 else if (*cp == '-' || *cp == '/' || *cp == '.')
499 /* save delimiting character to use later */
502 APPEND_CHAR(bufp, bufend, *cp++);
503 /* second field is all digits? then no embedded text month */
504 if (isdigit((unsigned char) *cp))
506 ftype[nf] = ((delim == '.') ? DTK_NUMBER : DTK_DATE);
507 while (isdigit((unsigned char) *cp))
508 APPEND_CHAR(bufp, bufend, *cp++);
511 * insist that the delimiters match to get a three-field
516 ftype[nf] = DTK_DATE;
517 APPEND_CHAR(bufp, bufend, *cp++);
518 while (isdigit((unsigned char) *cp) || *cp == delim)
519 APPEND_CHAR(bufp, bufend, *cp++);
524 ftype[nf] = DTK_DATE;
525 while (isalnum((unsigned char) *cp) || *cp == delim)
526 APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++));
531 * otherwise, number only and will determine year, month, day, or
532 * concatenated fields later...
535 ftype[nf] = DTK_NUMBER;
537 /* Leading decimal point? Then fractional seconds... */
540 APPEND_CHAR(bufp, bufend, *cp++);
541 while (isdigit((unsigned char) *cp))
542 APPEND_CHAR(bufp, bufend, *cp++);
544 ftype[nf] = DTK_NUMBER;
548 * text? then date string, month, day of week, special, or timezone
550 else if (isalpha((unsigned char) *cp))
554 ftype[nf] = DTK_STRING;
555 APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++));
556 while (isalpha((unsigned char) *cp))
557 APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++));
560 * Dates can have embedded '-', '/', or '.' separators. It could
561 * also be a timezone name containing embedded '/', '+', '-', '_',
562 * or ':' (but '_' or ':' can't be the first punctuation). If the
563 * next character is a digit or '+', we need to check whether what
564 * we have so far is a recognized non-timezone keyword --- if so,
565 * don't believe that this is the start of a timezone.
568 if (*cp == '-' || *cp == '/' || *cp == '.')
570 else if (*cp == '+' || isdigit((unsigned char) *cp))
572 *bufp = '\0'; /* null-terminate current field value */
573 /* we need search only the core token table, not TZ names */
574 if (datebsearch(field[nf], datetktbl, szdatetktbl) == NULL)
579 ftype[nf] = DTK_DATE;
582 APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++));
583 } while (*cp == '+' || *cp == '-' ||
584 *cp == '/' || *cp == '_' ||
585 *cp == '.' || *cp == ':' ||
586 isalnum((unsigned char) *cp));
589 /* sign? then special or numeric timezone */
590 else if (*cp == '+' || *cp == '-')
592 APPEND_CHAR(bufp, bufend, *cp++);
593 /* soak up leading whitespace */
594 while (isspace((unsigned char) *cp))
596 /* numeric timezone? */
597 if (isdigit((unsigned char) *cp))
600 APPEND_CHAR(bufp, bufend, *cp++);
601 while (isdigit((unsigned char) *cp) ||
602 *cp == ':' || *cp == '.')
603 APPEND_CHAR(bufp, bufend, *cp++);
606 else if (isalpha((unsigned char) *cp))
608 ftype[nf] = DTK_SPECIAL;
609 APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++));
610 while (isalpha((unsigned char) *cp))
611 APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++));
613 /* otherwise something wrong... */
615 return DTERR_BAD_FORMAT;
617 /* ignore other punctuation but use as delimiter */
618 else if (ispunct((unsigned char) *cp))
623 /* otherwise, something is not right... */
625 return DTERR_BAD_FORMAT;
627 /* force in a delimiter after each field */
639 * Interpret previously parsed fields for general date and time.
640 * Return 0 if full date, 1 if only time, and negative DTERR code if problems.
641 * (Currently, all callers treat 1 as an error return too.)
643 * External format(s):
644 * "<weekday> <month>-<day>-<year> <hour>:<minute>:<second>"
645 * "Fri Feb-7-1997 15:23:27"
646 * "Feb-7-1997 15:23:27"
647 * "2-7-1997 15:23:27"
648 * "1997-2-7 15:23:27"
649 * "1997.038 15:23:27" (day of year 1-366)
650 * Also supports input in compact time:
653 * "20011225T040506.789-07"
655 * Use the system-provided functions to get the current time zone
656 * if not specified in the input string.
658 * If the date is outside the range of pg_time_t (in practice that could only
659 * happen if pg_time_t is just 32 bits), then assume UTC time zone - thomas
663 DecodeDateTime(char **field, int *ftype, int nf,
664 int *dtype, struct pg_tm * tm, fsec_t *fsec, int *tzp)
669 int ptype = 0; /* "prefix type" for ISO y2001m02d04 format */
674 bool haveTextMonth = FALSE;
675 bool is2digits = FALSE;
677 pg_tz *namedTz = NULL;
680 * We'll insist on at least all of the date fields, but initialize the
681 * remaining fields in case they are not set later...
688 /* don't know daylight savings time status apriori */
693 for (i = 0; i < nf; i++)
699 * Integral julian day with attached time zone?
700 * All other forms with JD will be separated into
701 * distinct fields, so we handle just this case here.
703 if (ptype == DTK_JULIAN)
709 return DTERR_BAD_FORMAT;
712 val = strtol(field[i], &cp, 10);
714 return DTERR_FIELD_OVERFLOW;
716 j2date(val, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
717 /* Get the time zone from the end of the string */
718 dterr = DecodeTimezone(cp, tzp);
722 tmask = DTK_DATE_M | DTK_TIME_M | DTK_M(TZ);
727 * Already have a date? Then this might be a time zone name
728 * with embedded punctuation (e.g. "America/New_York") or a
729 * run-together time with trailing time zone (e.g. hhmmss-zz).
730 * - thomas 2001-12-25
732 * We consider it a time zone if we already have month & day.
733 * This is to allow the form "mmm dd hhmmss tz year", which
734 * we've historically accepted.
736 else if (ptype != 0 ||
737 ((fmask & (DTK_M(MONTH) | DTK_M(DAY))) ==
738 (DTK_M(MONTH) | DTK_M(DAY))))
740 /* No time zone accepted? Then quit... */
742 return DTERR_BAD_FORMAT;
744 if (isdigit((unsigned char) *field[i]) || ptype != 0)
750 /* Sanity check; should not fail this test */
751 if (ptype != DTK_TIME)
752 return DTERR_BAD_FORMAT;
757 * Starts with a digit but we already have a time
758 * field? Then we are in trouble with a date and time
761 if ((fmask & DTK_TIME_M) == DTK_TIME_M)
762 return DTERR_BAD_FORMAT;
764 if ((cp = strchr(field[i], '-')) == NULL)
765 return DTERR_BAD_FORMAT;
767 /* Get the time zone from the end of the string */
768 dterr = DecodeTimezone(cp, tzp);
774 * Then read the rest of the field as a concatenated
777 dterr = DecodeNumberField(strlen(field[i]), field[i],
785 * modify tmask after returning from
786 * DecodeNumberField()
792 namedTz = pg_tzset(field[i]);
796 * We should return an error code instead of
797 * ereport'ing directly, but then there is no way
798 * to report the bad time zone name.
801 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
802 errmsg("time zone \"%s\" not recognized",
805 /* we'll apply the zone setting below */
811 dterr = DecodeDate(field[i], fmask,
812 &tmask, &is2digits, tm);
819 dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec);
824 * Check upper limit on hours; other limits checked in
827 /* test for > 24:00:00 */
828 if (tm->tm_hour > 24 ||
829 (tm->tm_hour == 24 && (tm->tm_min > 0 || tm->tm_sec > 0)))
830 return DTERR_FIELD_OVERFLOW;
838 return DTERR_BAD_FORMAT;
840 dterr = DecodeTimezone(field[i], &tz);
851 * Was this an "ISO date" with embedded field labels? An
852 * example is "y2001m02d04" - thomas 2001-02-04
860 val = strtol(field[i], &cp, 10);
862 return DTERR_FIELD_OVERFLOW;
865 * only a few kinds are allowed to have an embedded
876 return DTERR_BAD_FORMAT;
879 else if (*cp != '\0')
880 return DTERR_BAD_FORMAT;
892 * already have a month and hour? then assume
895 if ((fmask & DTK_M(MONTH)) != 0 &&
896 (fmask & DTK_M(HOUR)) != 0)
899 tmask = DTK_M(MINUTE);
904 tmask = DTK_M(MONTH);
920 tmask = DTK_M(MINUTE);
925 tmask = DTK_M(SECOND);
930 frac = strtod(cp, &cp);
932 return DTERR_BAD_FORMAT;
933 #ifdef HAVE_INT64_TIMESTAMP
934 *fsec = rint(frac * 1000000);
938 tmask = DTK_ALL_SECS_M;
944 dterr = DecodeTimezone(field[i], tzp);
951 * previous field was a label for "julian date"?
954 j2date(val, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
955 /* fractional Julian Day? */
960 time = strtod(cp, &cp);
962 return DTERR_BAD_FORMAT;
965 #ifdef HAVE_INT64_TIMESTAMP
966 dt2time(time * USECS_PER_DAY,
967 &tm->tm_hour, &tm->tm_min,
970 dt2time(time * SECS_PER_DAY, &tm->tm_hour,
971 &tm->tm_min, &tm->tm_sec, fsec);
977 /* previous field was "t" for ISO time */
978 dterr = DecodeNumberField(strlen(field[i]), field[i],
979 (fmask | DTK_DATE_M),
984 if (tmask != DTK_TIME_M)
985 return DTERR_BAD_FORMAT;
989 return DTERR_BAD_FORMAT;
1001 flen = strlen(field[i]);
1002 cp = strchr(field[i], '.');
1004 /* Embedded decimal and no date yet? */
1005 if (cp != NULL && !(fmask & DTK_DATE_M))
1007 dterr = DecodeDate(field[i], fmask,
1008 &tmask, &is2digits, tm);
1012 /* embedded decimal and several digits before? */
1013 else if (cp != NULL && flen - strlen(cp) > 2)
1016 * Interpret as a concatenated date or time Set the
1017 * type field to allow decoding other fields later.
1018 * Example: 20011223 or 040506
1020 dterr = DecodeNumberField(flen, field[i], fmask,
1028 dterr = DecodeNumberField(flen, field[i], fmask,
1034 /* otherwise it is a single date/time field... */
1037 dterr = DecodeNumber(flen, field[i],
1038 haveTextMonth, fmask,
1049 type = DecodeSpecial(i, field[i], &val);
1050 if (type == IGNORE_DTF)
1053 tmask = DTK_M(type);
1061 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1062 errmsg("date/time value \"current\" is no longer supported")));
1064 return DTERR_BAD_FORMAT;
1068 tmask = (DTK_DATE_M | DTK_TIME_M | DTK_M(TZ));
1070 GetCurrentTimeUsec(tm, fsec, tzp);
1076 GetCurrentDateTime(tm);
1077 j2date(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - 1,
1078 &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
1087 GetCurrentDateTime(tm);
1096 GetCurrentDateTime(tm);
1097 j2date(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + 1,
1098 &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
1105 tmask = (DTK_TIME_M | DTK_M(TZ));
1123 * already have a (numeric) month? then see if we can
1126 if ((fmask & DTK_M(MONTH)) && !haveTextMonth &&
1127 !(fmask & DTK_M(DAY)) && tm->tm_mon >= 1 &&
1130 tm->tm_mday = tm->tm_mon;
1133 haveTextMonth = TRUE;
1140 * daylight savings time modifier (solves "MET DST"
1143 tmask |= DTK_M(DTZ);
1146 return DTERR_BAD_FORMAT;
1147 *tzp += val * MINS_PER_HOUR;
1153 * set mask for TZ here _or_ check for DTZ later when
1154 * getting default timezone
1159 return DTERR_BAD_FORMAT;
1160 *tzp = val * MINS_PER_HOUR;
1166 return DTERR_BAD_FORMAT;
1167 *tzp = val * MINS_PER_HOUR;
1193 * This is a filler field "t" indicating that the next
1194 * field is time. Try to verify that this is sensible.
1198 /* No preceding date? Then quit... */
1199 if ((fmask & DTK_DATE_M) != DTK_DATE_M)
1200 return DTERR_BAD_FORMAT;
1203 * We will need one of the following fields:
1204 * DTK_NUMBER should be hhmmss.fff
1205 * DTK_TIME should be hh:mm:ss.fff
1206 * DTK_DATE should be hhmmss-zz
1209 (ftype[i + 1] != DTK_NUMBER &&
1210 ftype[i + 1] != DTK_TIME &&
1211 ftype[i + 1] != DTK_DATE))
1212 return DTERR_BAD_FORMAT;
1220 * Before giving up and declaring error, check to see
1221 * if it is an all-alpha timezone name.
1223 namedTz = pg_tzset(field[i]);
1225 return DTERR_BAD_FORMAT;
1226 /* we'll apply the zone setting below */
1231 return DTERR_BAD_FORMAT;
1236 return DTERR_BAD_FORMAT;
1240 return DTERR_BAD_FORMAT;
1242 } /* end loop over fields */
1244 /* do final checking/adjustment of Y/M/D fields */
1245 dterr = ValidateDate(fmask, is2digits, bc, tm);
1250 if (mer != HR24 && tm->tm_hour > 12)
1251 return DTERR_FIELD_OVERFLOW;
1252 if (mer == AM && tm->tm_hour == 12)
1254 else if (mer == PM && tm->tm_hour != 12)
1257 /* do additional checking for full date specs... */
1258 if (*dtype == DTK_DATE)
1260 if ((fmask & DTK_DATE_M) != DTK_DATE_M)
1262 if ((fmask & DTK_TIME_M) == DTK_TIME_M)
1264 return DTERR_BAD_FORMAT;
1268 * If we had a full timezone spec, compute the offset (we could not do
1269 * it before, because we need the date to resolve DST status).
1271 if (namedTz != NULL)
1273 /* daylight savings time modifier disallowed with full TZ */
1274 if (fmask & DTK_M(DTZMOD))
1275 return DTERR_BAD_FORMAT;
1277 *tzp = DetermineTimeZoneOffset(tm, namedTz);
1280 /* timezone not specified? then find local timezone if possible */
1281 if (tzp != NULL && !(fmask & DTK_M(TZ)))
1284 * daylight savings time modifier but no standard timezone? then
1287 if (fmask & DTK_M(DTZMOD))
1288 return DTERR_BAD_FORMAT;
1290 *tzp = DetermineTimeZoneOffset(tm, session_timezone);
1298 /* DetermineTimeZoneOffset()
1300 * Given a struct pg_tm in which tm_year, tm_mon, tm_mday, tm_hour, tm_min, and
1301 * tm_sec fields are set, attempt to determine the applicable time zone
1302 * (ie, regular or daylight-savings time) at that time. Set the struct pg_tm's
1303 * tm_isdst field accordingly, and return the actual timezone offset.
1305 * Note: it might seem that we should use mktime() for this, but bitter
1306 * experience teaches otherwise. This code is much faster than most versions
1307 * of mktime(), anyway.
1310 DetermineTimeZoneOffset(struct pg_tm * tm, pg_tz *tzp)
1320 long int before_gmtoff,
1326 if (tzp == session_timezone && HasCTZSet)
1328 tm->tm_isdst = 0; /* for lack of a better idea */
1333 * First, generate the pg_time_t value corresponding to the given
1334 * y/m/d/h/m/s taken as GMT time. If this overflows, punt and decide the
1335 * timezone is GMT. (We only need to worry about overflow on machines
1336 * where pg_time_t is 32 bits.)
1338 if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
1340 date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - UNIX_EPOCH_JDATE;
1342 day = ((pg_time_t) date) * SECS_PER_DAY;
1343 if (day / SECS_PER_DAY != date)
1345 sec = tm->tm_sec + (tm->tm_min + tm->tm_hour * MINS_PER_HOUR) * SECS_PER_MINUTE;
1347 /* since sec >= 0, overflow could only be from +day to -mytime */
1348 if (mytime < 0 && day > 0)
1352 * Find the DST time boundary just before or following the target time. We
1353 * assume that all zones have GMT offsets less than 24 hours, and that DST
1354 * boundaries can't be closer together than 48 hours, so backing up 24
1355 * hours and finding the "next" boundary will work.
1357 prevtime = mytime - SECS_PER_DAY;
1358 if (mytime < 0 && prevtime > 0)
1361 res = pg_next_dst_boundary(&prevtime,
1362 &before_gmtoff, &before_isdst,
1364 &after_gmtoff, &after_isdst,
1367 goto overflow; /* failure? */
1371 /* Non-DST zone, life is simple */
1372 tm->tm_isdst = before_isdst;
1373 return -(int) before_gmtoff;
1377 * Form the candidate pg_time_t values with local-time adjustment
1379 beforetime = mytime - before_gmtoff;
1380 if ((before_gmtoff > 0 &&
1381 mytime < 0 && beforetime > 0) ||
1382 (before_gmtoff <= 0 &&
1383 mytime > 0 && beforetime < 0))
1385 aftertime = mytime - after_gmtoff;
1386 if ((after_gmtoff > 0 &&
1387 mytime < 0 && aftertime > 0) ||
1388 (after_gmtoff <= 0 &&
1389 mytime > 0 && aftertime < 0))
1393 * If both before or both after the boundary time, we know what to do
1395 if (beforetime <= boundary && aftertime < boundary)
1397 tm->tm_isdst = before_isdst;
1398 return -(int) before_gmtoff;
1400 if (beforetime > boundary && aftertime >= boundary)
1402 tm->tm_isdst = after_isdst;
1403 return -(int) after_gmtoff;
1407 * It's an invalid or ambiguous time due to timezone transition. Prefer
1408 * the standard-time interpretation.
1410 if (after_isdst == 0)
1412 tm->tm_isdst = after_isdst;
1413 return -(int) after_gmtoff;
1415 tm->tm_isdst = before_isdst;
1416 return -(int) before_gmtoff;
1419 /* Given date is out of range, so assume UTC */
1426 * Interpret parsed string as time fields only.
1427 * Returns 0 if successful, DTERR code if bogus input detected.
1429 * Note that support for time zone is here for
1430 * SQL92 TIME WITH TIME ZONE, but it reveals
1431 * bogosity with SQL92 date/time standards, since
1432 * we must infer a time zone from current time.
1433 * - thomas 2000-03-10
1434 * Allow specifying date to get a better time zone,
1435 * if time zones are allowed. - thomas 2001-12-26
1438 DecodeTimeOnly(char **field, int *ftype, int nf,
1439 int *dtype, struct pg_tm * tm, fsec_t *fsec, int *tzp)
1444 int ptype = 0; /* "prefix type" for ISO h04mm05s06 format */
1448 bool is2digits = FALSE;
1451 pg_tz *namedTz = NULL;
1458 /* don't know daylight savings time status apriori */
1464 for (i = 0; i < nf; i++)
1471 * Time zone not allowed? Then should not accept dates or time
1472 * zones no matter what else!
1475 return DTERR_BAD_FORMAT;
1477 /* Under limited circumstances, we will accept a date... */
1478 if (i == 0 && nf >= 2 &&
1479 (ftype[nf - 1] == DTK_DATE || ftype[1] == DTK_TIME))
1481 dterr = DecodeDate(field[i], fmask,
1482 &tmask, &is2digits, tm);
1486 /* otherwise, this is a time and/or time zone */
1489 if (isdigit((unsigned char) *field[i]))
1494 * Starts with a digit but we already have a time
1495 * field? Then we are in trouble with time already...
1497 if ((fmask & DTK_TIME_M) == DTK_TIME_M)
1498 return DTERR_BAD_FORMAT;
1501 * Should not get here and fail. Sanity check only...
1503 if ((cp = strchr(field[i], '-')) == NULL)
1504 return DTERR_BAD_FORMAT;
1506 /* Get the time zone from the end of the string */
1507 dterr = DecodeTimezone(cp, tzp);
1513 * Then read the rest of the field as a concatenated
1516 dterr = DecodeNumberField(strlen(field[i]), field[i],
1517 (fmask | DTK_DATE_M),
1528 namedTz = pg_tzset(field[i]);
1532 * We should return an error code instead of
1533 * ereport'ing directly, but then there is no way
1534 * to report the bad time zone name.
1537 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1538 errmsg("time zone \"%s\" not recognized",
1541 /* we'll apply the zone setting below */
1549 dterr = DecodeTime(field[i], (fmask | DTK_DATE_M),
1560 return DTERR_BAD_FORMAT;
1562 dterr = DecodeTimezone(field[i], &tz);
1573 * Was this an "ISO time" with embedded field labels? An
1574 * example is "h04m05s06" - thomas 2001-02-04
1581 /* Only accept a date under limited circumstances */
1589 return DTERR_BAD_FORMAT;
1595 val = strtol(field[i], &cp, 10);
1596 if (errno == ERANGE)
1597 return DTERR_FIELD_OVERFLOW;
1600 * only a few kinds are allowed to have an embedded
1611 return DTERR_BAD_FORMAT;
1614 else if (*cp != '\0')
1615 return DTERR_BAD_FORMAT;
1621 tmask = DTK_M(YEAR);
1627 * already have a month and hour? then assume
1630 if ((fmask & DTK_M(MONTH)) != 0 &&
1631 (fmask & DTK_M(HOUR)) != 0)
1634 tmask = DTK_M(MINUTE);
1639 tmask = DTK_M(MONTH);
1650 tmask = DTK_M(HOUR);
1655 tmask = DTK_M(MINUTE);
1660 tmask = DTK_M(SECOND);
1665 frac = strtod(cp, &cp);
1667 return DTERR_BAD_FORMAT;
1668 #ifdef HAVE_INT64_TIMESTAMP
1669 *fsec = rint(frac * 1000000);
1673 tmask = DTK_ALL_SECS_M;
1679 dterr = DecodeTimezone(field[i], tzp);
1686 * previous field was a label for "julian date"?
1689 j2date(val, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
1694 time = strtod(cp, &cp);
1696 return DTERR_BAD_FORMAT;
1698 tmask |= DTK_TIME_M;
1699 #ifdef HAVE_INT64_TIMESTAMP
1700 dt2time(time * USECS_PER_DAY,
1701 &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
1703 dt2time(time * SECS_PER_DAY,
1704 &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
1710 /* previous field was "t" for ISO time */
1711 dterr = DecodeNumberField(strlen(field[i]), field[i],
1712 (fmask | DTK_DATE_M),
1719 if (tmask != DTK_TIME_M)
1720 return DTERR_BAD_FORMAT;
1724 return DTERR_BAD_FORMAT;
1736 flen = strlen(field[i]);
1737 cp = strchr(field[i], '.');
1739 /* Embedded decimal? */
1743 * Under limited circumstances, we will accept a
1746 if (i == 0 && nf >= 2 && ftype[nf - 1] == DTK_DATE)
1748 dterr = DecodeDate(field[i], fmask,
1749 &tmask, &is2digits, tm);
1753 /* embedded decimal and several digits before? */
1754 else if (flen - strlen(cp) > 2)
1757 * Interpret as a concatenated date or time Set
1758 * the type field to allow decoding other fields
1759 * later. Example: 20011223 or 040506
1761 dterr = DecodeNumberField(flen, field[i],
1762 (fmask | DTK_DATE_M),
1770 return DTERR_BAD_FORMAT;
1774 dterr = DecodeNumberField(flen, field[i],
1775 (fmask | DTK_DATE_M),
1782 /* otherwise it is a single date/time field... */
1785 dterr = DecodeNumber(flen, field[i],
1787 (fmask | DTK_DATE_M),
1798 type = DecodeSpecial(i, field[i], &val);
1799 if (type == IGNORE_DTF)
1802 tmask = DTK_M(type);
1810 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1811 errmsg("date/time value \"current\" is no longer supported")));
1812 return DTERR_BAD_FORMAT;
1818 GetCurrentTimeUsec(tm, fsec, NULL);
1822 tmask = (DTK_TIME_M | DTK_M(TZ));
1831 return DTERR_BAD_FORMAT;
1839 * daylight savings time modifier (solves "MET DST"
1842 tmask |= DTK_M(DTZ);
1845 return DTERR_BAD_FORMAT;
1846 *tzp += val * MINS_PER_HOUR;
1852 * set mask for TZ here _or_ check for DTZ later when
1853 * getting default timezone
1858 return DTERR_BAD_FORMAT;
1859 *tzp = val * MINS_PER_HOUR;
1866 return DTERR_BAD_FORMAT;
1867 *tzp = val * MINS_PER_HOUR;
1891 * We will need one of the following fields:
1892 * DTK_NUMBER should be hhmmss.fff
1893 * DTK_TIME should be hh:mm:ss.fff
1894 * DTK_DATE should be hhmmss-zz
1897 (ftype[i + 1] != DTK_NUMBER &&
1898 ftype[i + 1] != DTK_TIME &&
1899 ftype[i + 1] != DTK_DATE))
1900 return DTERR_BAD_FORMAT;
1908 * Before giving up and declaring error, check to see
1909 * if it is an all-alpha timezone name.
1911 namedTz = pg_tzset(field[i]);
1913 return DTERR_BAD_FORMAT;
1914 /* we'll apply the zone setting below */
1919 return DTERR_BAD_FORMAT;
1924 return DTERR_BAD_FORMAT;
1928 return DTERR_BAD_FORMAT;
1930 } /* end loop over fields */
1932 /* do final checking/adjustment of Y/M/D fields */
1933 dterr = ValidateDate(fmask, is2digits, bc, tm);
1938 if (mer != HR24 && tm->tm_hour > 12)
1939 return DTERR_FIELD_OVERFLOW;
1940 if (mer == AM && tm->tm_hour == 12)
1942 else if (mer == PM && tm->tm_hour != 12)
1945 if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 ||
1946 tm->tm_sec < 0 || tm->tm_sec > 60 || tm->tm_hour > 24 ||
1947 /* test for > 24:00:00 */
1948 #ifdef HAVE_INT64_TIMESTAMP
1949 (tm->tm_hour == 24 && (tm->tm_min > 0 || tm->tm_sec > 0 ||
1950 *fsec > INT64CONST(0))) ||
1951 *fsec < INT64CONST(0) || *fsec >= USECS_PER_SEC
1953 (tm->tm_hour == 24 && (tm->tm_min > 0 || tm->tm_sec > 0 ||
1955 *fsec < 0 || *fsec >= 1
1958 return DTERR_FIELD_OVERFLOW;
1960 if ((fmask & DTK_TIME_M) != DTK_TIME_M)
1961 return DTERR_BAD_FORMAT;
1964 * If we had a full timezone spec, compute the offset (we could not do it
1965 * before, because we may need the date to resolve DST status).
1967 if (namedTz != NULL)
1971 /* daylight savings time modifier disallowed with full TZ */
1972 if (fmask & DTK_M(DTZMOD))
1973 return DTERR_BAD_FORMAT;
1975 /* if non-DST zone, we do not need to know the date */
1976 if (pg_get_timezone_offset(namedTz, &gmtoff))
1978 *tzp = -(int) gmtoff;
1982 /* a date has to be specified */
1983 if ((fmask & DTK_DATE_M) != DTK_DATE_M)
1984 return DTERR_BAD_FORMAT;
1985 *tzp = DetermineTimeZoneOffset(tm, namedTz);
1989 /* timezone not specified? then find local timezone if possible */
1990 if (tzp != NULL && !(fmask & DTK_M(TZ)))
1996 * daylight savings time modifier but no standard timezone? then error
1998 if (fmask & DTK_M(DTZMOD))
1999 return DTERR_BAD_FORMAT;
2001 if ((fmask & DTK_DATE_M) == 0)
2002 GetCurrentDateTime(tmp);
2005 tmp->tm_year = tm->tm_year;
2006 tmp->tm_mon = tm->tm_mon;
2007 tmp->tm_mday = tm->tm_mday;
2009 tmp->tm_hour = tm->tm_hour;
2010 tmp->tm_min = tm->tm_min;
2011 tmp->tm_sec = tm->tm_sec;
2012 *tzp = DetermineTimeZoneOffset(tmp, session_timezone);
2013 tm->tm_isdst = tmp->tm_isdst;
2020 * Decode date string which includes delimiters.
2021 * Return 0 if okay, a DTERR code if not.
2023 * str: field to be parsed
2024 * fmask: bitmask for field types already seen
2025 * *tmask: receives bitmask for fields found here
2026 * *is2digits: set to TRUE if we find 2-digit year
2027 * *tm: field values are stored into appropriate members of this struct
2030 DecodeDate(char *str, int fmask, int *tmask, bool *is2digits,
2038 bool haveTextMonth = FALSE;
2042 char *field[MAXDATEFIELDS];
2046 /* parse this string... */
2047 while (*str != '\0' && nf < MAXDATEFIELDS)
2049 /* skip field separators */
2050 while (!isalnum((unsigned char) *str))
2054 if (isdigit((unsigned char) *str))
2056 while (isdigit((unsigned char) *str))
2059 else if (isalpha((unsigned char) *str))
2061 while (isalpha((unsigned char) *str))
2065 /* Just get rid of any non-digit, non-alpha characters... */
2071 /* look first for text fields, since that will be unambiguous month */
2072 for (i = 0; i < nf; i++)
2074 if (isalpha((unsigned char) *field[i]))
2076 type = DecodeSpecial(i, field[i], &val);
2077 if (type == IGNORE_DTF)
2080 dmask = DTK_M(type);
2085 haveTextMonth = TRUE;
2089 return DTERR_BAD_FORMAT;
2092 return DTERR_BAD_FORMAT;
2097 /* mark this field as being completed */
2102 /* now pick up remaining numeric fields */
2103 for (i = 0; i < nf; i++)
2105 if (field[i] == NULL)
2108 if ((len = strlen(field[i])) <= 0)
2109 return DTERR_BAD_FORMAT;
2111 dterr = DecodeNumber(len, field[i], haveTextMonth, fmask,
2118 return DTERR_BAD_FORMAT;
2124 if ((fmask & ~(DTK_M(DOY) | DTK_M(TZ))) != DTK_DATE_M)
2125 return DTERR_BAD_FORMAT;
2127 /* validation of the field values must wait until ValidateDate() */
2133 * Check valid year/month/day values, handle BC and DOY cases
2134 * Return 0 if okay, a DTERR code if not.
2137 ValidateDate(int fmask, bool is2digits, bool bc, struct pg_tm * tm)
2139 if (fmask & DTK_M(YEAR))
2143 /* there is no year zero in AD/BC notation */
2144 if (tm->tm_year <= 0)
2145 return DTERR_FIELD_OVERFLOW;
2146 /* internally, we represent 1 BC as year zero, 2 BC as -1, etc */
2147 tm->tm_year = -(tm->tm_year - 1);
2151 /* allow 2-digit input for 1970-2069 AD; 00 is allowed */
2152 if (tm->tm_year < 0) /* just paranoia */
2153 return DTERR_FIELD_OVERFLOW;
2154 if (tm->tm_year < 70)
2155 tm->tm_year += 2000;
2156 else if (tm->tm_year < 100)
2157 tm->tm_year += 1900;
2161 /* there is no year zero in AD/BC notation */
2162 if (tm->tm_year <= 0)
2163 return DTERR_FIELD_OVERFLOW;
2167 /* now that we have correct year, decode DOY */
2168 if (fmask & DTK_M(DOY))
2170 j2date(date2j(tm->tm_year, 1, 1) + tm->tm_yday - 1,
2171 &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
2174 /* check for valid month */
2175 if (fmask & DTK_M(MONTH))
2177 if (tm->tm_mon < 1 || tm->tm_mon > MONTHS_PER_YEAR)
2178 return DTERR_MD_FIELD_OVERFLOW;
2181 /* minimal check for valid day */
2182 if (fmask & DTK_M(DAY))
2184 if (tm->tm_mday < 1 || tm->tm_mday > 31)
2185 return DTERR_MD_FIELD_OVERFLOW;
2188 if ((fmask & DTK_DATE_M) == DTK_DATE_M)
2191 * Check for valid day of month, now that we know for sure the month
2192 * and year. Note we don't use MD_FIELD_OVERFLOW here, since it seems
2193 * unlikely that "Feb 29" is a YMD-order error.
2195 if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
2196 return DTERR_FIELD_OVERFLOW;
2204 * Decode time string which includes delimiters.
2205 * Return 0 if okay, a DTERR code if not.
2207 * Only check the lower limit on hours, since this same code can be
2208 * used to represent time spans.
2211 DecodeTime(char *str, int fmask, int *tmask, struct pg_tm * tm, fsec_t *fsec)
2215 *tmask = DTK_TIME_M;
2218 tm->tm_hour = strtol(str, &cp, 10);
2219 if (errno == ERANGE)
2220 return DTERR_FIELD_OVERFLOW;
2222 return DTERR_BAD_FORMAT;
2225 tm->tm_min = strtol(str, &cp, 10);
2226 if (errno == ERANGE)
2227 return DTERR_FIELD_OVERFLOW;
2233 else if (*cp != ':')
2234 return DTERR_BAD_FORMAT;
2239 tm->tm_sec = strtol(str, &cp, 10);
2240 if (errno == ERANGE)
2241 return DTERR_FIELD_OVERFLOW;
2244 else if (*cp == '.')
2249 frac = strtod(str, &cp);
2251 return DTERR_BAD_FORMAT;
2252 #ifdef HAVE_INT64_TIMESTAMP
2253 *fsec = rint(frac * 1000000);
2259 return DTERR_BAD_FORMAT;
2262 /* do a sanity check */
2263 #ifdef HAVE_INT64_TIMESTAMP
2264 if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 ||
2265 tm->tm_sec < 0 || tm->tm_sec > 60 || *fsec < INT64CONST(0) ||
2266 *fsec >= USECS_PER_SEC)
2267 return DTERR_FIELD_OVERFLOW;
2269 if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 ||
2270 tm->tm_sec < 0 || tm->tm_sec > 60 || *fsec < 0 || *fsec >= 1)
2271 return DTERR_FIELD_OVERFLOW;
2279 * Interpret plain numeric field as a date value in context.
2280 * Return 0 if okay, a DTERR code if not.
2283 DecodeNumber(int flen, char *str, bool haveTextMonth, int fmask,
2284 int *tmask, struct pg_tm * tm, fsec_t *fsec, bool *is2digits)
2293 val = strtol(str, &cp, 10);
2294 if (errno == ERANGE)
2295 return DTERR_FIELD_OVERFLOW;
2297 return DTERR_BAD_FORMAT;
2304 * More than two digits before decimal point? Then could be a date or
2305 * a run-together time: 2001.360 20011225 040506.789
2309 dterr = DecodeNumberField(flen, str,
2310 (fmask | DTK_DATE_M),
2318 frac = strtod(cp, &cp);
2320 return DTERR_BAD_FORMAT;
2321 #ifdef HAVE_INT64_TIMESTAMP
2322 *fsec = rint(frac * 1000000);
2327 else if (*cp != '\0')
2328 return DTERR_BAD_FORMAT;
2330 /* Special case for day of year */
2331 if (flen == 3 && (fmask & DTK_DATE_M) == DTK_M(YEAR) && val >= 1 &&
2334 *tmask = (DTK_M(DOY) | DTK_M(MONTH) | DTK_M(DAY));
2336 /* tm_mon and tm_mday can't actually be set yet ... */
2340 /* Switch based on what we have so far */
2341 switch (fmask & DTK_DATE_M)
2346 * Nothing so far; make a decision about what we think the input
2347 * is. There used to be lots of heuristics here, but the
2348 * consensus now is to be paranoid. It *must* be either
2349 * YYYY-MM-DD (with a more-than-two-digit year field), or the
2350 * field order defined by DateOrder.
2352 if (flen >= 3 || DateOrder == DATEORDER_YMD)
2354 *tmask = DTK_M(YEAR);
2357 else if (DateOrder == DATEORDER_DMY)
2359 *tmask = DTK_M(DAY);
2364 *tmask = DTK_M(MONTH);
2370 /* Must be at second field of YY-MM-DD */
2371 *tmask = DTK_M(MONTH);
2375 case (DTK_M(MONTH)):
2379 * We are at the first numeric field of a date that included a
2380 * textual month name. We want to support the variants
2381 * MON-DD-YYYY, DD-MON-YYYY, and YYYY-MON-DD as unambiguous
2382 * inputs. We will also accept MON-DD-YY or DD-MON-YY in
2383 * either DMY or MDY modes, as well as YY-MON-DD in YMD mode.
2385 if (flen >= 3 || DateOrder == DATEORDER_YMD)
2387 *tmask = DTK_M(YEAR);
2392 *tmask = DTK_M(DAY);
2398 /* Must be at second field of MM-DD-YY */
2399 *tmask = DTK_M(DAY);
2404 case (DTK_M(YEAR) | DTK_M(MONTH)):
2407 /* Need to accept DD-MON-YYYY even in YMD mode */
2408 if (flen >= 3 && *is2digits)
2410 /* Guess that first numeric field is day was wrong */
2411 *tmask = DTK_M(DAY); /* YEAR is already set */
2412 tm->tm_mday = tm->tm_year;
2418 *tmask = DTK_M(DAY);
2424 /* Must be at third field of YY-MM-DD */
2425 *tmask = DTK_M(DAY);
2431 /* Must be at second field of DD-MM-YY */
2432 *tmask = DTK_M(MONTH);
2436 case (DTK_M(MONTH) | DTK_M(DAY)):
2437 /* Must be at third field of DD-MM-YY or MM-DD-YY */
2438 *tmask = DTK_M(YEAR);
2442 case (DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY)):
2443 /* we have all the date, so it must be a time field */
2444 dterr = DecodeNumberField(flen, str, fmask,
2452 /* Anything else is bogus input */
2453 return DTERR_BAD_FORMAT;
2457 * When processing a year field, mark it for adjustment if it's only one
2460 if (*tmask == DTK_M(YEAR))
2461 *is2digits = (flen <= 2);
2467 /* DecodeNumberField()
2468 * Interpret numeric string as a concatenated date or time field.
2469 * Return a DTK token (>= 0) if successful, a DTERR code (< 0) if not.
2471 * Use the context of previously decoded fields to help with
2472 * the interpretation.
2475 DecodeNumberField(int len, char *str, int fmask,
2476 int *tmask, struct pg_tm * tm, fsec_t *fsec, bool *is2digits)
2481 * Have a decimal point? Then this is a date or something with a seconds
2484 if ((cp = strchr(str, '.')) != NULL)
2488 frac = strtod(cp, NULL);
2489 #ifdef HAVE_INT64_TIMESTAMP
2490 *fsec = rint(frac * 1000000);
2497 /* No decimal point and no complete date yet? */
2498 else if ((fmask & DTK_DATE_M) != DTK_DATE_M)
2503 *tmask = DTK_DATE_M;
2505 tm->tm_mday = atoi(str + 6);
2507 tm->tm_mon = atoi(str + 4);
2509 tm->tm_year = atoi(str + 0);
2516 *tmask = DTK_DATE_M;
2517 tm->tm_mday = atoi(str + 4);
2519 tm->tm_mon = atoi(str + 2);
2521 tm->tm_year = atoi(str + 0);
2528 /* not all time fields are specified? */
2529 if ((fmask & DTK_TIME_M) != DTK_TIME_M)
2534 *tmask = DTK_TIME_M;
2535 tm->tm_sec = atoi(str + 4);
2537 tm->tm_min = atoi(str + 2);
2539 tm->tm_hour = atoi(str + 0);
2546 *tmask = DTK_TIME_M;
2548 tm->tm_min = atoi(str + 2);
2550 tm->tm_hour = atoi(str + 0);
2556 return DTERR_BAD_FORMAT;
2561 * Interpret string as a numeric timezone.
2563 * Return 0 if okay (and set *tzp), a DTERR code if not okay.
2565 * NB: this must *not* ereport on failure; see commands/variable.c.
2567 * Note: we allow timezone offsets up to 13:59. There are places that
2568 * use +1300 summer time.
2571 DecodeTimezone(char *str, int *tzp)
2579 /* leading character must be "+" or "-" */
2580 if (*str != '+' && *str != '-')
2581 return DTERR_BAD_FORMAT;
2584 hr = strtol(str + 1, &cp, 10);
2585 if (errno == ERANGE)
2586 return DTERR_TZDISP_OVERFLOW;
2588 /* explicit delimiter? */
2592 min = strtol(cp + 1, &cp, 10);
2593 if (errno == ERANGE)
2594 return DTERR_TZDISP_OVERFLOW;
2598 sec = strtol(cp + 1, &cp, 10);
2599 if (errno == ERANGE)
2600 return DTERR_TZDISP_OVERFLOW;
2603 /* otherwise, might have run things together... */
2604 else if (*cp == '\0' && strlen(str) > 3)
2608 /* we could, but don't, support a run-together hhmmss format */
2613 if (hr < 0 || hr > 14)
2614 return DTERR_TZDISP_OVERFLOW;
2615 if (min < 0 || min >= 60)
2616 return DTERR_TZDISP_OVERFLOW;
2617 if (sec < 0 || sec >= 60)
2618 return DTERR_TZDISP_OVERFLOW;
2620 tz = (hr * MINS_PER_HOUR + min) * SECS_PER_MINUTE + sec;
2627 return DTERR_BAD_FORMAT;
2633 * Decode text string using lookup table.
2635 * Implement a cache lookup since it is likely that dates
2636 * will be related in format.
2638 * NB: this must *not* ereport on failure;
2639 * see commands/variable.c.
2642 DecodeSpecial(int field, char *lowtoken, int *val)
2647 tp = datecache[field];
2648 if (tp == NULL || strncmp(lowtoken, tp->token, TOKMAXLEN) != 0)
2650 tp = datebsearch(lowtoken, timezonetktbl, sztimezonetktbl);
2652 tp = datebsearch(lowtoken, datetktbl, szdatetktbl);
2656 type = UNKNOWN_FIELD;
2661 datecache[field] = tp;
2682 * Interpret previously parsed fields for general time interval.
2683 * Returns 0 if successful, DTERR code if bogus input detected.
2685 * Allow "date" field DTK_DATE since this could be just
2686 * an unsigned floating point number. - thomas 1997-11-16
2688 * Allow ISO-style time span, with implicit units on number of days
2689 * preceding an hh:mm:ss field. - thomas 1998-04-30
2692 DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct pg_tm * tm, fsec_t *fsec)
2694 bool is_before = FALSE;
2715 /* read through list backwards to pick up units before values */
2716 for (i = nf - 1; i >= 0; i--)
2721 dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec);
2730 * Timezone is a token with a leading sign character and
2731 * otherwise the same as a non-signed time field
2733 Assert(*field[i] == '-' || *field[i] == '+');
2736 * A single signed number ends up here, but will be rejected
2737 * by DecodeTime(). So, work this out to drop through to
2738 * DTK_NUMBER, which *can* tolerate this.
2741 while (*cp != '\0' && *cp != ':' && *cp != '.')
2744 DecodeTime(field[i] + 1, fmask, &tmask, tm, fsec) == 0)
2746 if (*field[i] == '-')
2748 /* flip the sign on all fields */
2749 tm->tm_hour = -tm->tm_hour;
2750 tm->tm_min = -tm->tm_min;
2751 tm->tm_sec = -tm->tm_sec;
2756 * Set the next type to be a day, if units are not
2757 * specified. This handles the case of '1 +02:03' since we
2758 * are reading right to left.
2764 else if (type == IGNORE_DTF)
2769 * Got a decimal point? Then assume some sort of
2770 * seconds specification
2774 else if (*cp == '\0')
2777 * Only a signed integer? Then must assume a
2778 * timezone-like usage
2788 val = strtol(field[i], &cp, 10);
2789 if (errno == ERANGE)
2790 return DTERR_FIELD_OVERFLOW;
2792 if (type == IGNORE_DTF)
2797 fval = strtod(cp, &cp);
2799 return DTERR_BAD_FORMAT;
2801 if (*field[i] == '-')
2804 else if (*cp == '\0')
2807 return DTERR_BAD_FORMAT;
2809 tmask = 0; /* DTK_M(type); */
2814 #ifdef HAVE_INT64_TIMESTAMP
2815 *fsec += val + fval;
2817 *fsec += (val + fval) * 1e-6;
2819 tmask = DTK_M(MICROSECOND);
2823 #ifdef HAVE_INT64_TIMESTAMP
2824 *fsec += (val + fval) * 1000;
2826 *fsec += (val + fval) * 1e-3;
2828 tmask = DTK_M(MILLISECOND);
2833 #ifdef HAVE_INT64_TIMESTAMP
2834 *fsec += fval * 1000000;
2840 * If any subseconds were specified, consider this
2841 * microsecond and millisecond input as well.
2844 tmask = DTK_M(SECOND);
2846 tmask = DTK_ALL_SECS_M;
2855 fval *= SECS_PER_MINUTE;
2858 #ifdef HAVE_INT64_TIMESTAMP
2859 *fsec += (fval - sec) * 1000000;
2861 *fsec += fval - sec;
2864 tmask = DTK_M(MINUTE);
2873 fval *= SECS_PER_HOUR;
2876 #ifdef HAVE_INT64_TIMESTAMP
2877 *fsec += (fval - sec) * 1000000;
2879 *fsec += fval - sec;
2882 tmask = DTK_M(HOUR);
2891 fval *= SECS_PER_DAY;
2894 #ifdef HAVE_INT64_TIMESTAMP
2895 *fsec += (fval - sec) * 1000000;
2897 *fsec += fval - sec;
2900 tmask = (fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY);
2904 tm->tm_mday += val * 7;
2910 extra_days = (int32) fval;
2911 tm->tm_mday += extra_days;
2917 fval *= SECS_PER_DAY;
2920 #ifdef HAVE_INT64_TIMESTAMP
2921 *fsec += (fval - sec) * 1000000;
2923 *fsec += fval - sec;
2927 tmask = (fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY);
2936 fval *= DAYS_PER_MONTH;
2944 fval *= SECS_PER_DAY;
2947 #ifdef HAVE_INT64_TIMESTAMP
2948 *fsec += (fval - sec) * 1000000;
2950 *fsec += fval - sec;
2954 tmask = DTK_M(MONTH);
2960 tm->tm_mon += fval * MONTHS_PER_YEAR;
2961 tmask = (fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR);
2965 tm->tm_year += val * 10;
2967 tm->tm_mon += fval * MONTHS_PER_YEAR * 10;
2968 tmask = (fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR);
2972 tm->tm_year += val * 100;
2974 tm->tm_mon += fval * MONTHS_PER_YEAR * 100;
2975 tmask = (fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR);
2978 case DTK_MILLENNIUM:
2979 tm->tm_year += val * 1000;
2981 tm->tm_mon += fval * MONTHS_PER_YEAR * 1000;
2982 tmask = (fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR);
2986 return DTERR_BAD_FORMAT;
2992 type = DecodeUnits(i, field[i], &val);
2993 if (type == IGNORE_DTF)
2996 tmask = 0; /* DTK_M(type); */
3009 tmask = (DTK_DATE_M || DTK_TIME_M);
3014 return DTERR_BAD_FORMAT;
3019 return DTERR_BAD_FORMAT;
3023 return DTERR_BAD_FORMAT;
3031 #ifdef HAVE_INT64_TIMESTAMP
3032 sec = *fsec / USECS_PER_SEC;
3033 *fsec -= sec * USECS_PER_SEC;
3035 TMODULO(*fsec, sec, 1.0);
3043 tm->tm_sec = -tm->tm_sec;
3044 tm->tm_min = -tm->tm_min;
3045 tm->tm_hour = -tm->tm_hour;
3046 tm->tm_mday = -tm->tm_mday;
3047 tm->tm_mon = -tm->tm_mon;
3048 tm->tm_year = -tm->tm_year;
3051 /* ensure that at least one time field has been found */
3053 return DTERR_BAD_FORMAT;
3060 * Decode text string using lookup table.
3061 * This routine supports time interval decoding
3062 * (hence, it need not recognize timezone names).
3065 DecodeUnits(int field, char *lowtoken, int *val)
3070 tp = deltacache[field];
3071 if (tp == NULL || strncmp(lowtoken, tp->token, TOKMAXLEN) != 0)
3073 tp = datebsearch(lowtoken, deltatktbl, szdeltatktbl);
3077 type = UNKNOWN_FIELD;
3082 deltacache[field] = tp;
3084 if (type == TZ || type == DTZ)
3091 } /* DecodeUnits() */
3094 * Report an error detected by one of the datetime input processing routines.
3096 * dterr is the error code, str is the original input string, datatype is
3097 * the name of the datatype we were trying to accept.
3099 * Note: it might seem useless to distinguish DTERR_INTERVAL_OVERFLOW and
3100 * DTERR_TZDISP_OVERFLOW from DTERR_FIELD_OVERFLOW, but SQL99 mandates three
3101 * separate SQLSTATE codes, so ...
3104 DateTimeParseError(int dterr, const char *str, const char *datatype)
3108 case DTERR_FIELD_OVERFLOW:
3110 (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
3111 errmsg("date/time field value out of range: \"%s\"",
3114 case DTERR_MD_FIELD_OVERFLOW:
3115 /* <nanny>same as above, but add hint about DateStyle</nanny> */
3117 (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
3118 errmsg("date/time field value out of range: \"%s\"",
3120 errhint("Perhaps you need a different \"datestyle\" setting.")));
3122 case DTERR_INTERVAL_OVERFLOW:
3124 (errcode(ERRCODE_INTERVAL_FIELD_OVERFLOW),
3125 errmsg("interval field value out of range: \"%s\"",
3128 case DTERR_TZDISP_OVERFLOW:
3130 (errcode(ERRCODE_INVALID_TIME_ZONE_DISPLACEMENT_VALUE),
3131 errmsg("time zone displacement out of range: \"%s\"",
3134 case DTERR_BAD_FORMAT:
3137 (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
3138 errmsg("invalid input syntax for type %s: \"%s\"",
3145 * Binary search -- from Knuth (6.2.1) Algorithm B. Special case like this
3146 * is WAY faster than the generic bsearch().
3148 static const datetkn *
3149 datebsearch(const char *key, const datetkn *base, int nel)
3151 const datetkn *last = base + nel - 1,
3155 while (last >= base)
3157 position = base + ((last - base) >> 1);
3158 result = key[0] - position->token[0];
3161 result = strncmp(key, position->token, TOKMAXLEN);
3166 last = position - 1;
3168 base = position + 1;
3174 * Append representation of a numeric timezone offset to str.
3177 EncodeTimezone(char *str, int tz, int style)
3184 min = sec / SECS_PER_MINUTE;
3185 sec -= min * SECS_PER_MINUTE;
3186 hour = min / MINS_PER_HOUR;
3187 min -= hour * MINS_PER_HOUR;
3190 /* TZ is negated compared to sign we wish to display ... */
3191 *str++ = (tz <= 0 ? '+' : '-');
3194 sprintf(str, "%02d:%02d:%02d", hour, min, sec);
3195 else if (min != 0 || style == USE_XSD_DATES)
3196 sprintf(str, "%02d:%02d", hour, min);
3198 sprintf(str, "%02d", hour);
3202 * Encode date as local time.
3205 EncodeDateOnly(struct pg_tm * tm, int style, char *str)
3207 if (tm->tm_mon < 1 || tm->tm_mon > MONTHS_PER_YEAR)
3214 /* compatible with ISO date formats */
3215 if (tm->tm_year > 0)
3216 sprintf(str, "%04d-%02d-%02d",
3217 tm->tm_year, tm->tm_mon, tm->tm_mday);
3219 sprintf(str, "%04d-%02d-%02d %s",
3220 -(tm->tm_year - 1), tm->tm_mon, tm->tm_mday, "BC");
3224 /* compatible with Oracle/Ingres date formats */
3225 if (DateOrder == DATEORDER_DMY)
3226 sprintf(str, "%02d/%02d", tm->tm_mday, tm->tm_mon);
3228 sprintf(str, "%02d/%02d", tm->tm_mon, tm->tm_mday);
3229 if (tm->tm_year > 0)
3230 sprintf(str + 5, "/%04d", tm->tm_year);
3232 sprintf(str + 5, "/%04d %s", -(tm->tm_year - 1), "BC");
3235 case USE_GERMAN_DATES:
3236 /* German-style date format */
3237 sprintf(str, "%02d.%02d", tm->tm_mday, tm->tm_mon);
3238 if (tm->tm_year > 0)
3239 sprintf(str + 5, ".%04d", tm->tm_year);
3241 sprintf(str + 5, ".%04d %s", -(tm->tm_year - 1), "BC");
3244 case USE_POSTGRES_DATES:
3246 /* traditional date-only style for Postgres */
3247 if (DateOrder == DATEORDER_DMY)
3248 sprintf(str, "%02d-%02d", tm->tm_mday, tm->tm_mon);
3250 sprintf(str, "%02d-%02d", tm->tm_mon, tm->tm_mday);
3251 if (tm->tm_year > 0)
3252 sprintf(str + 5, "-%04d", tm->tm_year);
3254 sprintf(str + 5, "-%04d %s", -(tm->tm_year - 1), "BC");
3259 } /* EncodeDateOnly() */
3263 * Encode time fields only.
3266 EncodeTimeOnly(struct pg_tm * tm, fsec_t fsec, int *tzp, int style, char *str)
3268 if (tm->tm_hour < 0 || tm->tm_hour > HOURS_PER_DAY)
3271 sprintf(str, "%02d:%02d", tm->tm_hour, tm->tm_min);
3274 * Print fractional seconds if any. The fractional field widths here
3275 * should be equal to the larger of MAX_TIME_PRECISION and
3276 * MAX_TIMESTAMP_PRECISION.
3280 #ifdef HAVE_INT64_TIMESTAMP
3281 sprintf(str + strlen(str), ":%02d.%06d", tm->tm_sec, fsec);
3283 sprintf(str + strlen(str), ":%013.10f", tm->tm_sec + fsec);
3285 TrimTrailingZeros(str);
3288 sprintf(str + strlen(str), ":%02d", tm->tm_sec);
3291 EncodeTimezone(str, *tzp, style);
3294 } /* EncodeTimeOnly() */
3298 * Encode date and time interpreted as local time.
3299 * Support several date styles:
3300 * Postgres - day mon hh:mm:ss yyyy tz
3301 * SQL - mm/dd/yyyy hh:mm:ss.ss tz
3302 * ISO - yyyy-mm-dd hh:mm:ss+/-tz
3303 * German - dd.mm.yyyy hh:mm:ss tz
3304 * XSD - yyyy-mm-ddThh:mm:ss.ss+/-tz
3305 * Variants (affects order of month and day for Postgres and SQL styles):
3307 * European - dd/mm/yyyy
3310 EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, char *str)
3315 * Why are we checking only the month field? Change this to an assert...
3316 * if (tm->tm_mon < 1 || tm->tm_mon > MONTHS_PER_YEAR) return -1;
3318 Assert(tm->tm_mon >= 1 && tm->tm_mon <= MONTHS_PER_YEAR);
3324 /* Compatible with ISO-8601 date formats */
3326 if (style == USE_ISO_DATES)
3327 sprintf(str, "%04d-%02d-%02d %02d:%02d",
3328 (tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1),
3329 tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min);
3331 sprintf(str, "%04d-%02d-%02dT%02d:%02d",
3332 (tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1),
3333 tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min);
3337 * Print fractional seconds if any. The field widths here should
3338 * be at least equal to MAX_TIMESTAMP_PRECISION.
3340 * In float mode, don't print fractional seconds before 1 AD,
3341 * since it's unlikely there's any precision left ...
3343 #ifdef HAVE_INT64_TIMESTAMP
3346 sprintf(str + strlen(str), ":%02d.%06d", tm->tm_sec, fsec);
3347 TrimTrailingZeros(str);
3350 if (fsec != 0 && tm->tm_year > 0)
3352 sprintf(str + strlen(str), ":%09.6f", tm->tm_sec + fsec);
3353 TrimTrailingZeros(str);
3357 sprintf(str + strlen(str), ":%02d", tm->tm_sec);
3360 * tzp == NULL indicates that we don't want *any* time zone info
3361 * in the output string. *tzn != NULL indicates that we have alpha
3362 * time zone info available. tm_isdst != -1 indicates that we have
3363 * a valid time zone translation.
3365 if (tzp != NULL && tm->tm_isdst >= 0)
3366 EncodeTimezone(str, *tzp, style);
3368 if (tm->tm_year <= 0)
3369 sprintf(str + strlen(str), " BC");
3373 /* Compatible with Oracle/Ingres date formats */
3375 if (DateOrder == DATEORDER_DMY)
3376 sprintf(str, "%02d/%02d", tm->tm_mday, tm->tm_mon);
3378 sprintf(str, "%02d/%02d", tm->tm_mon, tm->tm_mday);
3380 sprintf(str + 5, "/%04d %02d:%02d",
3381 (tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1),
3382 tm->tm_hour, tm->tm_min);
3385 * Print fractional seconds if any. The field widths here should
3386 * be at least equal to MAX_TIMESTAMP_PRECISION.
3388 * In float mode, don't print fractional seconds before 1 AD,
3389 * since it's unlikely there's any precision left ...
3391 #ifdef HAVE_INT64_TIMESTAMP
3394 sprintf(str + strlen(str), ":%02d.%06d", tm->tm_sec, fsec);
3395 TrimTrailingZeros(str);
3398 if (fsec != 0 && tm->tm_year > 0)
3400 sprintf(str + strlen(str), ":%09.6f", tm->tm_sec + fsec);
3401 TrimTrailingZeros(str);
3405 sprintf(str + strlen(str), ":%02d", tm->tm_sec);
3407 if (tzp != NULL && tm->tm_isdst >= 0)
3410 sprintf(str + strlen(str), " %.*s", MAXTZLEN, *tzn);
3412 EncodeTimezone(str, *tzp, style);
3415 if (tm->tm_year <= 0)
3416 sprintf(str + strlen(str), " BC");
3419 case USE_GERMAN_DATES:
3420 /* German variant on European style */
3422 sprintf(str, "%02d.%02d", tm->tm_mday, tm->tm_mon);
3424 sprintf(str + 5, ".%04d %02d:%02d",
3425 (tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1),
3426 tm->tm_hour, tm->tm_min);
3429 * Print fractional seconds if any. The field widths here should
3430 * be at least equal to MAX_TIMESTAMP_PRECISION.
3432 * In float mode, don't print fractional seconds before 1 AD,
3433 * since it's unlikely there's any precision left ...
3435 #ifdef HAVE_INT64_TIMESTAMP
3438 sprintf(str + strlen(str), ":%02d.%06d", tm->tm_sec, fsec);
3439 TrimTrailingZeros(str);
3442 if (fsec != 0 && tm->tm_year > 0)
3444 sprintf(str + strlen(str), ":%09.6f", tm->tm_sec + fsec);
3445 TrimTrailingZeros(str);
3449 sprintf(str + strlen(str), ":%02d", tm->tm_sec);
3451 if (tzp != NULL && tm->tm_isdst >= 0)
3454 sprintf(str + strlen(str), " %.*s", MAXTZLEN, *tzn);
3456 EncodeTimezone(str, *tzp, style);
3459 if (tm->tm_year <= 0)
3460 sprintf(str + strlen(str), " BC");
3463 case USE_POSTGRES_DATES:
3465 /* Backward-compatible with traditional Postgres abstime dates */
3467 day = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
3468 tm->tm_wday = j2day(day);
3470 strncpy(str, days[tm->tm_wday], 3);
3471 strcpy(str + 3, " ");
3473 if (DateOrder == DATEORDER_DMY)
3474 sprintf(str + 4, "%02d %3s", tm->tm_mday, months[tm->tm_mon - 1]);
3476 sprintf(str + 4, "%3s %02d", months[tm->tm_mon - 1], tm->tm_mday);
3478 sprintf(str + 10, " %02d:%02d", tm->tm_hour, tm->tm_min);
3481 * Print fractional seconds if any. The field widths here should
3482 * be at least equal to MAX_TIMESTAMP_PRECISION.
3484 * In float mode, don't print fractional seconds before 1 AD,
3485 * since it's unlikely there's any precision left ...
3487 #ifdef HAVE_INT64_TIMESTAMP
3490 sprintf(str + strlen(str), ":%02d.%06d", tm->tm_sec, fsec);
3491 TrimTrailingZeros(str);
3494 if (fsec != 0 && tm->tm_year > 0)
3496 sprintf(str + strlen(str), ":%09.6f", tm->tm_sec + fsec);
3497 TrimTrailingZeros(str);
3501 sprintf(str + strlen(str), ":%02d", tm->tm_sec);
3503 sprintf(str + strlen(str), " %04d",
3504 (tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1));
3506 if (tzp != NULL && tm->tm_isdst >= 0)
3509 sprintf(str + strlen(str), " %.*s", MAXTZLEN, *tzn);
3513 * We have a time zone, but no string version. Use the
3514 * numeric form, but be sure to include a leading space to
3515 * avoid formatting something which would be rejected by
3516 * the date/time parser later. - thomas 2001-10-19
3518 sprintf(str + strlen(str), " ");
3519 EncodeTimezone(str, *tzp, style);
3523 if (tm->tm_year <= 0)
3524 sprintf(str + strlen(str), " BC");
3533 * Interpret time structure as a delta time and convert to string.
3535 * Support "traditional Postgres" and ISO-8601 styles.
3536 * Actually, afaik ISO does not address time interval formatting,
3537 * but this looks similar to the spec for absolute date/time.
3538 * - thomas 1998-04-30
3541 EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
3543 bool is_before = FALSE;
3544 bool is_nonzero = FALSE;
3548 * The sign of year and month are guaranteed to match, since they are
3549 * stored internally as "month". But we'll need to check for is_before and
3550 * is_nonzero when determining the signs of hour/minute/seconds fields.
3554 /* compatible with ISO date formats */
3556 if (tm->tm_year != 0)
3558 sprintf(cp, "%d year%s",
3559 tm->tm_year, (tm->tm_year != 1) ? "s" : "");
3561 is_before = (tm->tm_year < 0);
3565 if (tm->tm_mon != 0)
3567 sprintf(cp, "%s%s%d mon%s", is_nonzero ? " " : "",
3568 (is_before && tm->tm_mon > 0) ? "+" : "",
3569 tm->tm_mon, (tm->tm_mon != 1) ? "s" : "");
3571 is_before = (tm->tm_mon < 0);
3575 if (tm->tm_mday != 0)
3577 sprintf(cp, "%s%s%d day%s", is_nonzero ? " " : "",
3578 (is_before && tm->tm_mday > 0) ? "+" : "",
3579 tm->tm_mday, (tm->tm_mday != 1) ? "s" : "");
3581 is_before = (tm->tm_mday < 0);
3585 if (!is_nonzero || tm->tm_hour != 0 || tm->tm_min != 0 ||
3586 tm->tm_sec != 0 || fsec != 0)
3588 int minus = (tm->tm_hour < 0 || tm->tm_min < 0 ||
3589 tm->tm_sec < 0 || fsec < 0);
3591 sprintf(cp, "%s%s%02d:%02d", is_nonzero ? " " : "",
3592 (minus ? "-" : (is_before ? "+" : "")),
3593 abs(tm->tm_hour), abs(tm->tm_min));
3595 /* Mark as "non-zero" since the fields are now filled in */
3598 /* need fractional seconds? */
3601 #ifdef HAVE_INT64_TIMESTAMP
3602 sprintf(cp, ":%02d", abs(tm->tm_sec));
3604 sprintf(cp, ".%06d", Abs(fsec));
3607 sprintf(cp, ":%012.9f", fabs(fsec));
3609 TrimTrailingZeros(cp);
3614 sprintf(cp, ":%02d", abs(tm->tm_sec));
3620 case USE_POSTGRES_DATES:
3625 if (tm->tm_year != 0)
3627 int year = tm->tm_year;
3629 if (tm->tm_year < 0)
3632 sprintf(cp, "%d year%s", year,
3633 (year != 1) ? "s" : "");
3635 is_before = (tm->tm_year < 0);
3639 if (tm->tm_mon != 0)
3641 int mon = tm->tm_mon;
3643 if (is_before || (!is_nonzero && tm->tm_mon < 0))
3646 sprintf(cp, "%s%d mon%s", is_nonzero ? " " : "", mon,
3647 (mon != 1) ? "s" : "");
3650 is_before = (tm->tm_mon < 0);
3654 if (tm->tm_mday != 0)
3656 int day = tm->tm_mday;
3658 if (is_before || (!is_nonzero && tm->tm_mday < 0))
3661 sprintf(cp, "%s%d day%s", is_nonzero ? " " : "", day,
3662 (day != 1) ? "s" : "");
3665 is_before = (tm->tm_mday < 0);
3668 if (tm->tm_hour != 0)
3670 int hour = tm->tm_hour;
3672 if (is_before || (!is_nonzero && tm->tm_hour < 0))
3675 sprintf(cp, "%s%d hour%s", is_nonzero ? " " : "", hour,
3676 (hour != 1) ? "s" : "");
3679 is_before = (tm->tm_hour < 0);
3683 if (tm->tm_min != 0)
3685 int min = tm->tm_min;
3687 if (is_before || (!is_nonzero && tm->tm_min < 0))
3690 sprintf(cp, "%s%d min%s", is_nonzero ? " " : "", min,
3691 (min != 1) ? "s" : "");
3694 is_before = (tm->tm_min < 0);
3698 /* fractional seconds? */
3703 #ifdef HAVE_INT64_TIMESTAMP
3705 if (is_before || (!is_nonzero && tm->tm_sec < 0))
3707 tm->tm_sec = -tm->tm_sec;
3711 else if (!is_nonzero && tm->tm_sec == 0 && fsec < 0)
3716 sprintf(cp, "%s%d.%02d secs", is_nonzero ? " " : "",
3717 tm->tm_sec, ((int) sec) / 10000);
3722 if (is_before || (!is_nonzero && fsec < 0))
3725 sprintf(cp, "%s%.2f secs", is_nonzero ? " " : "", sec);
3728 is_before = (fsec < 0);
3732 /* otherwise, integer seconds only? */
3733 else if (tm->tm_sec != 0)
3735 int sec = tm->tm_sec;
3737 if (is_before || (!is_nonzero && tm->tm_sec < 0))
3740 sprintf(cp, "%s%d sec%s", is_nonzero ? " " : "", sec,
3741 (sec != 1) ? "s" : "");
3744 is_before = (tm->tm_sec < 0);
3750 /* identically zero? then put in a unitless zero... */
3757 if (is_before && (style != USE_ISO_DATES))
3764 } /* EncodeInterval() */
3768 * We've been burnt by stupid errors in the ordering of the datetkn tables
3769 * once too often. Arrange to check them during postmaster start.
3772 CheckDateTokenTable(const char *tablename, const datetkn *base, int nel)
3777 for (i = 1; i < nel; i++)
3779 if (strncmp(base[i - 1].token, base[i].token, TOKMAXLEN) >= 0)
3781 elog(LOG, "ordering error in %s table: \"%.*s\" >= \"%.*s\"",
3783 TOKMAXLEN, base[i - 1].token,
3784 TOKMAXLEN, base[i].token);
3792 CheckDateTokenTables(void)
3796 Assert(UNIX_EPOCH_JDATE == date2j(1970, 1, 1));
3797 Assert(POSTGRES_EPOCH_JDATE == date2j(2000, 1, 1));
3799 ok &= CheckDateTokenTable("datetktbl", datetktbl, szdatetktbl);
3800 ok &= CheckDateTokenTable("deltatktbl", deltatktbl, szdeltatktbl);
3805 * This function gets called during timezone config file load or reload
3806 * to create the final array of timezone tokens. The argument array
3807 * is already sorted in name order. This data is in a temporary memory
3808 * context and must be copied to somewhere permanent.
3811 InstallTimeZoneAbbrevs(tzEntry *abbrevs, int n)
3817 * Copy the data into TopMemoryContext and convert to datetkn format.
3819 newtbl = (datetkn *) MemoryContextAlloc(TopMemoryContext,
3820 n * sizeof(datetkn));
3821 for (i = 0; i < n; i++)
3823 strncpy(newtbl[i].token, abbrevs[i].abbrev, TOKMAXLEN);
3824 newtbl[i].type = abbrevs[i].is_dst ? DTZ : TZ;
3825 TOVAL(&newtbl[i], abbrevs[i].offset / 60);
3828 /* Check the ordering, if testing */
3829 Assert(CheckDateTokenTable("timezone offset", newtbl, n));
3831 /* Now safe to replace existing table (if any) */
3833 pfree(timezonetktbl);
3834 timezonetktbl = newtbl;
3835 sztimezonetktbl = n;
3837 /* clear date cache in case it contains any stale timezone names */
3838 for (i = 0; i < MAXDATEFIELDS; i++)
3839 datecache[i] = NULL;
3843 * This set-returning function reads all the available time zone abbreviations
3844 * and returns a set of (abbrev, utc_offset, is_dst).
3847 pg_timezone_abbrevs(PG_FUNCTION_ARGS)
3849 FuncCallContext *funcctx;
3855 char buffer[TOKMAXLEN + 1];
3858 Interval *resInterval;
3860 /* stuff done only on the first call of the function */
3861 if (SRF_IS_FIRSTCALL())
3864 MemoryContext oldcontext;
3866 /* create a function context for cross-call persistence */
3867 funcctx = SRF_FIRSTCALL_INIT();
3870 * switch to memory context appropriate for multiple function calls
3872 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
3874 /* allocate memory for user context */
3875 pindex = (int *) palloc(sizeof(int));
3877 funcctx->user_fctx = (void *) pindex;
3880 * build tupdesc for result tuples. This must match this function's
3883 tupdesc = CreateTemplateTupleDesc(3, false);
3884 TupleDescInitEntry(tupdesc, (AttrNumber) 1, "abbrev",
3886 TupleDescInitEntry(tupdesc, (AttrNumber) 2, "utc_offset",
3887 INTERVALOID, -1, 0);
3888 TupleDescInitEntry(tupdesc, (AttrNumber) 3, "is_dst",
3891 funcctx->tuple_desc = BlessTupleDesc(tupdesc);
3892 MemoryContextSwitchTo(oldcontext);
3895 /* stuff done on every call of the function */
3896 funcctx = SRF_PERCALL_SETUP();
3897 pindex = (int *) funcctx->user_fctx;
3899 if (*pindex >= sztimezonetktbl)
3900 SRF_RETURN_DONE(funcctx);
3902 MemSet(nulls, 0, sizeof(nulls));
3905 * Convert name to text, using upcasing conversion that is the inverse of
3906 * what ParseDateTime() uses.
3908 strncpy(buffer, timezonetktbl[*pindex].token, TOKMAXLEN);
3909 buffer[TOKMAXLEN] = '\0'; /* may not be null-terminated */
3910 for (p = (unsigned char *) buffer; *p; p++)
3911 *p = pg_toupper(*p);
3913 values[0] = DirectFunctionCall1(textin, CStringGetDatum(buffer));
3915 MemSet(&tm, 0, sizeof(struct pg_tm));
3916 tm.tm_min = (-1) * FROMVAL(&timezonetktbl[*pindex]);
3917 resInterval = (Interval *) palloc(sizeof(Interval));
3918 tm2interval(&tm, 0, resInterval);
3919 values[1] = IntervalPGetDatum(resInterval);
3921 Assert(timezonetktbl[*pindex].type == DTZ ||
3922 timezonetktbl[*pindex].type == TZ);
3923 values[2] = BoolGetDatum(timezonetktbl[*pindex].type == DTZ);
3927 tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
3928 result = HeapTupleGetDatum(tuple);
3930 SRF_RETURN_NEXT(funcctx, result);
3934 * This set-returning function reads all the available full time zones
3935 * and returns a set of (name, abbrev, utc_offset, is_dst).
3938 pg_timezone_names(PG_FUNCTION_ARGS)
3940 MemoryContext oldcontext;
3941 FuncCallContext *funcctx;
3952 Interval *resInterval;
3955 /* stuff done only on the first call of the function */
3956 if (SRF_IS_FIRSTCALL())
3960 /* create a function context for cross-call persistence */
3961 funcctx = SRF_FIRSTCALL_INIT();
3964 * switch to memory context appropriate for multiple function calls
3966 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
3968 /* initialize timezone scanning code */
3969 tzenum = pg_tzenumerate_start();
3970 funcctx->user_fctx = (void *) tzenum;
3973 * build tupdesc for result tuples. This must match this function's
3976 tupdesc = CreateTemplateTupleDesc(4, false);
3977 TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
3979 TupleDescInitEntry(tupdesc, (AttrNumber) 2, "abbrev",
3981 TupleDescInitEntry(tupdesc, (AttrNumber) 3, "utc_offset",
3982 INTERVALOID, -1, 0);
3983 TupleDescInitEntry(tupdesc, (AttrNumber) 4, "is_dst",
3986 funcctx->tuple_desc = BlessTupleDesc(tupdesc);
3987 MemoryContextSwitchTo(oldcontext);
3990 /* stuff done on every call of the function */
3991 funcctx = SRF_PERCALL_SETUP();
3992 tzenum = (pg_tzenum *) funcctx->user_fctx;
3994 /* search for another zone to display */
3997 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
3998 tz = pg_tzenumerate_next(tzenum);
3999 MemoryContextSwitchTo(oldcontext);
4003 pg_tzenumerate_end(tzenum);
4004 funcctx->user_fctx = NULL;
4005 SRF_RETURN_DONE(funcctx);
4008 /* Convert now() to local time in this zone */
4009 if (timestamp2tm(GetCurrentTransactionStartTimestamp(),
4010 &tzoff, &tm, &fsec, &tzn, tz) != 0)
4011 continue; /* ignore if conversion fails */
4013 /* Ignore zic's rather silly "Factory" time zone */
4014 if (tzn && strcmp(tzn, "Local time zone must be set--see zic manual page") == 0)
4017 /* Found a displayable zone */
4021 MemSet(nulls, 0, sizeof(nulls));
4023 values[0] = DirectFunctionCall1(textin,
4024 CStringGetDatum(pg_get_timezone_name(tz)));
4026 values[1] = DirectFunctionCall1(textin,
4027 CStringGetDatum(tzn ? tzn : ""));
4029 MemSet(&itm, 0, sizeof(struct pg_tm));
4030 itm.tm_sec = -tzoff;
4031 resInterval = (Interval *) palloc(sizeof(Interval));
4032 tm2interval(&itm, 0, resInterval);
4033 values[2] = IntervalPGetDatum(resInterval);
4035 values[3] = BoolGetDatum(tm.tm_isdst > 0);
4037 tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
4038 result = HeapTupleGetDatum(tuple);
4040 SRF_RETURN_NEXT(funcctx, result);