From 022fd99668d7c758b1d17952c46b80529776ce0d Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 17 Oct 2006 21:03:21 +0000 Subject: [PATCH] Fix up some problems in handling of zic-style time zone names in datetime input routines. Remove the former "DecodePosixTimezone" function in favor of letting the zic code handle POSIX-style zone specs (see tzparse()). In particular this means that "PST+3" now means the same as "-03", whereas it used to mean "-11" --- the zone abbreviation is effectively just a noise word in this syntax. Make sure that all named and POSIX-style zone names will be parsed as a single token. Fix long-standing bogosities in printing and input of fractional-hour timezone offsets (since the tzparse() code will accept these, we'd better make 'em work). Also correct an error in the original coding of the zic-zone-name patch: in "timestamp without time zone" input, zone names are supposed to be allowed but ignored, but the coding was such that the zone changed the interpretation anyway. --- doc/src/sgml/datatype.sgml | 16 +- doc/src/sgml/datetime.sgml | 30 +-- src/backend/utils/adt/datetime.c | 378 ++++++++++++------------------ src/test/regress/expected/horology.out | 24 +- src/test/regress/expected/timestamp.out | 36 +-- src/test/regress/expected/timestamptz.out | 10 +- src/test/regress/sql/timestamp.sql | 12 +- src/test/regress/sql/timestamptz.sql | 8 +- 8 files changed, 214 insertions(+), 300 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 10da8d8d7b..2cc9770f65 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,4 +1,4 @@ - + Data Types @@ -1675,13 +1675,17 @@ SELECT b, char_length(b) FROM test2; PST - Pacific Standard Time + Abbreviation (for Pacific Standard Time) America/New_York Full time zone name + PST8PDT + POSIX-style time zone specification + + -8:00 ISO-8601 offset for PST @@ -2183,7 +2187,7 @@ January 8 04:05:06 1999 PST In addition to the timezone names and abbreviations, - PostgreSQL will accept time zone + PostgreSQL will accept POSIX-style time zone specifications of the form STDoffset or STDoffsetDST, where STD is a zone abbreviation, offset is a @@ -2221,12 +2225,6 @@ January 8 04:05:06 1999 PST - Note that timezone names are not used for date/time output - — all supported output formats use numeric timezone displays to - avoid ambiguity. - - - Neither full names nor abbreviations are hard-wired into the server; they are obtained from configuration files stored under .../share/timezone/ and .../share/timezonesets/ diff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgml index b275ff803e..3d80d254f0 100644 --- a/doc/src/sgml/datetime.sgml +++ b/doc/src/sgml/datetime.sgml @@ -1,4 +1,4 @@ - + Date/Time Support @@ -46,9 +46,9 @@ If the numeric token contains a dash (-), slash (/), or two or more dots (.), this is - a date string which may have a text month. In case of a slash - (/) it can also be a full time zone name like - America/New_York. + a date string which may have a text month. If a date token has + already been seen, it is instead interpreted as a time zone + name (e.g., America/New_York). @@ -64,7 +64,7 @@ If the token starts with a plus (+) or minus - (-), then it is either a time zone or a special + (-), then it is either a numeric time zone or a special field. @@ -73,30 +73,24 @@ - If the token is a text string, match up with possible strings. + If the token is a text string, match up with possible strings: - Do a binary-search table lookup for the token - as either a special string (e.g., today), - day (e.g., Thursday), - month (e.g., January), - or noise word (e.g., at, on). - - - - Set field values and bit mask for fields. - For example, set year, month, day for today, - and additionally hour, minute, second for now. + Do a binary-search table lookup for the token as a time zone + abbreviation. If not found, do a similar binary-search table lookup to match - the token with a time zone. + the token as either a special string (e.g., today), + day (e.g., Thursday), + month (e.g., January), + or noise word (e.g., at, on). diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c index fac642ddc9..cfb29dce56 100644 --- a/src/backend/utils/adt/datetime.c +++ b/src/backend/utils/adt/datetime.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.172 2006/10/04 00:29:58 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/datetime.c,v 1.173 2006/10/17 21:03:21 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -39,8 +39,6 @@ static int DecodeNumberField(int len, char *str, static int DecodeTime(char *str, int fmask, int *tmask, struct pg_tm * tm, fsec_t *fsec); static int DecodeTimezone(char *str, int *tzp); -static int DecodePosixTimezone(char *str, int *tzp); -static int DecodeZicTimezone(char *str, int *tzp, struct pg_tm * tm); static const datetkn *datebsearch(const char *key, const datetkn *base, int nel); static int DecodeDate(char *str, int fmask, int *tmask, struct pg_tm * tm); static void TrimTrailingZeros(char *str); @@ -173,7 +171,7 @@ static const datetkn datetktbl[] = { {"wednesday", DOW, 3}, {"weds", DOW, 3}, {"y", UNITS, DTK_YEAR}, /* "year" for ISO input */ - {YESTERDAY, RESERV, DTK_YESTERDAY}, /* yesterday midnight */ + {YESTERDAY, RESERV, DTK_YESTERDAY} /* yesterday midnight */ }; static int szdatetktbl = sizeof datetktbl / sizeof datetktbl[0]; @@ -243,7 +241,7 @@ static datetkn deltatktbl[] = { {DYEAR, UNITS, DTK_YEAR}, /* "year" relative */ {"years", UNITS, DTK_YEAR}, /* "years" relative */ {"yr", UNITS, DTK_YEAR}, /* "year" relative */ - {"yrs", UNITS, DTK_YEAR}, /* "years" relative */ + {"yrs", UNITS, DTK_YEAR} /* "years" relative */ }; static int szdeltatktbl = sizeof deltatktbl / sizeof deltatktbl[0]; @@ -427,14 +425,14 @@ TrimTrailingZeros(char *str) * DTK_NUMBER - digits and (possibly) a decimal point * DTK_DATE - digits and two delimiters, or digits and text * DTK_TIME - digits, colon delimiters, and possibly a decimal point - * DTK_STRING - text (no digits) + * DTK_STRING - text (no digits or punctuation) * DTK_SPECIAL - leading "+" or "-" followed by text - * DTK_TZ - leading "+" or "-" followed by digits + * DTK_TZ - leading "+" or "-" followed by digits (also eats ':' or '.') * * Note that some field types can hold unexpected items: * DTK_NUMBER can hold date fields (yy.ddd) * DTK_STRING can hold months (January) and time zones (PST) - * DTK_DATE can hold Posix time zones (GMT-8) + * DTK_DATE can hold time zone names (America/New_York, GMT-8) */ int ParseDateTime(const char *timestr, char *workbuf, size_t buflen, @@ -546,46 +544,42 @@ ParseDateTime(const char *timestr, char *workbuf, size_t buflen, */ else if (isalpha((unsigned char) *cp)) { + bool is_date; + ftype[nf] = DTK_STRING; APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++)); while (isalpha((unsigned char) *cp)) APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++)); /* - * Full date string with leading text month? Could also be a POSIX - * time zone... + * Dates can have embedded '-', '/', or '.' separators. It could + * also be a timezone name containing embedded '/', '+', '-', + * '_', or ':' (but '_' or ':' can't be the first punctuation). + * If the next character is a digit or '+', we need to check + * whether what we have so far is a recognized non-timezone + * keyword --- if so, don't believe that this is the start of + * a timezone. */ + is_date = false; if (*cp == '-' || *cp == '/' || *cp == '.') + is_date = true; + else if (*cp == '+' || isdigit((unsigned char) *cp)) { - char delim = *cp; - - if (*cp == '/') - { - ftype[nf] = DTK_TZ; - - /* - * set the first character of the region to upper case - * again - */ - field[nf][0] = pg_toupper((unsigned char) field[nf][0]); - - /* - * we have seen "Region/" of a POSIX timezone, continue to - * read the City part - */ - do - { - APPEND_CHAR(bufp, bufend, *cp++); - /* there is for example America/New_York */ - } while (isalpha((unsigned char) *cp) || *cp == '_'); - } - else + *bufp = '\0'; /* null-terminate current field value */ + /* we need search only the core token table, not TZ names */ + if (datebsearch(field[nf], datetktbl, szdatetktbl) == NULL) + is_date = true; + } + if (is_date) + { + ftype[nf] = DTK_DATE; + do { - ftype[nf] = DTK_DATE; - APPEND_CHAR(bufp, bufend, *cp++); - } - while (isdigit((unsigned char) *cp) || *cp == delim) - APPEND_CHAR(bufp, bufend, *cp++); + APPEND_CHAR(bufp, bufend, pg_tolower((unsigned char) *cp++)); + } while (*cp == '+' || *cp == '-' || + *cp == '/' || *cp == '_' || + *cp == '.' || *cp == ':' || + isalnum((unsigned char) *cp)); } } /* sign? then special or numeric timezone */ @@ -674,7 +668,7 @@ DecodeDateTime(char **field, int *ftype, int nf, bool haveTextMonth = FALSE; int is2digits = FALSE; int bc = FALSE; - int zicTzFnum = -1; + pg_tz *namedTz = NULL; /* * We'll insist on at least all of the date fields, but initialize the @@ -724,8 +718,8 @@ DecodeDateTime(char **field, int *ftype, int nf, break; } /*** - * Already have a date? Then this might be a POSIX time - * zone with an embedded dash (e.g. "PST-3" == "EST") or + * Already have a date? Then this might be a time zone name + * with embedded punctuation (e.g. "America/New_York") or * a run-together time with trailing time zone (e.g. hhmmss-zz). * - thomas 2001-12-25 ***/ @@ -774,7 +768,6 @@ DecodeDateTime(char **field, int *ftype, int nf, fsec, &is2digits); if (dterr < 0) return dterr; - ftype[i] = dterr; /* * modify tmask after returning from @@ -784,11 +777,20 @@ DecodeDateTime(char **field, int *ftype, int nf, } else { - dterr = DecodePosixTimezone(field[i], tzp); - if (dterr) - return dterr; - - ftype[i] = DTK_TZ; + namedTz = pg_tzset(field[i]); + if (!namedTz) + { + /* + * We should return an error code instead of + * ereport'ing directly, but then there is no + * way to report the bad time zone name. + */ + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("time zone \"%s\" not recognized", + field[i]))); + } + /* we'll apply the zone setting below */ tmask = DTK_M(TZ); } } @@ -822,34 +824,11 @@ DecodeDateTime(char **field, int *ftype, int nf, if (tzp == NULL) return DTERR_BAD_FORMAT; - if (strchr(field[i], '/') != NULL) - { - /* remember to apply the timezone at the end */ - zicTzFnum = i; - tmask = DTK_M(TZ); - break; - } - else - dterr = DecodeTimezone(field[i], &tz); + dterr = DecodeTimezone(field[i], &tz); if (dterr) return dterr; - - /* - * Already have a time zone? Then maybe this is the second - * field of a POSIX time: EST+3 (equivalent to PST) - */ - if (i > 0 && (fmask & DTK_M(TZ)) != 0 && - ftype[i - 1] == DTK_TZ && - isalpha((unsigned char) *field[i - 1])) - { - *tzp -= tz; - tmask = 0; - } - else - { - *tzp = tz; - tmask = DTK_M(TZ); - } + *tzp = tz; + tmask = DTK_M(TZ); } break; @@ -988,8 +967,6 @@ DecodeDateTime(char **field, int *ftype, int nf, fsec, &is2digits); if (dterr < 0) return dterr; - ftype[i] = dterr; - if (tmask != DTK_TIME_M) return DTERR_BAD_FORMAT; break; @@ -1030,7 +1007,6 @@ DecodeDateTime(char **field, int *ftype, int nf, fsec, &is2digits); if (dterr < 0) return dterr; - ftype[i] = dterr; } else if (flen > 4) { @@ -1039,7 +1015,6 @@ DecodeDateTime(char **field, int *ftype, int nf, fsec, &is2digits); if (dterr < 0) return dterr; - ftype[i] = dterr; } /* otherwise it is a single date/time field... */ else @@ -1168,7 +1143,6 @@ DecodeDateTime(char **field, int *ftype, int nf, if (tzp == NULL) return DTERR_BAD_FORMAT; *tzp = val * MINS_PER_HOUR; - ftype[i] = DTK_TZ; break; case TZ: @@ -1176,7 +1150,6 @@ DecodeDateTime(char **field, int *ftype, int nf, if (tzp == NULL) return DTERR_BAD_FORMAT; *tzp = val * MINS_PER_HOUR; - ftype[i] = DTK_TZ; break; case IGNORE_DTF: @@ -1308,18 +1281,17 @@ DecodeDateTime(char **field, int *ftype, int nf, if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]) return DTERR_FIELD_OVERFLOW; - if (zicTzFnum != -1) + /* + * If we had a full timezone spec, compute the offset (we could not + * do it before, because we need the date to resolve DST status). + */ + if (namedTz != NULL) { - Datum tsTz; - Timestamp timestamp; - - tm2timestamp(tm, *fsec, NULL, ×tamp); - tsTz = DirectFunctionCall2(timestamp_zone, - DirectFunctionCall1(textin, - CStringGetDatum(field[zicTzFnum])), - TimestampGetDatum(timestamp)); - timestamp2tm(DatumGetTimestampTz(tsTz), tzp, tm, fsec, NULL, NULL); - fmask &= ~DTK_M(TZ); + /* daylight savings time modifier disallowed with full TZ */ + if (fmask & DTK_M(DTZMOD)) + return DTERR_BAD_FORMAT; + + *tzp = DetermineTimeZoneOffset(tm, namedTz); } /* timezone not specified? then find local timezone if possible */ @@ -1492,6 +1464,7 @@ DecodeTimeOnly(char **field, int *ftype, int nf, int dterr; int is2digits = FALSE; int mer = HR24; + pg_tz *namedTz = NULL; *dtype = DTK_TIME; tm->tm_hour = 0; @@ -1567,10 +1540,20 @@ DecodeTimeOnly(char **field, int *ftype, int nf, } else { - dterr = DecodePosixTimezone(field[i], tzp); - if (dterr) - return dterr; - + namedTz = pg_tzset(field[i]); + if (!namedTz) + { + /* + * We should return an error code instead of + * ereport'ing directly, but then there is no + * way to report the bad time zone name. + */ + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("time zone \"%s\" not recognized", + field[i]))); + } + /* we'll apply the zone setting below */ ftype[i] = DTK_TZ; tmask = DTK_M(TZ); } @@ -1591,34 +1574,11 @@ DecodeTimeOnly(char **field, int *ftype, int nf, if (tzp == NULL) return DTERR_BAD_FORMAT; - if (strchr(field[i], '/') != NULL) - { - /* a date has to be specified */ - if ((fmask & DTK_DATE_M) != DTK_DATE_M) - return DTERR_BAD_FORMAT; - dterr = DecodeZicTimezone(field[i], &tz, tm); - } - else - dterr = DecodeTimezone(field[i], &tz); + dterr = DecodeTimezone(field[i], &tz); if (dterr) return dterr; - - /* - * Already have a time zone? Then maybe this is the second - * field of a POSIX time: EST+3 (equivalent to PST) - */ - if (i > 0 && (fmask & DTK_M(TZ)) != 0 && - ftype[i - 1] == DTK_TZ && - isalpha((unsigned char) *field[i - 1])) - { - *tzp -= tz; - tmask = 0; - } - else - { - *tzp = tz; - tmask = DTK_M(TZ); - } + *tzp = tz; + tmask = DTK_M(TZ); } break; @@ -1974,21 +1934,38 @@ DecodeTimeOnly(char **field, int *ftype, int nf, if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > 59 || tm->tm_sec < 0 || tm->tm_sec > 60 || tm->tm_hour > 24 || - /* test for > 24:00:00 */ - (tm->tm_hour == 24 && (tm->tm_min > 0 || tm->tm_sec > 0 || + /* test for > 24:00:00 */ #ifdef HAVE_INT64_TIMESTAMP + (tm->tm_hour == 24 && (tm->tm_min > 0 || tm->tm_sec > 0 || *fsec > INT64CONST(0))) || - *fsec < INT64CONST(0) || *fsec >= USECS_PER_SEC) - return DTERR_FIELD_OVERFLOW; + *fsec < INT64CONST(0) || *fsec >= USECS_PER_SEC #else + (tm->tm_hour == 24 && (tm->tm_min > 0 || tm->tm_sec > 0 || *fsec > 0)) || - *fsec < 0 || *fsec >= 1) - return DTERR_FIELD_OVERFLOW; + *fsec < 0 || *fsec >= 1 #endif + ) + return DTERR_FIELD_OVERFLOW; if ((fmask & DTK_TIME_M) != DTK_TIME_M) return DTERR_BAD_FORMAT; + /* + * If we had a full timezone spec, compute the offset (we could not + * do it before, because we need the date to resolve DST status). + */ + if (namedTz != NULL) + { + /* a date has to be specified */ + if ((fmask & DTK_DATE_M) != DTK_DATE_M) + return DTERR_BAD_FORMAT; + /* daylight savings time modifier disallowed with full TZ */ + if (fmask & DTK_M(DTZMOD)) + return DTERR_BAD_FORMAT; + + *tzp = DetermineTimeZoneOffset(tm, namedTz); + } + /* timezone not specified? then find local timezone if possible */ if (tzp != NULL && !(fmask & DTK_M(TZ))) { @@ -2548,7 +2525,8 @@ DecodeTimezone(char *str, int *tzp) { int tz; int hr, - min; + min, + sec = 0; char *cp; /* leading character must be "+" or "-" */ @@ -2567,22 +2545,32 @@ DecodeTimezone(char *str, int *tzp) min = strtol(cp + 1, &cp, 10); if (errno == ERANGE) return DTERR_TZDISP_OVERFLOW; + if (*cp == ':') + { + errno = 0; + sec = strtol(cp + 1, &cp, 10); + if (errno == ERANGE) + return DTERR_TZDISP_OVERFLOW; + } } /* otherwise, might have run things together... */ else if (*cp == '\0' && strlen(str) > 3) { min = hr % 100; hr = hr / 100; + /* we could, but don't, support a run-together hhmmss format */ } else min = 0; - if (hr < 0 || hr > 13) + if (hr < 0 || hr > 14) return DTERR_TZDISP_OVERFLOW; if (min < 0 || min >= 60) return DTERR_TZDISP_OVERFLOW; + if (sec < 0 || sec >= 60) + return DTERR_TZDISP_OVERFLOW; - tz = (hr * MINS_PER_HOUR + min) * SECS_PER_MINUTE; + tz = (hr * MINS_PER_HOUR + min) * SECS_PER_MINUTE + sec; if (*str == '-') tz = -tz; @@ -2594,75 +2582,6 @@ DecodeTimezone(char *str, int *tzp) return 0; } - -/* DecodePosixTimezone() - * Interpret string as a POSIX-compatible timezone: - * PST-hh:mm - * PST+h - * PST - * - thomas 2000-03-15 - * - * Return 0 if okay (and set *tzp), a DTERR code if not okay. - */ -static int -DecodePosixTimezone(char *str, int *tzp) -{ - int val, - tz; - int type; - int dterr; - char *cp; - char delim; - - /* advance over name part */ - cp = str; - while (*cp && isalpha((unsigned char) *cp)) - cp++; - - /* decode offset, if present */ - if (*cp) - { - dterr = DecodeTimezone(cp, &tz); - if (dterr) - return dterr; - } - else - tz = 0; - - /* decode name part. We must temporarily scribble on the input! */ - delim = *cp; - *cp = '\0'; - type = DecodeSpecial(MAXDATEFIELDS - 1, str, &val); - *cp = delim; - - switch (type) - { - case DTZ: - case TZ: - *tzp = (val * MINS_PER_HOUR) - tz; - break; - - default: - return DTERR_BAD_FORMAT; - } - - return 0; -} - -static int -DecodeZicTimezone(char *str, int *tzp, struct pg_tm * tm) -{ - struct pg_tz *tz; - - tz = pg_tzset(str); - if (!tz) - return DTERR_BAD_FORMAT; - - *tzp = DetermineTimeZoneOffset(tm, tz); - - return 0; -} - /* DecodeSpecial() * Decode text string using lookup table. * @@ -3194,6 +3113,33 @@ datebsearch(const char *key, const datetkn *base, int nel) return NULL; } +/* EncodeTimezone() + * Append representation of a numeric timezone offset to str. + */ +static void +EncodeTimezone(char *str, int tz) +{ + int hour, + min, + sec; + + sec = abs(tz); + min = sec / SECS_PER_MINUTE; + sec -= min * SECS_PER_MINUTE; + hour = min / MINS_PER_HOUR; + min -= hour * MINS_PER_HOUR; + + str += strlen(str); + /* TZ is negated compared to sign we wish to display ... */ + *str++ = (tz <= 0 ? '+' : '-'); + + if (sec != 0) + sprintf(str, "%02d:%02d:%02d", hour, min, sec); + else if (min != 0) + sprintf(str, "%02d:%02d", hour, min); + else + sprintf(str, "%02d", hour); +} /* EncodeDateOnly() * Encode date as local time. @@ -3284,14 +3230,7 @@ EncodeTimeOnly(struct pg_tm * tm, fsec_t fsec, int *tzp, int style, char *str) sprintf(str + strlen(str), ":%02d", tm->tm_sec); if (tzp != NULL) - { - int hour, - min; - - hour = -(*tzp / SECS_PER_HOUR); - min = (abs(*tzp) / MINS_PER_HOUR) % MINS_PER_HOUR; - sprintf(str + strlen(str), (min != 0) ? "%+03d:%02d" : "%+03d", hour, min); - } + EncodeTimezone(str, *tzp); return TRUE; } /* EncodeTimeOnly() */ @@ -3311,9 +3250,7 @@ EncodeTimeOnly(struct pg_tm * tm, fsec_t fsec, int *tzp, int style, char *str) int EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, char *str) { - int day, - hour, - min; + int day; /* * Why are we checking only the month field? Change this to an assert... @@ -3360,11 +3297,7 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, * a valid time zone translation. */ if (tzp != NULL && tm->tm_isdst >= 0) - { - hour = -(*tzp / SECS_PER_HOUR); - min = (abs(*tzp) / MINS_PER_HOUR) % MINS_PER_HOUR; - sprintf(str + strlen(str), (min != 0) ? "%+03d:%02d" : "%+03d", hour, min); - } + EncodeTimezone(str, *tzp); if (tm->tm_year <= 0) sprintf(str + strlen(str), " BC"); @@ -3410,11 +3343,7 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, if (*tzn != NULL) sprintf(str + strlen(str), " %.*s", MAXTZLEN, *tzn); else - { - hour = -(*tzp / SECS_PER_HOUR); - min = (abs(*tzp) / MINS_PER_HOUR) % MINS_PER_HOUR; - sprintf(str + strlen(str), (min != 0) ? "%+03d:%02d" : "%+03d", hour, min); - } + EncodeTimezone(str, *tzp); } if (tm->tm_year <= 0) @@ -3458,11 +3387,7 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, if (*tzn != NULL) sprintf(str + strlen(str), " %.*s", MAXTZLEN, *tzn); else - { - hour = -(*tzp / SECS_PER_HOUR); - min = (abs(*tzp) / MINS_PER_HOUR) % MINS_PER_HOUR; - sprintf(str + strlen(str), (min != 0) ? "%+03d:%02d" : "%+03d", hour, min); - } + EncodeTimezone(str, *tzp); } if (tm->tm_year <= 0) @@ -3524,9 +3449,8 @@ EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, * avoid formatting something which would be rejected by * the date/time parser later. - thomas 2001-10-19 */ - hour = -(*tzp / SECS_PER_HOUR); - min = (abs(*tzp) / MINS_PER_HOUR) % MINS_PER_HOUR; - sprintf(str + strlen(str), (min != 0) ? " %+03d:%02d" : " %+03d", hour, min); + sprintf(str + strlen(str), " "); + EncodeTimezone(str, *tzp); } } diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out index 15b397c74f..3aa8e3714b 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -448,7 +448,7 @@ SELECT '' AS "64", d1 + interval '1 year' AS one_year FROM TIMESTAMP_TBL; | Tue Feb 10 17:32:01 1998 | Tue Feb 10 17:32:01 1998 | Tue Feb 10 17:32:01 1998 - | Tue Feb 10 14:32:01 1998 + | Tue Feb 10 17:32:01 1998 | Wed Jun 10 18:32:01 1998 | Tue Feb 10 17:32:01 1998 | Wed Feb 11 17:32:01 1998 @@ -518,7 +518,7 @@ SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMP_TBL; | Sat Feb 10 17:32:01 1996 | Sat Feb 10 17:32:01 1996 | Sat Feb 10 17:32:01 1996 - | Sat Feb 10 14:32:01 1996 + | Sat Feb 10 17:32:01 1996 | Mon Jun 10 18:32:01 1996 | Sat Feb 10 17:32:01 1996 | Sun Feb 11 17:32:01 1996 @@ -936,8 +936,8 @@ SELECT t.d1 + i.f1 AS "102" FROM TIMESTAMP_TBL t, INTERVAL_TBL i Mon Feb 10 22:32:01 1997 Mon Feb 10 17:33:01 1997 Mon Feb 10 22:32:01 1997 - Mon Feb 10 14:33:01 1997 - Mon Feb 10 19:32:01 1997 + Mon Feb 10 17:33:01 1997 + Mon Feb 10 22:32:01 1997 Tue Jun 10 18:33:01 1997 Tue Jun 10 23:32:01 1997 Mon Feb 10 17:33:01 1997 @@ -1047,8 +1047,8 @@ SELECT t.d1 - i.f1 AS "102" FROM TIMESTAMP_TBL t, INTERVAL_TBL i Mon Feb 10 12:32:01 1997 Mon Feb 10 17:31:01 1997 Mon Feb 10 12:32:01 1997 - Mon Feb 10 14:31:01 1997 - Mon Feb 10 09:32:01 1997 + Mon Feb 10 17:31:01 1997 + Mon Feb 10 12:32:01 1997 Tue Jun 10 18:31:01 1997 Tue Jun 10 13:32:01 1997 Mon Feb 10 17:31:01 1997 @@ -2538,7 +2538,7 @@ SELECT '' AS "64", d1 AS us_postgres FROM TIMESTAMP_TBL; | Mon Feb 10 17:32:01 1997 | Mon Feb 10 17:32:01 1997 | Mon Feb 10 17:32:01 1997 - | Mon Feb 10 14:32:01 1997 + | Mon Feb 10 17:32:01 1997 | Tue Jun 10 18:32:01 1997 | Mon Feb 10 17:32:01 1997 | Tue Feb 11 17:32:01 1997 @@ -2621,7 +2621,7 @@ SELECT '' AS "64", d1 AS us_iso FROM TIMESTAMP_TBL; | 1997-02-10 17:32:01 | 1997-02-10 17:32:01 | 1997-02-10 17:32:01 - | 1997-02-10 14:32:01 + | 1997-02-10 17:32:01 | 1997-06-10 18:32:01 | 1997-02-10 17:32:01 | 1997-02-11 17:32:01 @@ -2710,7 +2710,7 @@ SELECT '' AS "64", d1 AS us_sql FROM TIMESTAMP_TBL; | 02/10/1997 17:32:01 | 02/10/1997 17:32:01 | 02/10/1997 17:32:01 - | 02/10/1997 14:32:01 + | 02/10/1997 17:32:01 | 06/10/1997 18:32:01 | 02/10/1997 17:32:01 | 02/11/1997 17:32:01 @@ -2806,7 +2806,7 @@ SELECT '' AS "65", d1 AS european_postgres FROM TIMESTAMP_TBL; | Mon 10 Feb 17:32:01 1997 | Mon 10 Feb 17:32:01 1997 | Mon 10 Feb 17:32:01 1997 - | Mon 10 Feb 14:32:01 1997 + | Mon 10 Feb 17:32:01 1997 | Tue 10 Jun 18:32:01 1997 | Mon 10 Feb 17:32:01 1997 | Tue 11 Feb 17:32:01 1997 @@ -2896,7 +2896,7 @@ SELECT '' AS "65", d1 AS european_iso FROM TIMESTAMP_TBL; | 1997-02-10 17:32:01 | 1997-02-10 17:32:01 | 1997-02-10 17:32:01 - | 1997-02-10 14:32:01 + | 1997-02-10 17:32:01 | 1997-06-10 18:32:01 | 1997-02-10 17:32:01 | 1997-02-11 17:32:01 @@ -2986,7 +2986,7 @@ SELECT '' AS "65", d1 AS european_sql FROM TIMESTAMP_TBL; | 10/02/1997 17:32:01 | 10/02/1997 17:32:01 | 10/02/1997 17:32:01 - | 10/02/1997 14:32:01 + | 10/02/1997 17:32:01 | 10/06/1997 18:32:01 | 10/02/1997 17:32:01 | 11/02/1997 17:32:01 diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index 5f3de34149..c145279e73 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -80,12 +80,12 @@ INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 -08:00'); INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 -0800'); INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 17:32:01 -07:00'); INSERT INTO TIMESTAMP_TBL VALUES ('2001-09-22T18:19:20'); --- POSIX format +-- POSIX format (note that the timezone abbrev is just decoration here) INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 08:14:01 GMT+8'); INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 13:14:02 GMT-1'); -INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT -2'); -INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 EST+3'); -INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 EST +2:00'); +INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT-2'); +INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 PST+8'); +INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 MST+7:00'); -- Variations for acceptable input formats INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997 -0800'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997'); @@ -101,9 +101,9 @@ INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC'); reset datestyle; INSERT INTO TIMESTAMP_TBL VALUES ('1997.041 17:32:01 UTC'); INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 America/New_York'); --- this fails +-- this fails (even though TZ is a no-op, we still look it up) INSERT INTO TIMESTAMP_TBL VALUES ('19970710 173201 America/Does_not_exist'); -ERROR: time zone "America/Does_not_exist" not recognized +ERROR: time zone "america/does_not_exist" not recognized -- Check date conversion and date arithmetic INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 18:32:01 PDT'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997'); @@ -179,7 +179,7 @@ SELECT '' AS "64", d1 FROM TIMESTAMP_TBL; | Mon Feb 10 17:32:01 1997 | Mon Feb 10 17:32:01 1997 | Mon Feb 10 17:32:01 1997 - | Mon Feb 10 14:32:01 1997 + | Mon Feb 10 17:32:01 1997 | Tue Jun 10 18:32:01 1997 | Mon Feb 10 17:32:01 1997 | Tue Feb 11 17:32:01 1997 @@ -248,7 +248,7 @@ SELECT '' AS "48", d1 FROM TIMESTAMP_TBL | Mon Feb 10 17:32:01 1997 | Mon Feb 10 17:32:01 1997 | Mon Feb 10 17:32:01 1997 - | Mon Feb 10 14:32:01 1997 + | Mon Feb 10 17:32:01 1997 | Tue Jun 10 18:32:01 1997 | Mon Feb 10 17:32:01 1997 | Tue Feb 11 17:32:01 1997 @@ -333,7 +333,7 @@ SELECT '' AS "63", d1 FROM TIMESTAMP_TBL | Mon Feb 10 17:32:01 1997 | Mon Feb 10 17:32:01 1997 | Mon Feb 10 17:32:01 1997 - | Mon Feb 10 14:32:01 1997 + | Mon Feb 10 17:32:01 1997 | Tue Jun 10 18:32:01 1997 | Mon Feb 10 17:32:01 1997 | Tue Feb 11 17:32:01 1997 @@ -424,7 +424,7 @@ SELECT '' AS "49", d1 FROM TIMESTAMP_TBL | Mon Feb 10 17:32:01 1997 | Mon Feb 10 17:32:01 1997 | Mon Feb 10 17:32:01 1997 - | Mon Feb 10 14:32:01 1997 + | Mon Feb 10 17:32:01 1997 | Tue Jun 10 18:32:01 1997 | Mon Feb 10 17:32:01 1997 | Tue Feb 11 17:32:01 1997 @@ -480,7 +480,7 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff | @ 39 days 17 hours 32 mins 1 sec | @ 39 days 17 hours 32 mins 1 sec | @ 39 days 17 hours 32 mins 1 sec - | @ 39 days 14 hours 32 mins 1 sec + | @ 39 days 17 hours 32 mins 1 sec | @ 159 days 18 hours 32 mins 1 sec | @ 39 days 17 hours 32 mins 1 sec | @ 40 days 17 hours 32 mins 1 sec @@ -550,7 +550,7 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff | @ 39 days 17 hours 32 mins 1 sec | @ 39 days 17 hours 32 mins 1 sec | @ 39 days 17 hours 32 mins 1 sec - | @ 39 days 14 hours 32 mins 1 sec + | @ 39 days 17 hours 32 mins 1 sec | @ 159 days 18 hours 32 mins 1 sec | @ 39 days 17 hours 32 mins 1 sec | @ 40 days 17 hours 32 mins 1 sec @@ -614,7 +614,7 @@ SELECT '' AS "54", d1 as "timestamp", | Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1 | Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1 | Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1 - | Mon Feb 10 14:32:01 1997 | 1997 | 2 | 10 | 14 | 32 | 1 + | Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1 | Tue Jun 10 18:32:01 1997 | 1997 | 6 | 10 | 18 | 32 | 1 | Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1 | Tue Feb 11 17:32:01 1997 | 1997 | 2 | 11 | 17 | 32 | 1 @@ -677,7 +677,7 @@ SELECT '' AS "54", d1 as "timestamp", | Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000 | Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000 | Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000 - | Mon Feb 10 14:32:01 1997 | 1 | 1000 | 1000000 + | Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000 | Tue Jun 10 18:32:01 1997 | 2 | 1000 | 1000000 | Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000 | Tue Feb 11 17:32:01 1997 | 1 | 1000 | 1000000 @@ -1025,7 +1025,7 @@ SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS') | 05 05 17 32 01 63121 | 05 05 17 32 01 63121 | 05 05 17 32 01 63121 - | 02 02 14 32 01 52321 + | 05 05 17 32 01 63121 | 06 06 18 32 01 66721 | 05 05 17 32 01 63121 | 05 05 17 32 01 63121 @@ -1096,7 +1096,7 @@ SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between qu | HH:MI:SS is 05:32:01 "text between quote marks" | HH:MI:SS is 05:32:01 "text between quote marks" | HH:MI:SS is 05:32:01 "text between quote marks" - | HH:MI:SS is 02:32:01 "text between quote marks" + | HH:MI:SS is 05:32:01 "text between quote marks" | HH:MI:SS is 06:32:01 "text between quote marks" | HH:MI:SS is 05:32:01 "text between quote marks" | HH:MI:SS is 05:32:01 "text between quote marks" @@ -1167,7 +1167,7 @@ SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS') | 17--text--32--text--01 | 17--text--32--text--01 | 17--text--32--text--01 - | 14--text--32--text--01 + | 17--text--32--text--01 | 18--text--32--text--01 | 17--text--32--text--01 | 17--text--32--text--01 @@ -1310,7 +1310,7 @@ SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. H | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm - | 1997 A.D. 1997 a.d. 1997 ad 02:32:01 P.M. 02:32:01 p.m. 02:32:01 pm + | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm | 1997 A.D. 1997 a.d. 1997 ad 06:32:01 P.M. 06:32:01 p.m. 06:32:01 pm | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm | 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index 4448ef6e20..4a17503324 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -75,12 +75,12 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 -08:00'); INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 -0800'); INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 17:32:01 -07:00'); INSERT INTO TIMESTAMPTZ_TBL VALUES ('2001-09-22T18:19:20'); --- POSIX format +-- POSIX format (note that the timezone abbrev is just decoration here) INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 08:14:01 GMT+8'); INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 13:14:02 GMT-1'); -INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT -2'); -INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 EST+3'); -INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 EST +2:00'); +INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT-2'); +INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 PST+8'); +INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 MST+7:00'); -- Variations for acceptable input formats INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997 -0800'); INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997'); @@ -111,7 +111,7 @@ SELECT '19970710 173201' AT TIME ZONE 'America/New_York'; (1 row) INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America/Does_not_exist'); -ERROR: time zone "America/Does_not_exist" not recognized +ERROR: time zone "america/does_not_exist" not recognized SELECT '19970710 173201' AT TIME ZONE 'America/Does_not_exist'; ERROR: time zone "America/Does_not_exist" not recognized -- Check date conversion and date arithmetic diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql index 31235704a9..34689564da 100644 --- a/src/test/regress/sql/timestamp.sql +++ b/src/test/regress/sql/timestamp.sql @@ -62,12 +62,12 @@ INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 -0800'); INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 17:32:01 -07:00'); INSERT INTO TIMESTAMP_TBL VALUES ('2001-09-22T18:19:20'); --- POSIX format +-- POSIX format (note that the timezone abbrev is just decoration here) INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 08:14:01 GMT+8'); INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 13:14:02 GMT-1'); -INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT -2'); -INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 EST+3'); -INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 EST +2:00'); +INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT-2'); +INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 PST+8'); +INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 MST+7:00'); -- Variations for acceptable input formats INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997 -0800'); @@ -83,12 +83,10 @@ INSERT INTO TIMESTAMP_TBL VALUES ('97FEB10 5:32:01PM UTC'); INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC'); reset datestyle; INSERT INTO TIMESTAMP_TBL VALUES ('1997.041 17:32:01 UTC'); - INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 America/New_York'); --- this fails +-- this fails (even though TZ is a no-op, we still look it up) INSERT INTO TIMESTAMP_TBL VALUES ('19970710 173201 America/Does_not_exist'); - -- Check date conversion and date arithmetic INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 18:32:01 PDT'); diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index 65af1b0417..fc597a6b2c 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -56,12 +56,12 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 -0800'); INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 17:32:01 -07:00'); INSERT INTO TIMESTAMPTZ_TBL VALUES ('2001-09-22T18:19:20'); --- POSIX format +-- POSIX format (note that the timezone abbrev is just decoration here) INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 08:14:01 GMT+8'); INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 13:14:02 GMT-1'); -INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT -2'); -INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 EST+3'); -INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 EST +2:00'); +INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT-2'); +INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 PST+8'); +INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 MST+7:00'); -- Variations for acceptable input formats INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997 -0800'); -- 2.11.0