diff options
Diffstat (limited to 'src')
24 files changed, 1990 insertions, 615 deletions
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index 073104d4bac..bb23b12c171 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -2695,24 +2695,39 @@ timetz_zone(PG_FUNCTION_ARGS) pg_tz *tzp; /* - * Look up the requested timezone. First we look in the date token table - * (to handle cases like "EST"), and if that fails, we look in the - * timezone database (to handle cases like "America/New_York"). (This - * matches the order in which timestamp input checks the cases; it's - * important because the timezone database unwisely uses a few zone names - * that are identical to offset abbreviations.) + * Look up the requested timezone. First we look in the timezone + * abbreviation table (to handle cases like "EST"), and if that fails, we + * look in the timezone database (to handle cases like + * "America/New_York"). (This matches the order in which timestamp input + * checks the cases; it's important because the timezone database unwisely + * uses a few zone names that are identical to offset abbreviations.) */ text_to_cstring_buffer(zone, tzname, sizeof(tzname)); + + /* DecodeTimezoneAbbrev requires lowercase input */ lowzone = downcase_truncate_identifier(tzname, strlen(tzname), false); - type = DecodeSpecial(0, lowzone, &val); + type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp); if (type == TZ || type == DTZ) - tz = val * MINS_PER_HOUR; + { + /* fixed-offset abbreviation */ + tz = -val; + } + else if (type == DYNTZ) + { + /* dynamic-offset abbreviation, resolve using current time */ + pg_time_t now = (pg_time_t) time(NULL); + struct pg_tm *tm; + + tm = pg_localtime(&now, tzp); + tz = DetermineTimeZoneAbbrevOffset(tm, tzname, tzp); + } else { + /* try it as a full zone name */ tzp = pg_tzset(tzname); if (tzp) { diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c index 7632d1177e6..4381a5ae437 100644 --- a/src/backend/utils/adt/datetime.c +++ b/src/backend/utils/adt/datetime.c @@ -50,6 +50,11 @@ static void AdjustFractSeconds(double frac, struct pg_tm * tm, fsec_t *fsec, int scale); static void AdjustFractDays(double frac, struct pg_tm * tm, fsec_t *fsec, int scale); +static int DetermineTimeZoneOffsetInternal(struct pg_tm * tm, pg_tz *tzp, + pg_time_t *tp); +static int DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t, const char *abbr, + pg_tz *tzp, int *isdst); +static pg_tz *FetchDynamicTimeZone(TimeZoneAbbrevTable *tbl, const datetkn *tp); const int day_tab[2][13] = @@ -70,41 +75,18 @@ const char *const days[] = {"Sunday", "Monday", "Tuesday", "Wednesday", *****************************************************************************/ /* - * Definitions for squeezing values into "value" - * We set aside a high bit for a sign, and scale the timezone offsets - * in minutes by a factor of 15 (so can represent quarter-hour increments). - */ -#define ABS_SIGNBIT ((char) 0200) -#define VALMASK ((char) 0177) -#define POS(n) (n) -#define NEG(n) ((n)|ABS_SIGNBIT) -#define SIGNEDCHAR(c) ((c)&ABS_SIGNBIT? -((c)&VALMASK): (c)) -#define FROMVAL(tp) (-SIGNEDCHAR((tp)->value) * 15) /* uncompress */ -#define TOVAL(tp, v) ((tp)->value = ((v) < 0? NEG((-(v))/15): POS(v)/15)) - -/* * datetktbl holds date/time keywords. * * Note that this table must be strictly alphabetically ordered to allow an * O(ln(N)) search algorithm to be used. * - * The token field is NOT guaranteed to be NULL-terminated. - * - * To keep this table reasonably small, we divide the value for TZ and DTZ - * entries by 15 (so they are on 15 minute boundaries) and truncate the token - * field at TOKMAXLEN characters. - * Formerly, we divided by 10 rather than 15 but there are a few time zones - * which are 30 or 45 minutes away from an even hour, most are on an hour - * boundary, and none on other boundaries. + * The token field must be NUL-terminated; we truncate entries to TOKMAXLEN + * characters to fit. * - * The static table contains no TZ or DTZ entries, rather those are loaded - * from configuration files and stored in timezonetktbl, which has the same - * format as the static datetktbl. + * The static table contains no TZ, DTZ, or DYNTZ entries; rather those + * are loaded from configuration files and stored in zoneabbrevtbl, whose + * abbrevs[] field has the same format as the static datetktbl. */ -static datetkn *timezonetktbl = NULL; - -static int sztimezonetktbl = 0; - static const datetkn datetktbl[] = { /* token, type, value */ {EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */ @@ -123,7 +105,7 @@ static const datetkn datetktbl[] = { {"december", MONTH, 12}, {"dow", RESERV, DTK_DOW}, /* day of week */ {"doy", RESERV, DTK_DOY}, /* day of year */ - {"dst", DTZMOD, 6}, + {"dst", DTZMOD, SECS_PER_HOUR}, {EPOCH, RESERV, DTK_EPOCH}, /* "epoch" reserved for system epoch time */ {"feb", MONTH, 2}, {"february", MONTH, 2}, @@ -185,6 +167,10 @@ static const datetkn datetktbl[] = { static int szdatetktbl = sizeof datetktbl / sizeof datetktbl[0]; +/* + * deltatktbl: same format as datetktbl, but holds keywords used to represent + * time units (eg, for intervals, and for EXTRACT). + */ static const datetkn deltatktbl[] = { /* token, type, value */ {"@", IGNORE_DTF, 0}, /* postgres relative prefix */ @@ -254,10 +240,16 @@ static const datetkn deltatktbl[] = { static int szdeltatktbl = sizeof deltatktbl / sizeof deltatktbl[0]; +static TimeZoneAbbrevTable *zoneabbrevtbl = NULL; + +/* Caches of recent lookup results in the above tables */ + static const datetkn *datecache[MAXDATEFIELDS] = {NULL}; static const datetkn *deltacache[MAXDATEFIELDS] = {NULL}; +static const datetkn *abbrevcache[MAXDATEFIELDS] = {NULL}; + /* * strtoi --- just like strtol, but returns int not long @@ -798,6 +790,9 @@ DecodeDateTime(char **field, int *ftype, int nf, bool is2digits = FALSE; bool bc = FALSE; pg_tz *namedTz = NULL; + pg_tz *abbrevTz = NULL; + pg_tz *valtz; + char *abbrev = NULL; struct pg_tm cur_tm; /* @@ -1194,7 +1189,10 @@ DecodeDateTime(char **field, int *ftype, int nf, case DTK_STRING: case DTK_SPECIAL: - type = DecodeSpecial(i, field[i], &val); + /* timezone abbrevs take precedence over built-in tokens */ + type = DecodeTimezoneAbbrev(i, field[i], &val, &valtz); + if (type == UNKNOWN_FIELD) + type = DecodeSpecial(i, field[i], &val); if (type == IGNORE_DTF) continue; @@ -1286,7 +1284,7 @@ DecodeDateTime(char **field, int *ftype, int nf, tm->tm_isdst = 1; if (tzp == NULL) return DTERR_BAD_FORMAT; - *tzp += val * MINS_PER_HOUR; + *tzp -= val; break; case DTZ: @@ -1299,17 +1297,23 @@ DecodeDateTime(char **field, int *ftype, int nf, tm->tm_isdst = 1; if (tzp == NULL) return DTERR_BAD_FORMAT; - *tzp = val * MINS_PER_HOUR; + *tzp = -val; break; case TZ: tm->tm_isdst = 0; if (tzp == NULL) return DTERR_BAD_FORMAT; - *tzp = val * MINS_PER_HOUR; + *tzp = -val; break; - case IGNORE_DTF: + case DYNTZ: + tmask |= DTK_M(TZ); + if (tzp == NULL) + return DTERR_BAD_FORMAT; + /* we'll determine the actual offset later */ + abbrevTz = valtz; + abbrev = field[i]; break; case AMPM: @@ -1419,7 +1423,20 @@ DecodeDateTime(char **field, int *ftype, int nf, *tzp = DetermineTimeZoneOffset(tm, namedTz); } - /* timezone not specified? then find local timezone if possible */ + /* + * Likewise, if we had a dynamic timezone abbreviation, resolve it + * now. + */ + if (abbrevTz != NULL) + { + /* daylight savings time modifier disallowed with dynamic TZ */ + if (fmask & DTK_M(DTZMOD)) + return DTERR_BAD_FORMAT; + + *tzp = DetermineTimeZoneAbbrevOffset(tm, abbrev, abbrevTz); + } + + /* timezone not specified? then use session timezone */ if (tzp != NULL && !(fmask & DTK_M(TZ))) { /* @@ -1439,17 +1456,40 @@ DecodeDateTime(char **field, int *ftype, int nf, /* DetermineTimeZoneOffset() * - * Given a struct pg_tm in which tm_year, tm_mon, tm_mday, tm_hour, tm_min, and - * tm_sec fields are set, attempt to determine the applicable time zone - * (ie, regular or daylight-savings time) at that time. Set the struct pg_tm's - * tm_isdst field accordingly, and return the actual timezone offset. + * Given a struct pg_tm in which tm_year, tm_mon, tm_mday, tm_hour, tm_min, + * and tm_sec fields are set, and a zic-style time zone definition, determine + * the applicable GMT offset and daylight-savings status at that time. + * Set the struct pg_tm's tm_isdst field accordingly, and return the GMT + * offset as the function result. + * + * Note: if the date is out of the range we can deal with, we return zero + * as the GMT offset and set tm_isdst = 0. We don't throw an error here, + * though probably some higher-level code will. + */ +int +DetermineTimeZoneOffset(struct pg_tm * tm, pg_tz *tzp) +{ + pg_time_t t; + + return DetermineTimeZoneOffsetInternal(tm, tzp, &t); +} + + +/* DetermineTimeZoneOffsetInternal() + * + * As above, but also return the actual UTC time imputed to the date/time + * into *tp. + * + * In event of an out-of-range date, we punt by returning zero into *tp. + * This is okay for the immediate callers but is a good reason for not + * exposing this worker function globally. * * Note: it might seem that we should use mktime() for this, but bitter * experience teaches otherwise. This code is much faster than most versions * of mktime(), anyway. */ -int -DetermineTimeZoneOffset(struct pg_tm * tm, pg_tz *tzp) +static int +DetermineTimeZoneOffsetInternal(struct pg_tm * tm, pg_tz *tzp, pg_time_t *tp) { int date, sec; @@ -1468,8 +1508,8 @@ DetermineTimeZoneOffset(struct pg_tm * tm, pg_tz *tzp) /* * First, generate the pg_time_t value corresponding to the given * y/m/d/h/m/s taken as GMT time. If this overflows, punt and decide the - * timezone is GMT. (We only need to worry about overflow on machines - * where pg_time_t is 32 bits.) + * timezone is GMT. (For a valid Julian date, integer overflow should be + * impossible with 64-bit pg_time_t, but let's check for safety.) */ if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday)) goto overflow; @@ -1506,6 +1546,7 @@ DetermineTimeZoneOffset(struct pg_tm * tm, pg_tz *tzp) { /* Non-DST zone, life is simple */ tm->tm_isdst = before_isdst; + *tp = mytime - before_gmtoff; return -(int) before_gmtoff; } @@ -1526,38 +1567,124 @@ DetermineTimeZoneOffset(struct pg_tm * tm, pg_tz *tzp) goto overflow; /* - * If both before or both after the boundary time, we know what to do + * If both before or both after the boundary time, we know what to do. The + * boundary time itself is considered to be after the transition, which + * means we can accept aftertime == boundary in the second case. */ - if (beforetime <= boundary && aftertime < boundary) + if (beforetime < boundary && aftertime < boundary) { tm->tm_isdst = before_isdst; + *tp = beforetime; return -(int) before_gmtoff; } if (beforetime > boundary && aftertime >= boundary) { tm->tm_isdst = after_isdst; + *tp = aftertime; return -(int) after_gmtoff; } /* - * It's an invalid or ambiguous time due to timezone transition. Prefer - * the standard-time interpretation. + * It's an invalid or ambiguous time due to timezone transition. In a + * spring-forward transition, prefer the "before" interpretation; in a + * fall-back transition, prefer "after". (We used to define and implement + * this test as "prefer the standard-time interpretation", but that rule + * does not help to resolve the behavior when both times are reported as + * standard time; which does happen, eg Europe/Moscow in Oct 2014.) */ - if (after_isdst == 0) + if (beforetime > aftertime) { - tm->tm_isdst = after_isdst; - return -(int) after_gmtoff; + tm->tm_isdst = before_isdst; + *tp = beforetime; + return -(int) before_gmtoff; } - tm->tm_isdst = before_isdst; - return -(int) before_gmtoff; + tm->tm_isdst = after_isdst; + *tp = aftertime; + return -(int) after_gmtoff; overflow: /* Given date is out of range, so assume UTC */ tm->tm_isdst = 0; + *tp = 0; return 0; } +/* DetermineTimeZoneAbbrevOffset() + * + * Determine the GMT offset and DST flag to be attributed to a dynamic + * time zone abbreviation, that is one whose meaning has changed over time. + * *tm contains the local time at which the meaning should be determined, + * and tm->tm_isdst receives the DST flag. + * + * This differs from the behavior of DetermineTimeZoneOffset() in that a + * standard-time or daylight-time abbreviation forces use of the corresponding + * GMT offset even when the zone was then in DS or standard time respectively. + */ +int +DetermineTimeZoneAbbrevOffset(struct pg_tm * tm, const char *abbr, pg_tz *tzp) +{ + pg_time_t t; + + /* + * Compute the UTC time we want to probe at. (In event of overflow, we'll + * probe at the epoch, which is a bit random but probably doesn't matter.) + */ + (void) DetermineTimeZoneOffsetInternal(tm, tzp, &t); + + return DetermineTimeZoneAbbrevOffsetInternal(t, abbr, tzp, &tm->tm_isdst); +} + + +/* DetermineTimeZoneAbbrevOffsetTS() + * + * As above but the probe time is specified as a TimestampTz (hence, UTC time), + * and DST status is returned into *isdst rather than into tm->tm_isdst. + */ +int +DetermineTimeZoneAbbrevOffsetTS(TimestampTz ts, const char *abbr, + pg_tz *tzp, int *isdst) +{ + pg_time_t t = timestamptz_to_time_t(ts); + + return DetermineTimeZoneAbbrevOffsetInternal(t, abbr, tzp, isdst); +} + + +/* DetermineTimeZoneAbbrevOffsetInternal() + * + * Workhorse for above two functions: work from a pg_time_t probe instant. + * DST status is returned into *isdst. + */ +static int +DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t, const char *abbr, + pg_tz *tzp, int *isdst) +{ + char upabbr[TZ_STRLEN_MAX + 1]; + unsigned char *p; + long int gmtoff; + + /* We need to force the abbrev to upper case */ + strlcpy(upabbr, abbr, sizeof(upabbr)); + for (p = (unsigned char *) upabbr; *p; p++) + *p = pg_toupper(*p); + + /* Look up the abbrev's meaning at this time in this zone */ + if (!pg_interpret_timezone_abbrev(upabbr, + &t, + &gmtoff, + isdst, + tzp)) + ereport(ERROR, + (errcode(ERRCODE_CONFIG_FILE_ERROR), + errmsg("time zone abbreviation \"%s\" is not used in time zone \"%s\"", + abbr, pg_get_timezone_name(tzp)))); + + /* Change sign to agree with DetermineTimeZoneOffset() */ + return (int) -gmtoff; +} + + /* DecodeTimeOnly() * Interpret parsed string as time fields only. * Returns 0 if successful, DTERR code if bogus input detected. @@ -1586,6 +1713,9 @@ DecodeTimeOnly(char **field, int *ftype, int nf, bool bc = FALSE; int mer = HR24; pg_tz *namedTz = NULL; + pg_tz *abbrevTz = NULL; + char *abbrev = NULL; + pg_tz *valtz; *dtype = DTK_TIME; tm->tm_hour = 0; @@ -1930,7 +2060,10 @@ DecodeTimeOnly(char **field, int *ftype, int nf, case DTK_STRING: case DTK_SPECIAL: - type = DecodeSpecial(i, field[i], &val); + /* timezone abbrevs take precedence over built-in tokens */ + type = DecodeTimezoneAbbrev(i, field[i], &val, &valtz); + if (type == UNKNOWN_FIELD) + type = DecodeSpecial(i, field[i], &val); if (type == IGNORE_DTF) continue; @@ -1978,7 +2111,7 @@ DecodeTimeOnly(char **field, int *ftype, int nf, tm->tm_isdst = 1; if (tzp == NULL) return DTERR_BAD_FORMAT; - *tzp += val * MINS_PER_HOUR; + *tzp -= val; break; case DTZ: @@ -1991,7 +2124,7 @@ DecodeTimeOnly(char **field, int *ftype, int nf, tm->tm_isdst = 1; if (tzp == NULL) return DTERR_BAD_FORMAT; - *tzp = val * MINS_PER_HOUR; + *tzp = -val; ftype[i] = DTK_TZ; break; @@ -1999,11 +2132,18 @@ DecodeTimeOnly(char **field, int *ftype, int nf, tm->tm_isdst = 0; if (tzp == NULL) return DTERR_BAD_FORMAT; - *tzp = val * MINS_PER_HOUR; + *tzp = -val; ftype[i] = DTK_TZ; break; - case IGNORE_DTF: + case DYNTZ: + tmask |= DTK_M(TZ); + if (tzp == NULL) + return DTERR_BAD_FORMAT; + /* we'll determine the actual offset later */ + abbrevTz = valtz; + abbrev = field[i]; + ftype[i] = DTK_TZ; break; case AMPM: @@ -2123,7 +2263,36 @@ DecodeTimeOnly(char **field, int *ftype, int nf, } } - /* timezone not specified? then find local timezone if possible */ + /* + * Likewise, if we had a dynamic timezone abbreviation, resolve it now. + */ + if (abbrevTz != NULL) + { + struct pg_tm tt, + *tmp = &tt; + + /* + * daylight savings time modifier but no standard timezone? then error + */ + if (fmask & DTK_M(DTZMOD)) + return DTERR_BAD_FORMAT; + + if ((fmask & DTK_DATE_M) == 0) + GetCurrentDateTime(tmp); + else + { + tmp->tm_year = tm->tm_year; + tmp->tm_mon = tm->tm_mon; + tmp->tm_mday = tm->tm_mday; + } + tmp->tm_hour = tm->tm_hour; + tmp->tm_min = tm->tm_min; + tmp->tm_sec = tm->tm_sec; + *tzp = DetermineTimeZoneAbbrevOffset(tmp, abbrev, abbrevTz); + tm->tm_isdst = tmp->tm_isdst; + } + + /* timezone not specified? then use session timezone */ if (tzp != NULL && !(fmask & DTK_M(TZ))) { struct pg_tm tt, @@ -2710,8 +2879,6 @@ DecodeNumberField(int len, char *str, int fmask, * Interpret string as a numeric timezone. * * Return 0 if okay (and set *tzp), a DTERR code if not okay. - * - * NB: this must *not* ereport on failure; see commands/variable.c. */ int DecodeTimezone(char *str, int *tzp) @@ -2776,14 +2943,75 @@ DecodeTimezone(char *str, int *tzp) return 0; } + +/* DecodeTimezoneAbbrev() + * Interpret string as a timezone abbreviation, if possible. + * + * Returns an abbreviation type (TZ, DTZ, or DYNTZ), or UNKNOWN_FIELD if + * string is not any known abbreviation. On success, set *offset and *tz to + * represent the UTC offset (for TZ or DTZ) or underlying zone (for DYNTZ). + * Note that full timezone names (such as America/New_York) are not handled + * here, mostly for historical reasons. + * + * Given string must be lowercased already. + * + * Implement a cache lookup since it is likely that dates + * will be related in format. + */ +int +DecodeTimezoneAbbrev(int field, char *lowtoken, + int *offset, pg_tz **tz) +{ + int type; + const datetkn *tp; + + tp = abbrevcache[field]; + /* use strncmp so that we match truncated tokens */ + if (tp == NULL || strncmp(lowtoken, tp->token, TOKMAXLEN) != 0) + { + if (zoneabbrevtbl) + tp = datebsearch(lowtoken, zoneabbrevtbl->abbrevs, + zoneabbrevtbl->numabbrevs); + else + tp = NULL; + } + if (tp == NULL) + { + type = UNKNOWN_FIELD; + *offset = 0; + *tz = NULL; + } + else + { + abbrevcache[field] = tp; + type = tp->type; + if (type == DYNTZ) + { + *offset = 0; + *tz = FetchDynamicTimeZone(zoneabbrevtbl, tp); + } + else + { + *offset = tp->value; + *tz = NULL; + } + } + + return type; +} + + /* DecodeSpecial() * Decode text string using lookup table. * + * Recognizes the keywords listed in datetktbl. + * Note: at one time this would also recognize timezone abbreviations, + * but no more; use DecodeTimezoneAbbrev for that. + * + * Given string must be lowercased already. + * * Implement a cache lookup since it is likely that dates * will be related in format. - * - * NB: this must *not* ereport on failure; - * see commands/variable.c. */ int DecodeSpecial(int field, char *lowtoken, int *val) @@ -2792,11 +3020,10 @@ DecodeSpecial(int field, char *lowtoken, int *val) const datetkn *tp; tp = datecache[field]; + /* use strncmp so that we match truncated tokens */ if (tp == NULL || strncmp(lowtoken, tp->token, TOKMAXLEN) != 0) { - tp = datebsearch(lowtoken, timezonetktbl, sztimezonetktbl); - if (tp == NULL) - tp = datebsearch(lowtoken, datetktbl, szdatetktbl); + tp = datebsearch(lowtoken, datetktbl, szdatetktbl); } if (tp == NULL) { @@ -2807,18 +3034,7 @@ DecodeSpecial(int field, char *lowtoken, int *val) { datecache[field] = tp; type = tp->type; - switch (type) - { - case TZ: - case DTZ: - case DTZMOD: - *val = FROMVAL(tp); - break; - - default: - *val = tp->value; - break; - } + *val = tp->value; } return type; @@ -3494,8 +3710,13 @@ DecodeISO8601Interval(char *str, /* DecodeUnits() * Decode text string using lookup table. - * This routine supports time interval decoding - * (hence, it need not recognize timezone names). + * + * This routine recognizes keywords associated with time interval units. + * + * Given string must be lowercased already. + * + * Implement a cache lookup since it is likely that dates + * will be related in format. */ int DecodeUnits(int field, char *lowtoken, int *val) @@ -3504,6 +3725,7 @@ DecodeUnits(int field, char *lowtoken, int *val) const datetkn *tp; tp = deltacache[field]; + /* use strncmp so that we match truncated tokens */ if (tp == NULL || strncmp(lowtoken, tp->token, TOKMAXLEN) != 0) { tp = datebsearch(lowtoken, deltatktbl, szdeltatktbl); @@ -3517,10 +3739,7 @@ DecodeUnits(int field, char *lowtoken, int *val) { deltacache[field] = tp; type = tp->type; - if (type == TZ || type == DTZ) - *val = FROMVAL(tp); - else - *val = tp->value; + *val = tp->value; } return type; @@ -3593,9 +3812,11 @@ datebsearch(const char *key, const datetkn *base, int nel) while (last >= base) { position = base + ((last - base) >> 1); - result = key[0] - position->token[0]; + /* precheck the first character for a bit of extra speed */ + result = (int) key[0] - (int) position->token[0]; if (result == 0) { + /* use strncmp so that we match truncated tokens */ result = strncmp(key, position->token, TOKMAXLEN); if (result == 0) return position; @@ -4142,15 +4363,26 @@ CheckDateTokenTable(const char *tablename, const datetkn *base, int nel) bool ok = true; int i; - for (i = 1; i < nel; i++) + for (i = 0; i < nel; i++) { - if (strncmp(base[i - 1].token, base[i].token, TOKMAXLEN) >= 0) + /* check for token strings that don't fit */ + if (strlen(base[i].token) > TOKMAXLEN) { /* %.*s is safe since all our tokens are ASCII */ - elog(LOG, "ordering error in %s table: \"%.*s\" >= \"%.*s\"", + elog(LOG, "token too long in %s table: \"%.*s\"", tablename, - TOKMAXLEN, base[i - 1].token, - TOKMAXLEN, base[i].token); + TOKMAXLEN + 1, base[i].token); + ok = false; + break; /* don't risk applying strcmp */ + } + /* check for out of order */ + if (i > 0 && + strcmp(base[i - 1].token, base[i].token) >= 0) + { + elog(LOG, "ordering error in %s table: \"%s\" >= \"%s\"", + tablename, + base[i - 1].token, + base[i].token); ok = false; } } @@ -4208,27 +4440,88 @@ TemporalTransform(int32 max_precis, Node *node) /* * This function gets called during timezone config file load or reload * to create the final array of timezone tokens. The argument array - * is already sorted in name order. The data is converted to datetkn - * format and installed in *tbl, which must be allocated by the caller. + * is already sorted in name order. + * + * The result is a TimeZoneAbbrevTable (which must be a single malloc'd chunk) + * or NULL on malloc failure. No other error conditions are defined. */ -void -ConvertTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl, - struct tzEntry *abbrevs, int n) +TimeZoneAbbrevTable * +ConvertTimeZoneAbbrevs(struct tzEntry *abbrevs, int n) { - datetkn *newtbl = tbl->abbrevs; + TimeZoneAbbrevTable *tbl; + Size tbl_size; int i; + /* Space for fixed fields and datetkn array */ + tbl_size = offsetof(TimeZoneAbbrevTable, abbrevs) + + n * sizeof(datetkn); + tbl_size = MAXALIGN(tbl_size); + /* Count up space for dynamic abbreviations */ + for (i = 0; i < n; i++) + { + struct tzEntry *abbr = abbrevs + i; + + if (abbr->zone != NULL) + { + Size dsize; + + dsize = offsetof(DynamicZoneAbbrev, zone) + + strlen(abbr->zone) + 1; + tbl_size += MAXALIGN(dsize); + } + } + + /* Alloc the result ... */ + tbl = malloc(tbl_size); + if (!tbl) + return NULL; + + /* ... and fill it in */ + tbl->tblsize = tbl_size; tbl->numabbrevs = n; + /* in this loop, tbl_size reprises the space calculation above */ + tbl_size = offsetof(TimeZoneAbbrevTable, abbrevs) + + n * sizeof(datetkn); + tbl_size = MAXALIGN(tbl_size); for (i = 0; i < n; i++) { - /* do NOT use strlcpy here; token field need not be null-terminated */ - strncpy(newtbl[i].token, abbrevs[i].abbrev, TOKMAXLEN); - newtbl[i].type = abbrevs[i].is_dst ? DTZ : TZ; - TOVAL(&newtbl[i], abbrevs[i].offset / MINS_PER_HOUR); + struct tzEntry *abbr = abbrevs + i; + datetkn *dtoken = tbl->abbrevs + i; + + /* use strlcpy to truncate name if necessary */ + strlcpy(dtoken->token, abbr->abbrev, TOKMAXLEN + 1); + if (abbr->zone != NULL) + { + /* Allocate a DynamicZoneAbbrev for this abbreviation */ + DynamicZoneAbbrev *dtza; + Size dsize; + + dtza = (DynamicZoneAbbrev *) ((char *) tbl + tbl_size); + dtza->tz = NULL; + strcpy(dtza->zone, abbr->zone); + + dtoken->type = DYNTZ; + /* value is offset from table start to DynamicZoneAbbrev */ + dtoken->value = (int32) tbl_size; + + dsize = offsetof(DynamicZoneAbbrev, zone) + + strlen(abbr->zone) + 1; + tbl_size += MAXALIGN(dsize); + } + else + { + dtoken->type = abbr->is_dst ? DTZ : TZ; + dtoken->value = abbr->offset; + } } + /* Assert the two loops above agreed on size calculations */ + Assert(tbl->tblsize == tbl_size); + /* Check the ordering, if testing */ - Assert(CheckDateTokenTable("timezone offset", newtbl, n)); + Assert(CheckDateTokenTable("timezone abbreviations", tbl->abbrevs, n)); + + return tbl; } /* @@ -4239,16 +4532,46 @@ ConvertTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl, void InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl) { - int i; + zoneabbrevtbl = tbl; + /* reset abbrevcache, which may contain pointers into old table */ + memset(abbrevcache, 0, sizeof(abbrevcache)); +} - timezonetktbl = tbl->abbrevs; - sztimezonetktbl = tbl->numabbrevs; +/* + * Helper subroutine to locate pg_tz timezone for a dynamic abbreviation. + */ +static pg_tz * +FetchDynamicTimeZone(TimeZoneAbbrevTable *tbl, const datetkn *tp) +{ + DynamicZoneAbbrev *dtza; - /* clear date cache in case it contains any stale timezone names */ - for (i = 0; i < MAXDATEFIELDS; i++) - datecache[i] = NULL; + /* Just some sanity checks to prevent indexing off into nowhere */ + Assert(tp->type == DYNTZ); + Assert(tp->value > 0 && tp->value < tbl->tblsize); + + dtza = (DynamicZoneAbbrev *) ((char *) tbl + tp->value); + + /* Look up the underlying zone if we haven't already */ + if (dtza->tz == NULL) + { + dtza->tz = pg_tzset(dtza->zone); + + /* + * Ideally we'd let the caller ereport instead of doing it here, but + * then there is no way to report the bad time zone name. + */ + if (dtza->tz == NULL) + ereport(ERROR, + (errcode(ERRCODE_CONFIG_FILE_ERROR), + errmsg("time zone \"%s\" not recognized", + dtza->zone), + errdetail("This time zone name appears in the configuration file for time zone abbreviation \"%s\".", + tp->token))); + } + return dtza->tz; } + /* * This set-returning function reads all the available time zone abbreviations * and returns a set of (abbrev, utc_offset, is_dst). @@ -4262,7 +4585,10 @@ pg_timezone_abbrevs(PG_FUNCTION_ARGS) HeapTuple tuple; Datum values[3]; bool nulls[3]; + const datetkn *tp; char buffer[TOKMAXLEN + 1]; + int gmtoffset; + bool is_dst; unsigned char *p; struct pg_tm tm; Interval *resInterval; @@ -4306,31 +4632,65 @@ pg_timezone_abbrevs(PG_FUNCTION_ARGS) funcctx = SRF_PERCALL_SETUP(); pindex = (int *) funcctx->user_fctx; - if (*pindex >= sztimezonetktbl) + if (zoneabbrevtbl == NULL || + *pindex >= zoneabbrevtbl->numabbrevs) SRF_RETURN_DONE(funcctx); + tp = zoneabbrevtbl->abbrevs + *pindex; + + switch (tp->type) + { + case TZ: + gmtoffset = tp->value; + is_dst = false; + break; + case DTZ: + gmtoffset = tp->value; + is_dst = true; + break; + case DYNTZ: + { + /* Determine the current meaning of the abbrev */ + pg_tz *tzp; + TimestampTz now; + int isdst; + + tzp = FetchDynamicTimeZone(zoneabbrevtbl, tp); + now = GetCurrentTransactionStartTimestamp(); + gmtoffset = -DetermineTimeZoneAbbrevOffsetTS(now, + tp->token, + tzp, + &isdst); + is_dst = (bool) isdst; + break; + } + default: + elog(ERROR, "unrecognized timezone type %d", (int) tp->type); + gmtoffset = 0; /* keep compiler quiet */ + is_dst = false; + break; + } + MemSet(nulls, 0, sizeof(nulls)); /* * Convert name to text, using upcasing conversion that is the inverse of * what ParseDateTime() uses. */ - strncpy(buffer, timezonetktbl[*pindex].token, TOKMAXLEN); - buffer[TOKMAXLEN] = '\0'; /* may not be null-terminated */ + strlcpy(buffer, tp->token, sizeof(buffer)); for (p = (unsigned char *) buffer; *p; p++) *p = pg_toupper(*p); values[0] = CStringGetTextDatum(buffer); + /* Convert offset (in seconds) to an interval */ MemSet(&tm, 0, sizeof(struct pg_tm)); - tm.tm_min = (-1) * FROMVAL(&timezonetktbl[*pindex]); + tm.tm_sec = gmtoffset; resInterval = (Interval *) palloc(sizeof(Interval)); tm2interval(&tm, 0, resInterval); values[1] = IntervalPGetDatum(resInterval); - Assert(timezonetktbl[*pindex].type == DTZ || - timezonetktbl[*pindex].type == TZ); - values[2] = BoolGetDatum(timezonetktbl[*pindex].type == DTZ); + values[2] = BoolGetDatum(is_dst); (*pindex)++; diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index 11007c6d894..410bf47483e 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -486,6 +486,9 @@ timestamptz_in(PG_FUNCTION_ARGS) /* * Try to parse a timezone specification, and return its timezone offset value * if it's acceptable. Otherwise, an error is thrown. + * + * Note: some code paths update tm->tm_isdst, and some don't; current callers + * don't care, so we don't bother being consistent. */ static int parse_sane_timezone(struct pg_tm * tm, text *zone) @@ -499,12 +502,12 @@ parse_sane_timezone(struct pg_tm * tm, text *zone) /* * Look up the requested timezone. First we try to interpret it as a * numeric timezone specification; if DecodeTimezone decides it doesn't - * like the format, we look in the date token table (to handle cases like - * "EST"), and if that also fails, we look in the timezone database (to - * handle cases like "America/New_York"). (This matches the order in - * which timestamp input checks the cases; it's important because the - * timezone database unwisely uses a few zone names that are identical to - * offset abbreviations.) + * like the format, we look in the timezone abbreviation table (to handle + * cases like "EST"), and if that also fails, we look in the timezone + * database (to handle cases like "America/New_York"). (This matches the + * order in which timestamp input checks the cases; it's important because + * the timezone database unwisely uses a few zone names that are identical + * to offset abbreviations.) * * Note pg_tzset happily parses numeric input that DecodeTimezone would * reject. To avoid having it accept input that would otherwise be seen @@ -524,6 +527,7 @@ parse_sane_timezone(struct pg_tm * tm, text *zone) char *lowzone; int type, val; + pg_tz *tzp; if (rt == DTERR_TZDISP_OVERFLOW) ereport(ERROR, @@ -534,19 +538,26 @@ parse_sane_timezone(struct pg_tm * tm, text *zone) (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("time zone \"%s\" not recognized", tzname))); + /* DecodeTimezoneAbbrev requires lowercase input */ lowzone = downcase_truncate_identifier(tzname, strlen(tzname), false); - type = DecodeSpecial(0, lowzone, &val); + type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp); if (type == TZ || type == DTZ) - tz = val * MINS_PER_HOUR; + { + /* fixed-offset abbreviation */ + tz = -val; + } + else if (type == DYNTZ) + { + /* dynamic-offset abbreviation, resolve using specified time */ + tz = DetermineTimeZoneAbbrevOffset(tm, tzname, tzp); + } else { - pg_tz *tzp; - + /* try it as a full zone name */ tzp = pg_tzset(tzname); - if (tzp) tz = DetermineTimeZoneOffset(tm, tzp); else @@ -4883,39 +4894,52 @@ timestamp_zone(PG_FUNCTION_ARGS) int type, val; pg_tz *tzp; + struct pg_tm tm; + fsec_t fsec; if (TIMESTAMP_NOT_FINITE(timestamp)) PG_RETURN_TIMESTAMPTZ(timestamp); /* - * Look up the requested timezone. First we look in the date token table - * (to handle cases like "EST"), and if that fails, we look in the - * timezone database (to handle cases like "America/New_York"). (This - * matches the order in which timestamp input checks the cases; it's - * important because the timezone database unwisely uses a few zone names - * that are identical to offset abbreviations.) + * Look up the requested timezone. First we look in the timezone + * abbreviation table (to handle cases like "EST"), and if that fails, we + * look in the timezone database (to handle cases like + * "America/New_York"). (This matches the order in which timestamp input + * checks the cases; it's important because the timezone database unwisely + * uses a few zone names that are identical to offset abbreviations.) */ text_to_cstring_buffer(zone, tzname, sizeof(tzname)); + + /* DecodeTimezoneAbbrev requires lowercase input */ lowzone = downcase_truncate_identifier(tzname, strlen(tzname), false); - type = DecodeSpecial(0, lowzone, &val); + type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp); if (type == TZ || type == DTZ) { - tz = -(val * MINS_PER_HOUR); + /* fixed-offset abbreviation */ + tz = val; + result = dt2local(timestamp, tz); + } + else if (type == DYNTZ) + { + /* dynamic-offset abbreviation, resolve using specified time */ + if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"))); + tz = -DetermineTimeZoneAbbrevOffset(&tm, tzname, tzp); result = dt2local(timestamp, tz); } else { + /* try it as a full zone name */ tzp = pg_tzset(tzname); if (tzp) { /* Apply the timezone change */ - struct pg_tm tm; - fsec_t fsec; - if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0) ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), @@ -5061,27 +5085,39 @@ timestamptz_zone(PG_FUNCTION_ARGS) PG_RETURN_TIMESTAMP(timestamp); /* - * Look up the requested timezone. First we look in the date token table - * (to handle cases like "EST"), and if that fails, we look in the - * timezone database (to handle cases like "America/New_York"). (This - * matches the order in which timestamp input checks the cases; it's - * important because the timezone database unwisely uses a few zone names - * that are identical to offset abbreviations.) + * Look up the requested timezone. First we look in the timezone + * abbreviation table (to handle cases like "EST"), and if that fails, we + * look in the timezone database (to handle cases like + * "America/New_York"). (This matches the order in which timestamp input + * checks the cases; it's important because the timezone database unwisely + * uses a few zone names that are identical to offset abbreviations.) */ text_to_cstring_buffer(zone, tzname, sizeof(tzname)); + + /* DecodeTimezoneAbbrev requires lowercase input */ lowzone = downcase_truncate_identifier(tzname, strlen(tzname), false); - type = DecodeSpecial(0, lowzone, &val); + type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp); if (type == TZ || type == DTZ) { - tz = val * MINS_PER_HOUR; + /* fixed-offset abbreviation */ + tz = -val; + result = dt2local(timestamp, tz); + } + else if (type == DYNTZ) + { + /* dynamic-offset abbreviation, resolve using specified time */ + int isdst; + + tz = DetermineTimeZoneAbbrevOffsetTS(timestamp, tzname, tzp, &isdst); result = dt2local(timestamp, tz); } else { + /* try it as a full zone name */ tzp = pg_tzset(tzname); if (tzp) { diff --git a/src/backend/utils/misc/tzparser.c b/src/backend/utils/misc/tzparser.c index 6a5a7b39abf..a6a12ff06e3 100644 --- a/src/backend/utils/misc/tzparser.c +++ b/src/backend/utils/misc/tzparser.c @@ -63,13 +63,6 @@ validateTzEntry(tzEntry *tzentry) tzentry->filename, tzentry->lineno); return false; } - if (tzentry->offset % 900 != 0) - { - GUC_check_errmsg("time zone offset %d is not a multiple of 900 sec (15 min) in time zone file \"%s\", line %d", - tzentry->offset, - tzentry->filename, tzentry->lineno); - return false; - } /* * Sanity-check the offset: shouldn't exceed 14 hours @@ -93,7 +86,11 @@ validateTzEntry(tzEntry *tzentry) } /* - * Attempt to parse the line as a timezone abbrev spec (name, offset, dst) + * Attempt to parse the line as a timezone abbrev spec + * + * Valid formats are: + * name zone + * name offset dst * * Returns TRUE if OK, else false; data is stored in *tzentry */ @@ -116,7 +113,7 @@ splitTzLine(const char *filename, int lineno, char *line, tzEntry *tzentry) filename, lineno); return false; } - tzentry->abbrev = abbrev; + tzentry->abbrev = pstrdup(abbrev); offset = strtok(NULL, WHITESPACE); if (!offset) @@ -125,25 +122,43 @@ splitTzLine(const char *filename, int lineno, char *line, tzEntry *tzentry) filename, lineno); return false; } - tzentry->offset = strtol(offset, &offset_endptr, 10); - if (offset_endptr == offset || *offset_endptr != '\0') - { - GUC_check_errmsg("invalid number for time zone offset in time zone file \"%s\", line %d", - filename, lineno); - return false; - } - is_dst = strtok(NULL, WHITESPACE); - if (is_dst && pg_strcasecmp(is_dst, "D") == 0) + /* We assume zone names don't begin with a digit or sign */ + if (isdigit((unsigned char) *offset) || *offset == '+' || *offset == '-') { - tzentry->is_dst = true; - remain = strtok(NULL, WHITESPACE); + tzentry->zone = NULL; + tzentry->offset = strtol(offset, &offset_endptr, 10); + if (offset_endptr == offset || *offset_endptr != '\0') + { + GUC_check_errmsg("invalid number for time zone offset in time zone file \"%s\", line %d", + filename, lineno); + return false; + } + + is_dst = strtok(NULL, WHITESPACE); + if (is_dst && pg_strcasecmp(is_dst, "D") == 0) + { + tzentry->is_dst = true; + remain = strtok(NULL, WHITESPACE); + } + else + { + /* there was no 'D' dst specifier */ + tzentry->is_dst = false; + remain = is_dst; + } } else { - /* there was no 'D' dst specifier */ + /* + * Assume entry is a zone name. We do not try to validate it by + * looking up the zone, because that would force loading of a lot of + * zones that probably will never be used in the current session. + */ + tzentry->zone = pstrdup(offset); + tzentry->offset = 0; tzentry->is_dst = false; - remain = is_dst; + remain = strtok(NULL, WHITESPACE); } if (!remain) /* no more non-whitespace chars */ @@ -201,8 +216,11 @@ addToArray(tzEntry **base, int *arraysize, int n, /* * Found a duplicate entry; complain unless it's the same. */ - if (midptr->offset == entry->offset && - midptr->is_dst == entry->is_dst) + if ((midptr->zone == NULL && entry->zone == NULL && + midptr->offset == entry->offset && + midptr->is_dst == entry->is_dst) || + (midptr->zone != NULL && entry->zone != NULL && + strcmp(midptr->zone, entry->zone) == 0)) { /* return unchanged array */ return n; @@ -210,6 +228,7 @@ addToArray(tzEntry **base, int *arraysize, int n, if (override) { /* same abbrev but something is different, override */ + midptr->zone = entry->zone; midptr->offset = entry->offset; midptr->is_dst = entry->is_dst; return n; @@ -239,9 +258,6 @@ addToArray(tzEntry **base, int *arraysize, int n, memcpy(arrayptr, entry, sizeof(tzEntry)); - /* Must dup the abbrev to ensure it survives */ - arrayptr->abbrev = pstrdup(entry->abbrev); - return n + 1; } @@ -446,15 +462,12 @@ load_tzoffsets(const char *filename) /* Parse the file(s) */ n = ParseTzFile(filename, 0, &array, &arraysize, 0); - /* If no errors so far, allocate result and let datetime.c convert data */ + /* If no errors so far, let datetime.c allocate memory & convert format */ if (n >= 0) { - result = malloc(offsetof(TimeZoneAbbrevTable, abbrevs) + - n * sizeof(datetkn)); + result = ConvertTimeZoneAbbrevs(array, n); if (!result) GUC_check_errmsg("out of memory"); - else - ConvertTimeZoneAbbrevs(result, array, n); } /* Clean up */ diff --git a/src/include/pgtime.h b/src/include/pgtime.h index b3c867a4cf8..a85bc2781b5 100644 --- a/src/include/pgtime.h +++ b/src/include/pgtime.h @@ -54,13 +54,20 @@ extern int pg_next_dst_boundary(const pg_time_t *timep, long int *after_gmtoff, int *after_isdst, const pg_tz *tz); -extern size_t pg_strftime(char *s, size_t max, const char *format, - const struct pg_tm * tm); - +extern bool pg_interpret_timezone_abbrev(const char *abbrev, + const pg_time_t *timep, + long int *gmtoff, + int *isdst, + const pg_tz *tz); extern bool pg_get_timezone_offset(const pg_tz *tz, long int *gmtoff); extern const char *pg_get_timezone_name(pg_tz *tz); extern bool pg_tz_acceptable(pg_tz *tz); +/* these functions are in strftime.c */ + +extern size_t pg_strftime(char *s, size_t max, const char *format, + const struct pg_tm * tm); + /* these functions and variables are in pgtz.c */ extern pg_tz *session_timezone; diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h index 2e69503f96d..9b53ee38ccf 100644 --- a/src/include/utils/datetime.h +++ b/src/include/utils/datetime.h @@ -77,7 +77,7 @@ struct tzEntry; #define BC 1 /* - * Fields for time decoding. + * Field types for time decoding. * * Can't have more of these than there are bits in an unsigned int * since these are turned into bit masks during parsing and decoding. @@ -93,9 +93,9 @@ struct tzEntry; #define YEAR 2 #define DAY 3 #define JULIAN 4 -#define TZ 5 -#define DTZ 6 -#define DTZMOD 7 +#define TZ 5 /* fixed-offset timezone abbreviation */ +#define DTZ 6 /* fixed-offset timezone abbrev, DST */ +#define DYNTZ 7 /* dynamic timezone abbreviation */ #define IGNORE_DTF 8 #define AMPM 9 #define HOUR 10 @@ -119,18 +119,24 @@ struct tzEntry; #define DECADE 25 #define CENTURY 26 #define MILLENNIUM 27 +/* hack for parsing two-word timezone specs "MET DST" etc */ +#define DTZMOD 28 /* "DST" as a separate word */ /* reserved for unrecognized string values */ #define UNKNOWN_FIELD 31 /* * Token field definitions for time parsing and decoding. - * These need to fit into the datetkn table type. - * At the moment, that means keep them within [-127,127]. - * These are also used for bit masks in DecodeDateDelta() + * + * Some field type codes (see above) use these as the "value" in datetktbl[]. + * These are also used for bit masks in DecodeDateTime and friends * so actually restrict them to within [0,31] for now. * - thomas 97/06/19 - * Not all of these fields are used for masks in DecodeDateDelta + * Not all of these fields are used for masks in DecodeDateTime * so allow some larger than 31. - thomas 1997-11-17 + * + * Caution: there are undocumented assumptions in the code that most of these + * values are not equal to IGNORE_DTF nor RESERV. Be very careful when + * renumbering values in either of these apparently-independent lists :-( */ #define DTK_NUMBER 0 @@ -203,18 +209,27 @@ struct tzEntry; /* keep this struct small; it gets used a lot */ typedef struct { - char token[TOKMAXLEN]; - char type; - char value; /* this may be unsigned, alas */ + char token[TOKMAXLEN + 1]; /* always NUL-terminated */ + char type; /* see field type codes above */ + int32 value; /* meaning depends on type */ } datetkn; /* one of its uses is in tables of time zone abbreviations */ typedef struct TimeZoneAbbrevTable { - int numabbrevs; + Size tblsize; /* size in bytes of TimeZoneAbbrevTable */ + int numabbrevs; /* number of entries in abbrevs[] array */ datetkn abbrevs[1]; /* VARIABLE LENGTH ARRAY */ + /* DynamicZoneAbbrev(s) may follow the abbrevs[] array */ } TimeZoneAbbrevTable; +/* auxiliary data for a dynamic time zone abbreviation (non-fixed-offset) */ +typedef struct DynamicZoneAbbrev +{ + pg_tz *tz; /* NULL if not yet looked up */ + char zone[1]; /* zone name (var length, NUL-terminated) */ +} DynamicZoneAbbrev; + /* FMODULO() * Macro to replace modf(), which is broken on some platforms. @@ -296,6 +311,9 @@ extern void DateTimeParseError(int dterr, const char *str, const char *datatype) __attribute__((noreturn)); extern int DetermineTimeZoneOffset(struct pg_tm * tm, pg_tz *tzp); +extern int DetermineTimeZoneAbbrevOffset(struct pg_tm * tm, const char *abbr, pg_tz *tzp); +extern int DetermineTimeZoneAbbrevOffsetTS(TimestampTz ts, const char *abbr, + pg_tz *tzp, int *isdst); extern void EncodeDateOnly(struct pg_tm * tm, int style, char *str); extern void EncodeTimeOnly(struct pg_tm * tm, fsec_t fsec, bool print_tz, int tz, int style, char *str); @@ -305,6 +323,8 @@ extern void EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str) extern int ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc, struct pg_tm * tm); +extern int DecodeTimezoneAbbrev(int field, char *lowtoken, + int *offset, pg_tz **tz); extern int DecodeSpecial(int field, char *lowtoken, int *val); extern int DecodeUnits(int field, char *lowtoken, int *val); @@ -314,8 +334,8 @@ extern Node *TemporalTransform(int32 max_precis, Node *node); extern bool CheckDateTokenTables(void); -extern void ConvertTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl, - struct tzEntry *abbrevs, int n); +extern TimeZoneAbbrevTable *ConvertTimeZoneAbbrevs(struct tzEntry *abbrevs, + int n); extern void InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl); extern Datum pg_timezone_abbrevs(PG_FUNCTION_ARGS); diff --git a/src/include/utils/tzparser.h b/src/include/utils/tzparser.h index 82728a67dd0..637f449e36c 100644 --- a/src/include/utils/tzparser.h +++ b/src/include/utils/tzparser.h @@ -22,10 +22,12 @@ */ typedef struct tzEntry { - /* the actual data: TZ abbrev (downcased), offset, DST flag */ - char *abbrev; - int offset; /* in seconds from UTC */ - bool is_dst; + /* the actual data */ + char *abbrev; /* TZ abbreviation (downcased) */ + char *zone; /* zone name if dynamic abbrev, else NULL */ + /* for a dynamic abbreviation, offset/is_dst are not used */ + int offset; /* offset in seconds from UTC */ + bool is_dst; /* true if a DST abbreviation */ /* source information (for error messages) */ int lineno; const char *filename; diff --git a/src/interfaces/ecpg/pgtypeslib/dt.h b/src/interfaces/ecpg/pgtypeslib/dt.h index c2635c7b287..145e2b7c4fb 100644 --- a/src/interfaces/ecpg/pgtypeslib/dt.h +++ b/src/interfaces/ecpg/pgtypeslib/dt.h @@ -42,6 +42,7 @@ typedef double fsec_t; #define DAGO "ago" +#define DCURRENT "current" #define EPOCH "epoch" #define INVALID "invalid" #define EARLY "-infinity" @@ -68,7 +69,6 @@ typedef double fsec_t; #define DA_D "ad" #define DB_C "bc" #define DTIMEZONE "timezone" -#define DCURRENT "current" /* * Fundamental time field definitions for parsing. @@ -85,7 +85,7 @@ typedef double fsec_t; #define BC 1 /* - * Fields for time decoding. + * Field types for time decoding. * * Can't have more of these than there are bits in an unsigned int * since these are turned into bit masks during parsing and decoding. @@ -103,9 +103,9 @@ typedef double fsec_t; #define YEAR 2 #define DAY 3 #define JULIAN 4 -#define TZ 5 -#define DTZ 6 -#define DTZMOD 7 +#define TZ 5 /* fixed-offset timezone abbreviation */ +#define DTZ 6 /* fixed-offset timezone abbrev, DST */ +#define DYNTZ 7 /* dynamic timezone abbr (unimplemented) */ #define IGNORE_DTF 8 #define AMPM 9 #define HOUR 10 @@ -124,19 +124,25 @@ typedef double fsec_t; /* generic fields to help with parsing */ #define ISODATE 22 #define ISOTIME 23 +/* hack for parsing two-word timezone specs "MET DST" etc */ +#define DTZMOD 28 /* "DST" as a separate word */ /* reserved for unrecognized string values */ #define UNKNOWN_FIELD 31 /* * Token field definitions for time parsing and decoding. - * These need to fit into the datetkn table type. - * At the moment, that means keep them within [-127,127]. - * These are also used for bit masks in DecodeDateDelta() + * + * Some field type codes (see above) use these as the "value" in datetktbl[]. + * These are also used for bit masks in DecodeDateTime and friends * so actually restrict them to within [0,31] for now. * - thomas 97/06/19 - * Not all of these fields are used for masks in DecodeDateDelta + * Not all of these fields are used for masks in DecodeDateTime * so allow some larger than 31. - thomas 1997-11-17 + * + * Caution: there are undocumented assumptions in the code that most of these + * values are not equal to IGNORE_DTF nor RESERV. Be very careful when + * renumbering values in either of these apparently-independent lists :-( */ #define DTK_NUMBER 0 @@ -207,13 +213,9 @@ typedef double fsec_t; /* keep this struct small; it gets used a lot */ typedef struct { -#if defined(_AIX) - char *token; -#else - char token[TOKMAXLEN]; -#endif /* _AIX */ - char type; - char value; /* this may be unsigned, alas */ + char token[TOKMAXLEN + 1]; /* always NUL-terminated */ + char type; /* see field type codes above */ + int32 value; /* meaning depends on type */ } datetkn; diff --git a/src/interfaces/ecpg/pgtypeslib/dt_common.c b/src/interfaces/ecpg/pgtypeslib/dt_common.c index 2286acd428f..7fdd09b3068 100644 --- a/src/interfaces/ecpg/pgtypeslib/dt_common.c +++ b/src/interfaces/ecpg/pgtypeslib/dt_common.c @@ -16,38 +16,31 @@ int day_tab[2][13] = { typedef long AbsoluteTime; -#define ABS_SIGNBIT ((char) 0200) -#define POS(n) (n) -#define NEG(n) ((n)|ABS_SIGNBIT) -#define FROMVAL(tp) (-SIGNEDCHAR((tp)->value) * 15) /* uncompress */ -#define VALMASK ((char) 0177) -#define SIGNEDCHAR(c) ((c)&ABS_SIGNBIT? -((c)&VALMASK): (c)) - static datetkn datetktbl[] = { /* text, token, lexval */ {EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */ - {"acsst", DTZ, POS(42)}, /* Cent. Australia */ - {"acst", DTZ, NEG(16)}, /* Atlantic/Porto Acre */ - {"act", TZ, NEG(20)}, /* Atlantic/Porto Acre */ + {"acsst", DTZ, 37800}, /* Cent. Australia */ + {"acst", DTZ, -14400}, /* Atlantic/Porto Acre */ + {"act", TZ, -18000}, /* Atlantic/Porto Acre */ {DA_D, ADBC, AD}, /* "ad" for years >= 0 */ - {"adt", DTZ, NEG(12)}, /* Atlantic Daylight Time */ - {"aesst", DTZ, POS(44)}, /* E. Australia */ - {"aest", TZ, POS(40)}, /* Australia Eastern Std Time */ - {"aft", TZ, POS(18)}, /* Kabul */ - {"ahst", TZ, NEG(40)}, /* Alaska-Hawaii Std Time */ - {"akdt", DTZ, NEG(32)}, /* Alaska Daylight Time */ - {"akst", DTZ, NEG(36)}, /* Alaska Standard Time */ + {"adt", DTZ, -10800}, /* Atlantic Daylight Time */ + {"aesst", DTZ, 39600}, /* E. Australia */ + {"aest", TZ, 36000}, /* Australia Eastern Std Time */ + {"aft", TZ, 16200}, /* Kabul */ + {"ahst", TZ, -36000}, /* Alaska-Hawaii Std Time */ + {"akdt", DTZ, -28800}, /* Alaska Daylight Time */ + {"akst", DTZ, -32400}, /* Alaska Standard Time */ {"allballs", RESERV, DTK_ZULU}, /* 00:00:00 */ - {"almst", TZ, POS(28)}, /* Almaty Savings Time */ - {"almt", TZ, POS(24)}, /* Almaty Time */ + {"almst", TZ, 25200}, /* Almaty Savings Time */ + {"almt", TZ, 21600}, /* Almaty Time */ {"am", AMPM, AM}, - {"amst", DTZ, POS(20)}, /* Armenia Summer Time (Yerevan) */ + {"amst", DTZ, 18000}, /* Armenia Summer Time (Yerevan) */ #if 0 - {"amst", DTZ, NEG(12)}, /* Porto Velho */ + {"amst", DTZ, -10800}, /* Porto Velho */ #endif - {"amt", TZ, POS(16)}, /* Armenia Time (Yerevan) */ - {"anast", DTZ, POS(52)}, /* Anadyr Summer Time (Russia) */ - {"anat", TZ, POS(48)}, /* Anadyr Time (Russia) */ + {"amt", TZ, 14400}, /* Armenia Time (Yerevan) */ + {"anast", DTZ, 46800}, /* Anadyr Summer Time (Russia) */ + {"anat", TZ, 43200}, /* Anadyr Time (Russia) */ {"apr", MONTH, 4}, {"april", MONTH, 4}, #if 0 @@ -55,376 +48,376 @@ static datetkn datetktbl[] = { aqtt arst #endif - {"art", TZ, NEG(12)}, /* Argentina Time */ + {"art", TZ, -10800}, /* Argentina Time */ #if 0 ashst ast /* Atlantic Standard Time, Arabia Standard * Time, Acre Standard Time */ #endif - {"ast", TZ, NEG(16)}, /* Atlantic Std Time (Canada) */ + {"ast", TZ, -14400}, /* Atlantic Std Time (Canada) */ {"at", IGNORE_DTF, 0}, /* "at" (throwaway) */ {"aug", MONTH, 8}, {"august", MONTH, 8}, - {"awsst", DTZ, POS(36)}, /* W. Australia */ - {"awst", TZ, POS(32)}, /* W. Australia */ - {"awt", DTZ, NEG(12)}, - {"azost", DTZ, POS(0)}, /* Azores Summer Time */ - {"azot", TZ, NEG(4)}, /* Azores Time */ - {"azst", DTZ, POS(20)}, /* Azerbaijan Summer Time */ - {"azt", TZ, POS(16)}, /* Azerbaijan Time */ + {"awsst", DTZ, 32400}, /* W. Australia */ + {"awst", TZ, 28800}, /* W. Australia */ + {"awt", DTZ, -10800}, + {"azost", DTZ, 0}, /* Azores Summer Time */ + {"azot", TZ, -3600}, /* Azores Time */ + {"azst", DTZ, 18000}, /* Azerbaijan Summer Time */ + {"azt", TZ, 14400}, /* Azerbaijan Time */ {DB_C, ADBC, BC}, /* "bc" for years < 0 */ - {"bdst", TZ, POS(8)}, /* British Double Summer Time */ - {"bdt", TZ, POS(24)}, /* Dacca */ - {"bnt", TZ, POS(32)}, /* Brunei Darussalam Time */ - {"bort", TZ, POS(32)}, /* Borneo Time (Indonesia) */ + {"bdst", TZ, 7200}, /* British Double Summer Time */ + {"bdt", TZ, 21600}, /* Dacca */ + {"bnt", TZ, 28800}, /* Brunei Darussalam Time */ + {"bort", TZ, 28800}, /* Borneo Time (Indonesia) */ #if 0 bortst bost #endif - {"bot", TZ, NEG(16)}, /* Bolivia Time */ - {"bra", TZ, NEG(12)}, /* Brazil Time */ + {"bot", TZ, -14400}, /* Bolivia Time */ + {"bra", TZ, -10800}, /* Brazil Time */ #if 0 brst brt #endif - {"bst", DTZ, POS(4)}, /* British Summer Time */ + {"bst", DTZ, 3600}, /* British Summer Time */ #if 0 - {"bst", TZ, NEG(12)}, /* Brazil Standard Time */ - {"bst", DTZ, NEG(44)}, /* Bering Summer Time */ + {"bst", TZ, -10800}, /* Brazil Standard Time */ + {"bst", DTZ, -39600}, /* Bering Summer Time */ #endif - {"bt", TZ, POS(12)}, /* Baghdad Time */ - {"btt", TZ, POS(24)}, /* Bhutan Time */ - {"cadt", DTZ, POS(42)}, /* Central Australian DST */ - {"cast", TZ, POS(38)}, /* Central Australian ST */ - {"cat", TZ, NEG(40)}, /* Central Alaska Time */ - {"cct", TZ, POS(32)}, /* China Coast Time */ + {"bt", TZ, 10800}, /* Baghdad Time */ + {"btt", TZ, 21600}, /* Bhutan Time */ + {"cadt", DTZ, 37800}, /* Central Australian DST */ + {"cast", TZ, 34200}, /* Central Australian ST */ + {"cat", TZ, -36000}, /* Central Alaska Time */ + {"cct", TZ, 28800}, /* China Coast Time */ #if 0 - {"cct", TZ, POS(26)}, /* Indian Cocos (Island) Time */ + {"cct", TZ, 23400}, /* Indian Cocos (Island) Time */ #endif - {"cdt", DTZ, NEG(20)}, /* Central Daylight Time */ - {"cest", DTZ, POS(8)}, /* Central European Dayl.Time */ - {"cet", TZ, POS(4)}, /* Central European Time */ - {"cetdst", DTZ, POS(8)}, /* Central European Dayl.Time */ - {"chadt", DTZ, POS(55)}, /* Chatham Island Daylight Time (13:45) */ - {"chast", TZ, POS(51)}, /* Chatham Island Time (12:45) */ + {"cdt", DTZ, -18000}, /* Central Daylight Time */ + {"cest", DTZ, 7200}, /* Central European Dayl.Time */ + {"cet", TZ, 3600}, /* Central European Time */ + {"cetdst", DTZ, 7200}, /* Central European Dayl.Time */ + {"chadt", DTZ, 49500}, /* Chatham Island Daylight Time (13:45) */ + {"chast", TZ, 45900}, /* Chatham Island Time (12:45) */ #if 0 ckhst #endif - {"ckt", TZ, POS(48)}, /* Cook Islands Time */ - {"clst", DTZ, NEG(12)}, /* Chile Summer Time */ - {"clt", TZ, NEG(16)}, /* Chile Time */ + {"ckt", TZ, 43200}, /* Cook Islands Time */ + {"clst", DTZ, -10800}, /* Chile Summer Time */ + {"clt", TZ, -14400}, /* Chile Time */ #if 0 cost #endif - {"cot", TZ, NEG(20)}, /* Columbia Time */ - {"cst", TZ, NEG(24)}, /* Central Standard Time */ + {"cot", TZ, -18000}, /* Columbia Time */ + {"cst", TZ, -21600}, /* Central Standard Time */ {DCURRENT, RESERV, DTK_CURRENT}, /* "current" is always now */ #if 0 cvst #endif - {"cvt", TZ, POS(28)}, /* Christmas Island Time (Indian Ocean) */ - {"cxt", TZ, POS(28)}, /* Christmas Island Time (Indian Ocean) */ + {"cvt", TZ, 25200}, /* Christmas Island Time (Indian Ocean) */ + {"cxt", TZ, 25200}, /* Christmas Island Time (Indian Ocean) */ {"d", UNITS, DTK_DAY}, /* "day of month" for ISO input */ - {"davt", TZ, POS(28)}, /* Davis Time (Antarctica) */ - {"ddut", TZ, POS(40)}, /* Dumont-d'Urville Time (Antarctica) */ + {"davt", TZ, 25200}, /* Davis Time (Antarctica) */ + {"ddut", TZ, 36000}, /* Dumont-d'Urville Time (Antarctica) */ {"dec", MONTH, 12}, {"december", MONTH, 12}, - {"dnt", TZ, POS(4)}, /* Dansk Normal Tid */ + {"dnt", TZ, 3600}, /* Dansk Normal Tid */ {"dow", RESERV, DTK_DOW}, /* day of week */ {"doy", RESERV, DTK_DOY}, /* day of year */ - {"dst", DTZMOD, 6}, + {"dst", DTZMOD, SECS_PER_HOUR}, #if 0 - {"dusst", DTZ, POS(24)}, /* Dushanbe Summer Time */ + {"dusst", DTZ, 21600}, /* Dushanbe Summer Time */ #endif - {"easst", DTZ, NEG(20)}, /* Easter Island Summer Time */ - {"east", TZ, NEG(24)}, /* Easter Island Time */ - {"eat", TZ, POS(12)}, /* East Africa Time */ + {"easst", DTZ, -18000}, /* Easter Island Summer Time */ + {"east", TZ, -21600}, /* Easter Island Time */ + {"eat", TZ, 10800}, /* East Africa Time */ #if 0 - {"east", DTZ, POS(16)}, /* Indian Antananarivo Savings Time */ - {"eat", TZ, POS(12)}, /* Indian Antananarivo Time */ - {"ect", TZ, NEG(16)}, /* Eastern Caribbean Time */ - {"ect", TZ, NEG(20)}, /* Ecuador Time */ + {"east", DTZ, 14400}, /* Indian Antananarivo Savings Time */ + {"eat", TZ, 10800}, /* Indian Antananarivo Time */ + {"ect", TZ, -14400}, /* Eastern Caribbean Time */ + {"ect", TZ, -18000}, /* Ecuador Time */ #endif - {"edt", DTZ, NEG(16)}, /* Eastern Daylight Time */ - {"eest", DTZ, POS(12)}, /* Eastern Europe Summer Time */ - {"eet", TZ, POS(8)}, /* East. Europe, USSR Zone 1 */ - {"eetdst", DTZ, POS(12)}, /* Eastern Europe Daylight Time */ - {"egst", DTZ, POS(0)}, /* East Greenland Summer Time */ - {"egt", TZ, NEG(4)}, /* East Greenland Time */ + {"edt", DTZ, -14400}, /* Eastern Daylight Time */ + {"eest", DTZ, 10800}, /* Eastern Europe Summer Time */ + {"eet", TZ, 7200}, /* East. Europe, USSR Zone 1 */ + {"eetdst", DTZ, 10800}, /* Eastern Europe Daylight Time */ + {"egst", DTZ, 0}, /* East Greenland Summer Time */ + {"egt", TZ, -3600}, /* East Greenland Time */ #if 0 ehdt #endif {EPOCH, RESERV, DTK_EPOCH}, /* "epoch" reserved for system epoch time */ - {"est", TZ, NEG(20)}, /* Eastern Standard Time */ + {"est", TZ, -18000}, /* Eastern Standard Time */ {"feb", MONTH, 2}, {"february", MONTH, 2}, - {"fjst", DTZ, NEG(52)}, /* Fiji Summer Time (13 hour offset!) */ - {"fjt", TZ, NEG(48)}, /* Fiji Time */ - {"fkst", DTZ, NEG(12)}, /* Falkland Islands Summer Time */ - {"fkt", TZ, NEG(8)}, /* Falkland Islands Time */ + {"fjst", DTZ, -46800}, /* Fiji Summer Time (13 hour offset!) */ + {"fjt", TZ, -43200}, /* Fiji Time */ + {"fkst", DTZ, -10800}, /* Falkland Islands Summer Time */ + {"fkt", TZ, -7200}, /* Falkland Islands Time */ #if 0 fnst fnt #endif {"fri", DOW, 5}, {"friday", DOW, 5}, - {"fst", TZ, POS(4)}, /* French Summer Time */ - {"fwt", DTZ, POS(8)}, /* French Winter Time */ - {"galt", TZ, NEG(24)}, /* Galapagos Time */ - {"gamt", TZ, NEG(36)}, /* Gambier Time */ - {"gest", DTZ, POS(20)}, /* Georgia Summer Time */ - {"get", TZ, POS(16)}, /* Georgia Time */ - {"gft", TZ, NEG(12)}, /* French Guiana Time */ + {"fst", TZ, 3600}, /* French Summer Time */ + {"fwt", DTZ, 7200}, /* French Winter Time */ + {"galt", TZ, -21600}, /* Galapagos Time */ + {"gamt", TZ, -32400}, /* Gambier Time */ + {"gest", DTZ, 18000}, /* Georgia Summer Time */ + {"get", TZ, 14400}, /* Georgia Time */ + {"gft", TZ, -10800}, /* French Guiana Time */ #if 0 ghst #endif - {"gilt", TZ, POS(48)}, /* Gilbert Islands Time */ - {"gmt", TZ, POS(0)}, /* Greenwish Mean Time */ - {"gst", TZ, POS(40)}, /* Guam Std Time, USSR Zone 9 */ - {"gyt", TZ, NEG(16)}, /* Guyana Time */ + {"gilt", TZ, 43200}, /* Gilbert Islands Time */ + {"gmt", TZ, 0}, /* Greenwish Mean Time */ + {"gst", TZ, 36000}, /* Guam Std Time, USSR Zone 9 */ + {"gyt", TZ, -14400}, /* Guyana Time */ {"h", UNITS, DTK_HOUR}, /* "hour" */ #if 0 hadt hast #endif - {"hdt", DTZ, NEG(36)}, /* Hawaii/Alaska Daylight Time */ + {"hdt", DTZ, -32400}, /* Hawaii/Alaska Daylight Time */ #if 0 hkst #endif - {"hkt", TZ, POS(32)}, /* Hong Kong Time */ + {"hkt", TZ, 28800}, /* Hong Kong Time */ #if 0 - {"hmt", TZ, POS(12)}, /* Hellas ? ? */ + {"hmt", TZ, 10800}, /* Hellas ? ? */ hovst hovt #endif - {"hst", TZ, NEG(40)}, /* Hawaii Std Time */ + {"hst", TZ, -36000}, /* Hawaii Std Time */ #if 0 hwt #endif - {"ict", TZ, POS(28)}, /* Indochina Time */ - {"idle", TZ, POS(48)}, /* Intl. Date Line, East */ - {"idlw", TZ, NEG(48)}, /* Intl. Date Line, West */ + {"ict", TZ, 25200}, /* Indochina Time */ + {"idle", TZ, 43200}, /* Intl. Date Line, East */ + {"idlw", TZ, -43200}, /* Intl. Date Line, West */ #if 0 idt /* Israeli, Iran, Indian Daylight Time */ #endif {LATE, RESERV, DTK_LATE}, /* "infinity" reserved for "late time" */ {INVALID, RESERV, DTK_INVALID}, /* "invalid" reserved for bad time */ - {"iot", TZ, POS(20)}, /* Indian Chagos Time */ - {"irkst", DTZ, POS(36)}, /* Irkutsk Summer Time */ - {"irkt", TZ, POS(32)}, /* Irkutsk Time */ - {"irt", TZ, POS(14)}, /* Iran Time */ + {"iot", TZ, 18000}, /* Indian Chagos Time */ + {"irkst", DTZ, 32400}, /* Irkutsk Summer Time */ + {"irkt", TZ, 28800}, /* Irkutsk Time */ + {"irt", TZ, 12600}, /* Iran Time */ {"isodow", RESERV, DTK_ISODOW}, /* ISO day of week, Sunday == 7 */ #if 0 isst #endif - {"ist", TZ, POS(8)}, /* Israel */ - {"it", TZ, POS(14)}, /* Iran Time */ + {"ist", TZ, 7200}, /* Israel */ + {"it", TZ, 12600}, /* Iran Time */ {"j", UNITS, DTK_JULIAN}, {"jan", MONTH, 1}, {"january", MONTH, 1}, - {"javt", TZ, POS(28)}, /* Java Time (07:00? see JT) */ - {"jayt", TZ, POS(36)}, /* Jayapura Time (Indonesia) */ + {"javt", TZ, 25200}, /* Java Time (07:00? see JT) */ + {"jayt", TZ, 32400}, /* Jayapura Time (Indonesia) */ {"jd", UNITS, DTK_JULIAN}, - {"jst", TZ, POS(36)}, /* Japan Std Time,USSR Zone 8 */ - {"jt", TZ, POS(30)}, /* Java Time (07:30? see JAVT) */ + {"jst", TZ, 32400}, /* Japan Std Time,USSR Zone 8 */ + {"jt", TZ, 27000}, /* Java Time (07:30? see JAVT) */ {"jul", MONTH, 7}, {"julian", UNITS, DTK_JULIAN}, {"july", MONTH, 7}, {"jun", MONTH, 6}, {"june", MONTH, 6}, - {"kdt", DTZ, POS(40)}, /* Korea Daylight Time */ - {"kgst", DTZ, POS(24)}, /* Kyrgyzstan Summer Time */ - {"kgt", TZ, POS(20)}, /* Kyrgyzstan Time */ - {"kost", TZ, POS(48)}, /* Kosrae Time */ - {"krast", DTZ, POS(28)}, /* Krasnoyarsk Summer Time */ - {"krat", TZ, POS(32)}, /* Krasnoyarsk Standard Time */ - {"kst", TZ, POS(36)}, /* Korea Standard Time */ - {"lhdt", DTZ, POS(44)}, /* Lord Howe Daylight Time, Australia */ - {"lhst", TZ, POS(42)}, /* Lord Howe Standard Time, Australia */ - {"ligt", TZ, POS(40)}, /* From Melbourne, Australia */ - {"lint", TZ, POS(56)}, /* Line Islands Time (Kiribati; +14 hours!) */ - {"lkt", TZ, POS(24)}, /* Lanka Time */ + {"kdt", DTZ, 36000}, /* Korea Daylight Time */ + {"kgst", DTZ, 21600}, /* Kyrgyzstan Summer Time */ + {"kgt", TZ, 18000}, /* Kyrgyzstan Time */ + {"kost", TZ, 43200}, /* Kosrae Time */ + {"krast", DTZ, 25200}, /* Krasnoyarsk Summer Time */ + {"krat", TZ, 28800}, /* Krasnoyarsk Standard Time */ + {"kst", TZ, 32400}, /* Korea Standard Time */ + {"lhdt", DTZ, 39600}, /* Lord Howe Daylight Time, Australia */ + {"lhst", TZ, 37800}, /* Lord Howe Standard Time, Australia */ + {"ligt", TZ, 36000}, /* From Melbourne, Australia */ + {"lint", TZ, 50400}, /* Line Islands Time (Kiribati; +14 hours!) */ + {"lkt", TZ, 21600}, /* Lanka Time */ {"m", UNITS, DTK_MONTH}, /* "month" for ISO input */ - {"magst", DTZ, POS(48)}, /* Magadan Summer Time */ - {"magt", TZ, POS(44)}, /* Magadan Time */ + {"magst", DTZ, 43200}, /* Magadan Summer Time */ + {"magt", TZ, 39600}, /* Magadan Time */ {"mar", MONTH, 3}, {"march", MONTH, 3}, - {"mart", TZ, NEG(38)}, /* Marquesas Time */ - {"mawt", TZ, POS(24)}, /* Mawson, Antarctica */ + {"mart", TZ, -34200}, /* Marquesas Time */ + {"mawt", TZ, 21600}, /* Mawson, Antarctica */ {"may", MONTH, 5}, - {"mdt", DTZ, NEG(24)}, /* Mountain Daylight Time */ - {"mest", DTZ, POS(8)}, /* Middle Europe Summer Time */ - {"met", TZ, POS(4)}, /* Middle Europe Time */ - {"metdst", DTZ, POS(8)}, /* Middle Europe Daylight Time */ - {"mewt", TZ, POS(4)}, /* Middle Europe Winter Time */ - {"mez", TZ, POS(4)}, /* Middle Europe Zone */ - {"mht", TZ, POS(48)}, /* Kwajalein */ + {"mdt", DTZ, -21600}, /* Mountain Daylight Time */ + {"mest", DTZ, 7200}, /* Middle Europe Summer Time */ + {"met", TZ, 3600}, /* Middle Europe Time */ + {"metdst", DTZ, 7200}, /* Middle Europe Daylight Time */ + {"mewt", TZ, 3600}, /* Middle Europe Winter Time */ + {"mez", TZ, 3600}, /* Middle Europe Zone */ + {"mht", TZ, 43200}, /* Kwajalein */ {"mm", UNITS, DTK_MINUTE}, /* "minute" for ISO input */ - {"mmt", TZ, POS(26)}, /* Myannar Time */ + {"mmt", TZ, 23400}, /* Myannar Time */ {"mon", DOW, 1}, {"monday", DOW, 1}, #if 0 most #endif - {"mpt", TZ, POS(40)}, /* North Mariana Islands Time */ - {"msd", DTZ, POS(16)}, /* Moscow Summer Time */ - {"msk", TZ, POS(12)}, /* Moscow Time */ - {"mst", TZ, NEG(28)}, /* Mountain Standard Time */ - {"mt", TZ, POS(34)}, /* Moluccas Time */ - {"mut", TZ, POS(16)}, /* Mauritius Island Time */ - {"mvt", TZ, POS(20)}, /* Maldives Island Time */ - {"myt", TZ, POS(32)}, /* Malaysia Time */ + {"mpt", TZ, 36000}, /* North Mariana Islands Time */ + {"msd", DTZ, 14400}, /* Moscow Summer Time */ + {"msk", TZ, 10800}, /* Moscow Time */ + {"mst", TZ, -25200}, /* Mountain Standard Time */ + {"mt", TZ, 30600}, /* Moluccas Time */ + {"mut", TZ, 14400}, /* Mauritius Island Time */ + {"mvt", TZ, 18000}, /* Maldives Island Time */ + {"myt", TZ, 28800}, /* Malaysia Time */ #if 0 ncst #endif - {"nct", TZ, POS(44)}, /* New Caledonia Time */ - {"ndt", DTZ, NEG(10)}, /* Nfld. Daylight Time */ - {"nft", TZ, NEG(14)}, /* Newfoundland Standard Time */ - {"nor", TZ, POS(4)}, /* Norway Standard Time */ + {"nct", TZ, 39600}, /* New Caledonia Time */ + {"ndt", DTZ, -9000}, /* Nfld. Daylight Time */ + {"nft", TZ, -12600}, /* Newfoundland Standard Time */ + {"nor", TZ, 3600}, /* Norway Standard Time */ {"nov", MONTH, 11}, {"november", MONTH, 11}, - {"novst", DTZ, POS(28)}, /* Novosibirsk Summer Time */ - {"novt", TZ, POS(24)}, /* Novosibirsk Standard Time */ + {"novst", DTZ, 25200}, /* Novosibirsk Summer Time */ + {"novt", TZ, 21600}, /* Novosibirsk Standard Time */ {NOW, RESERV, DTK_NOW}, /* current transaction time */ - {"npt", TZ, POS(23)}, /* Nepal Standard Time (GMT-5:45) */ - {"nst", TZ, NEG(14)}, /* Nfld. Standard Time */ - {"nt", TZ, NEG(44)}, /* Nome Time */ - {"nut", TZ, NEG(44)}, /* Niue Time */ - {"nzdt", DTZ, POS(52)}, /* New Zealand Daylight Time */ - {"nzst", TZ, POS(48)}, /* New Zealand Standard Time */ - {"nzt", TZ, POS(48)}, /* New Zealand Time */ + {"npt", TZ, 20700}, /* Nepal Standard Time (GMT-5:45) */ + {"nst", TZ, -12600}, /* Nfld. Standard Time */ + {"nt", TZ, -39600}, /* Nome Time */ + {"nut", TZ, -39600}, /* Niue Time */ + {"nzdt", DTZ, 46800}, /* New Zealand Daylight Time */ + {"nzst", TZ, 43200}, /* New Zealand Standard Time */ + {"nzt", TZ, 43200}, /* New Zealand Time */ {"oct", MONTH, 10}, {"october", MONTH, 10}, - {"omsst", DTZ, POS(28)}, /* Omsk Summer Time */ - {"omst", TZ, POS(24)}, /* Omsk Time */ + {"omsst", DTZ, 25200}, /* Omsk Summer Time */ + {"omst", TZ, 21600}, /* Omsk Time */ {"on", IGNORE_DTF, 0}, /* "on" (throwaway) */ - {"pdt", DTZ, NEG(28)}, /* Pacific Daylight Time */ + {"pdt", DTZ, -25200}, /* Pacific Daylight Time */ #if 0 pest #endif - {"pet", TZ, NEG(20)}, /* Peru Time */ - {"petst", DTZ, POS(52)}, /* Petropavlovsk-Kamchatski Summer Time */ - {"pett", TZ, POS(48)}, /* Petropavlovsk-Kamchatski Time */ - {"pgt", TZ, POS(40)}, /* Papua New Guinea Time */ - {"phot", TZ, POS(52)}, /* Phoenix Islands (Kiribati) Time */ + {"pet", TZ, -18000}, /* Peru Time */ + {"petst", DTZ, 46800}, /* Petropavlovsk-Kamchatski Summer Time */ + {"pett", TZ, 43200}, /* Petropavlovsk-Kamchatski Time */ + {"pgt", TZ, 36000}, /* Papua New Guinea Time */ + {"phot", TZ, 46800}, /* Phoenix Islands (Kiribati) Time */ #if 0 phst #endif - {"pht", TZ, POS(32)}, /* Philippine Time */ - {"pkt", TZ, POS(20)}, /* Pakistan Time */ + {"pht", TZ, 28800}, /* Philippine Time */ + {"pkt", TZ, 18000}, /* Pakistan Time */ {"pm", AMPM, PM}, - {"pmdt", DTZ, NEG(8)}, /* Pierre & Miquelon Daylight Time */ + {"pmdt", DTZ, -7200}, /* Pierre & Miquelon Daylight Time */ #if 0 pmst #endif - {"pont", TZ, POS(44)}, /* Ponape Time (Micronesia) */ - {"pst", TZ, NEG(32)}, /* Pacific Standard Time */ - {"pwt", TZ, POS(36)}, /* Palau Time */ - {"pyst", DTZ, NEG(12)}, /* Paraguay Summer Time */ - {"pyt", TZ, NEG(16)}, /* Paraguay Time */ - {"ret", DTZ, POS(16)}, /* Reunion Island Time */ + {"pont", TZ, 39600}, /* Ponape Time (Micronesia) */ + {"pst", TZ, -28800}, /* Pacific Standard Time */ + {"pwt", TZ, 32400}, /* Palau Time */ + {"pyst", DTZ, -10800}, /* Paraguay Summer Time */ + {"pyt", TZ, -14400}, /* Paraguay Time */ + {"ret", DTZ, 14400}, /* Reunion Island Time */ {"s", UNITS, DTK_SECOND}, /* "seconds" for ISO input */ - {"sadt", DTZ, POS(42)}, /* S. Australian Dayl. Time */ + {"sadt", DTZ, 37800}, /* S. Australian Dayl. Time */ #if 0 samst samt #endif - {"sast", TZ, POS(38)}, /* South Australian Std Time */ + {"sast", TZ, 34200}, /* South Australian Std Time */ {"sat", DOW, 6}, {"saturday", DOW, 6}, #if 0 sbt #endif - {"sct", DTZ, POS(16)}, /* Mahe Island Time */ + {"sct", DTZ, 14400}, /* Mahe Island Time */ {"sep", MONTH, 9}, {"sept", MONTH, 9}, {"september", MONTH, 9}, - {"set", TZ, NEG(4)}, /* Seychelles Time ?? */ + {"set", TZ, -3600}, /* Seychelles Time ?? */ #if 0 sgt #endif - {"sst", DTZ, POS(8)}, /* Swedish Summer Time */ + {"sst", DTZ, 7200}, /* Swedish Summer Time */ {"sun", DOW, 0}, {"sunday", DOW, 0}, - {"swt", TZ, POS(4)}, /* Swedish Winter Time */ + {"swt", TZ, 3600}, /* Swedish Winter Time */ #if 0 syot #endif {"t", ISOTIME, DTK_TIME}, /* Filler for ISO time fields */ - {"tft", TZ, POS(20)}, /* Kerguelen Time */ - {"that", TZ, NEG(40)}, /* Tahiti Time */ + {"tft", TZ, 18000}, /* Kerguelen Time */ + {"that", TZ, -36000}, /* Tahiti Time */ {"thu", DOW, 4}, {"thur", DOW, 4}, {"thurs", DOW, 4}, {"thursday", DOW, 4}, - {"tjt", TZ, POS(20)}, /* Tajikistan Time */ - {"tkt", TZ, NEG(40)}, /* Tokelau Time */ - {"tmt", TZ, POS(20)}, /* Turkmenistan Time */ + {"tjt", TZ, 18000}, /* Tajikistan Time */ + {"tkt", TZ, -36000}, /* Tokelau Time */ + {"tmt", TZ, 18000}, /* Turkmenistan Time */ {TODAY, RESERV, DTK_TODAY}, /* midnight */ {TOMORROW, RESERV, DTK_TOMORROW}, /* tomorrow midnight */ #if 0 tost #endif - {"tot", TZ, POS(52)}, /* Tonga Time */ + {"tot", TZ, 46800}, /* Tonga Time */ #if 0 tpt #endif - {"truk", TZ, POS(40)}, /* Truk Time */ + {"truk", TZ, 36000}, /* Truk Time */ {"tue", DOW, 2}, {"tues", DOW, 2}, {"tuesday", DOW, 2}, - {"tvt", TZ, POS(48)}, /* Tuvalu Time */ + {"tvt", TZ, 43200}, /* Tuvalu Time */ #if 0 uct #endif - {"ulast", DTZ, POS(36)}, /* Ulan Bator Summer Time */ - {"ulat", TZ, POS(32)}, /* Ulan Bator Time */ + {"ulast", DTZ, 32400}, /* Ulan Bator Summer Time */ + {"ulat", TZ, 28800}, /* Ulan Bator Time */ {"undefined", RESERV, DTK_INVALID}, /* pre-v6.1 invalid time */ - {"ut", TZ, POS(0)}, - {"utc", TZ, POS(0)}, - {"uyst", DTZ, NEG(8)}, /* Uruguay Summer Time */ - {"uyt", TZ, NEG(12)}, /* Uruguay Time */ - {"uzst", DTZ, POS(24)}, /* Uzbekistan Summer Time */ - {"uzt", TZ, POS(20)}, /* Uzbekistan Time */ - {"vet", TZ, NEG(16)}, /* Venezuela Time */ - {"vlast", DTZ, POS(44)}, /* Vladivostok Summer Time */ - {"vlat", TZ, POS(40)}, /* Vladivostok Time */ + {"ut", TZ, 0}, + {"utc", TZ, 0}, + {"uyst", DTZ, -7200}, /* Uruguay Summer Time */ + {"uyt", TZ, -10800}, /* Uruguay Time */ + {"uzst", DTZ, 21600}, /* Uzbekistan Summer Time */ + {"uzt", TZ, 18000}, /* Uzbekistan Time */ + {"vet", TZ, -14400}, /* Venezuela Time */ + {"vlast", DTZ, 39600}, /* Vladivostok Summer Time */ + {"vlat", TZ, 36000}, /* Vladivostok Time */ #if 0 vust #endif - {"vut", TZ, POS(44)}, /* Vanuata Time */ - {"wadt", DTZ, POS(32)}, /* West Australian DST */ - {"wakt", TZ, POS(48)}, /* Wake Time */ + {"vut", TZ, 39600}, /* Vanuata Time */ + {"wadt", DTZ, 28800}, /* West Australian DST */ + {"wakt", TZ, 43200}, /* Wake Time */ #if 0 warst #endif - {"wast", TZ, POS(28)}, /* West Australian Std Time */ - {"wat", TZ, NEG(4)}, /* West Africa Time */ - {"wdt", DTZ, POS(36)}, /* West Australian DST */ + {"wast", TZ, 25200}, /* West Australian Std Time */ + {"wat", TZ, -3600}, /* West Africa Time */ + {"wdt", DTZ, 32400}, /* West Australian DST */ {"wed", DOW, 3}, {"wednesday", DOW, 3}, {"weds", DOW, 3}, - {"west", DTZ, POS(4)}, /* Western Europe Summer Time */ - {"wet", TZ, POS(0)}, /* Western Europe */ - {"wetdst", DTZ, POS(4)}, /* Western Europe Daylight Savings Time */ - {"wft", TZ, POS(48)}, /* Wallis and Futuna Time */ - {"wgst", DTZ, NEG(8)}, /* West Greenland Summer Time */ - {"wgt", TZ, NEG(12)}, /* West Greenland Time */ - {"wst", TZ, POS(32)}, /* West Australian Standard Time */ + {"west", DTZ, 3600}, /* Western Europe Summer Time */ + {"wet", TZ, 0}, /* Western Europe */ + {"wetdst", DTZ, 3600}, /* Western Europe Daylight Savings Time */ + {"wft", TZ, 43200}, /* Wallis and Futuna Time */ + {"wgst", DTZ, -7200}, /* West Greenland Summer Time */ + {"wgt", TZ, -10800}, /* West Greenland Time */ + {"wst", TZ, 28800}, /* West Australian Standard Time */ {"y", UNITS, DTK_YEAR}, /* "year" for ISO input */ - {"yakst", DTZ, POS(40)}, /* Yakutsk Summer Time */ - {"yakt", TZ, POS(36)}, /* Yakutsk Time */ - {"yapt", TZ, POS(40)}, /* Yap Time (Micronesia) */ - {"ydt", DTZ, NEG(32)}, /* Yukon Daylight Time */ - {"yekst", DTZ, POS(24)}, /* Yekaterinburg Summer Time */ - {"yekt", TZ, POS(20)}, /* Yekaterinburg Time */ + {"yakst", DTZ, 36000}, /* Yakutsk Summer Time */ + {"yakt", TZ, 32400}, /* Yakutsk Time */ + {"yapt", TZ, 36000}, /* Yap Time (Micronesia) */ + {"ydt", DTZ, -28800}, /* Yukon Daylight Time */ + {"yekst", DTZ, 21600}, /* Yekaterinburg Summer Time */ + {"yekt", TZ, 18000}, /* Yekaterinburg Time */ {YESTERDAY, RESERV, DTK_YESTERDAY}, /* yesterday midnight */ - {"yst", TZ, NEG(36)}, /* Yukon Standard Time */ - {"z", TZ, POS(0)}, /* time zone tag per ISO-8601 */ - {"zp4", TZ, NEG(16)}, /* UTC +4 hours. */ - {"zp5", TZ, NEG(20)}, /* UTC +5 hours. */ - {"zp6", TZ, NEG(24)}, /* UTC +6 hours. */ - {ZULU, TZ, POS(0)}, /* UTC */ + {"yst", TZ, -32400}, /* Yukon Standard Time */ + {"z", TZ, 0}, /* time zone tag per ISO-8601 */ + {"zp4", TZ, -14400}, /* UTC +4 hours. */ + {"zp5", TZ, -18000}, /* UTC +5 hours. */ + {"zp6", TZ, -21600}, /* UTC +6 hours. */ + {ZULU, TZ, 0}, /* UTC */ }; static datetkn deltatktbl[] = { @@ -521,9 +514,11 @@ datebsearch(char *key, datetkn *base, unsigned int nel) while (last >= base) { position = base + ((last - base) >> 1); - result = key[0] - position->token[0]; + /* precheck the first character for a bit of extra speed */ + result = (int) key[0] - (int) position->token[0]; if (result == 0) { + /* use strncmp so that we match truncated tokens */ result = strncmp(key, position->token, TOKMAXLEN); if (result == 0) return position; @@ -547,6 +542,7 @@ DecodeUnits(int field, char *lowtoken, int *val) int type; datetkn *tp; + /* use strncmp so that we match truncated tokens */ if (deltacache[field] != NULL && strncmp(lowtoken, deltacache[field]->token, TOKMAXLEN) == 0) tp = deltacache[field]; @@ -561,10 +557,7 @@ DecodeUnits(int field, char *lowtoken, int *val) else { type = tp->type; - if (type == TZ || type == DTZ) - *val = FROMVAL(tp); - else - *val = tp->value; + *val = tp->value; } return type; @@ -650,6 +643,7 @@ DecodeSpecial(int field, char *lowtoken, int *val) int type; datetkn *tp; + /* use strncmp so that we match truncated tokens */ if (datecache[field] != NULL && strncmp(lowtoken, datecache[field]->token, TOKMAXLEN) == 0) tp = datecache[field]; @@ -668,18 +662,7 @@ DecodeSpecial(int field, char *lowtoken, int *val) else { type = tp->type; - switch (type) - { - case TZ: - case DTZ: - case DTZMOD: - *val = FROMVAL(tp); - break; - - default: - *val = tp->value; - break; - } + *val = tp->value; } return type; @@ -1656,7 +1639,7 @@ DecodePosixTimezone(char *str, int *tzp) { case DTZ: case TZ: - *tzp = (val * MINS_PER_HOUR) - tz; + *tzp = -(val + tz); break; default: @@ -2308,7 +2291,7 @@ DecodeDateTime(char **field, int *ftype, int nf, tm->tm_isdst = 1; if (tzp == NULL) return -1; - *tzp += val * MINS_PER_HOUR; + *tzp -= val; break; case DTZ: @@ -2321,7 +2304,7 @@ DecodeDateTime(char **field, int *ftype, int nf, tm->tm_isdst = 1; if (tzp == NULL) return -1; - *tzp = val * MINS_PER_HOUR; + *tzp = -val; ftype[i] = DTK_TZ; break; @@ -2329,7 +2312,7 @@ DecodeDateTime(char **field, int *ftype, int nf, tm->tm_isdst = 0; if (tzp == NULL) return -1; - *tzp = val * MINS_PER_HOUR; + *tzp = -val; ftype[i] = DTK_TZ; break; @@ -3000,25 +2983,26 @@ PGTYPEStimestamp_defmt_scan(char **str, char *fmt, timestamp * d, pfmt++; scan_type = PGTYPES_TYPE_STRING_MALLOCED; err = pgtypes_defmt_scan(&scan_val, scan_type, &pstr, pfmt); - - /* - * XXX use DecodeSpecial instead ? - it's declared static but - * the arrays as well. :-( - */ - for (j = 0; !err && j < szdatetktbl; j++) + if (!err) { - if (pg_strcasecmp(datetktbl[j].token, scan_val.str_val) == 0) + /* + * XXX use DecodeSpecial instead? Do we need strcasecmp + * here? + */ + err = 1; + for (j = 0; j < szdatetktbl; j++) { - /* - * tz calculates the offset for the seconds, the - * timezone value of the datetktbl table is in quarter - * hours - */ - *tz = -15 * MINS_PER_HOUR * datetktbl[j].value; - break; + if ((datetktbl[j].type == TZ || datetktbl[j].type == DTZ) && + pg_strcasecmp(datetktbl[j].token, + scan_val.str_val) == 0) + { + *tz = -datetktbl[j].value; + err = 0; + break; + } } + free(scan_val.str_val); } - free(scan_val.str_val); break; case '+': /* XXX */ diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index f95617b01a3..552f3d1aa5e 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -1805,3 +1805,683 @@ SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, 'PST8PDT'); (1 row) RESET TimeZone; +-- +-- Test behavior with a dynamic (time-varying) timezone abbreviation. +-- These tests rely on the knowledge that MSK (Europe/Moscow standard time) +-- changed meaning in Mar 2011 and back again in Oct 2014. +-- +SET TimeZone to 'UTC'; +SELECT '2011-03-27 00:00:00 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 21:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 01:00:00 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 22:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 01:59:59 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 22:59:59 2011 UTC +(1 row) + +SELECT '2011-03-27 02:00:00 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 23:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 02:00:01 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 23:00:01 2011 UTC +(1 row) + +SELECT '2011-03-27 02:59:59 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 23:59:59 2011 UTC +(1 row) + +SELECT '2011-03-27 03:00:00 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 23:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 03:00:01 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 23:00:01 2011 UTC +(1 row) + +SELECT '2011-03-27 04:00:00 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sun Mar 27 00:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 00:00:00 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 21:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 01:00:00 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 22:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 01:59:59 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 22:59:59 2011 UTC +(1 row) + +SELECT '2011-03-27 02:00:00 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 22:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 02:00:01 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 22:00:01 2011 UTC +(1 row) + +SELECT '2011-03-27 02:59:59 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 22:59:59 2011 UTC +(1 row) + +SELECT '2011-03-27 03:00:00 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 23:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 03:00:01 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Mar 26 23:00:01 2011 UTC +(1 row) + +SELECT '2011-03-27 04:00:00 MSK'::timestamptz; + timestamptz +------------------------------ + Sun Mar 27 00:00:00 2011 UTC +(1 row) + +SELECT '2014-10-26 00:00:00 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 20:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 00:59:59 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 20:59:59 2014 UTC +(1 row) + +SELECT '2014-10-26 01:00:00 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 22:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 01:00:01 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 22:00:01 2014 UTC +(1 row) + +SELECT '2014-10-26 01:59:59 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 22:59:59 2014 UTC +(1 row) + +SELECT '2014-10-26 02:00:00 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 23:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 02:00:01 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 23:00:01 2014 UTC +(1 row) + +SELECT '2014-10-26 03:00:00 Europe/Moscow'::timestamptz; + timestamptz +------------------------------ + Sun Oct 26 00:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 00:00:00 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 20:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 00:59:59 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 20:59:59 2014 UTC +(1 row) + +SELECT '2014-10-26 01:00:00 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 22:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 01:00:01 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 22:00:01 2014 UTC +(1 row) + +SELECT '2014-10-26 01:59:59 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 22:59:59 2014 UTC +(1 row) + +SELECT '2014-10-26 02:00:00 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 23:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 02:00:01 MSK'::timestamptz; + timestamptz +------------------------------ + Sat Oct 25 23:00:01 2014 UTC +(1 row) + +SELECT '2014-10-26 03:00:00 MSK'::timestamptz; + timestamptz +------------------------------ + Sun Oct 26 00:00:00 2014 UTC +(1 row) + +SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Mar 26 21:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Mar 26 22:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Mar 26 22:59:59 2011 UTC +(1 row) + +SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Mar 26 23:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Mar 26 23:00:01 2011 UTC +(1 row) + +SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Mar 26 23:59:59 2011 UTC +(1 row) + +SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Mar 26 23:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Mar 26 23:00:01 2011 UTC +(1 row) + +SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sun Mar 27 00:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Mar 26 21:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Mar 26 22:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Mar 26 22:59:59 2011 UTC +(1 row) + +SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Mar 26 22:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Mar 26 22:00:01 2011 UTC +(1 row) + +SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Mar 26 22:59:59 2011 UTC +(1 row) + +SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Mar 26 23:00:00 2011 UTC +(1 row) + +SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Mar 26 23:00:01 2011 UTC +(1 row) + +SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sun Mar 27 00:00:00 2011 UTC +(1 row) + +SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Oct 25 20:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Oct 25 20:59:59 2014 UTC +(1 row) + +SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Oct 25 22:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Oct 25 22:00:01 2014 UTC +(1 row) + +SELECT '2014-10-26 01:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Oct 25 22:59:59 2014 UTC +(1 row) + +SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Oct 25 23:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 02:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sat Oct 25 23:00:01 2014 UTC +(1 row) + +SELECT '2014-10-26 03:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + timezone +------------------------------ + Sun Oct 26 00:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Oct 25 20:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Oct 25 20:59:59 2014 UTC +(1 row) + +SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Oct 25 22:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Oct 25 22:00:01 2014 UTC +(1 row) + +SELECT '2014-10-26 01:59:59'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Oct 25 22:59:59 2014 UTC +(1 row) + +SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Oct 25 23:00:00 2014 UTC +(1 row) + +SELECT '2014-10-26 02:00:01'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sat Oct 25 23:00:01 2014 UTC +(1 row) + +SELECT '2014-10-26 03:00:00'::timestamp AT TIME ZONE 'MSK'; + timezone +------------------------------ + Sun Oct 26 00:00:00 2014 UTC +(1 row) + +SELECT make_timestamptz(2014, 10, 26, 0, 0, 0, 'MSK'); + make_timestamptz +------------------------------ + Sat Oct 25 20:00:00 2014 UTC +(1 row) + +SELECT make_timestamptz(2014, 10, 26, 3, 0, 0, 'MSK'); + make_timestamptz +------------------------------ + Sun Oct 26 00:00:00 2014 UTC +(1 row) + +SET TimeZone to 'Europe/Moscow'; +SELECT '2011-03-26 21:00:00 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Mar 27 00:00:00 2011 MSK +(1 row) + +SELECT '2011-03-26 22:00:00 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Mar 27 01:00:00 2011 MSK +(1 row) + +SELECT '2011-03-26 22:59:59 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Mar 27 01:59:59 2011 MSK +(1 row) + +SELECT '2011-03-26 23:00:00 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Mar 27 03:00:00 2011 MSK +(1 row) + +SELECT '2011-03-26 23:00:01 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Mar 27 03:00:01 2011 MSK +(1 row) + +SELECT '2011-03-26 23:59:59 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Mar 27 03:59:59 2011 MSK +(1 row) + +SELECT '2011-03-27 00:00:00 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Mar 27 04:00:00 2011 MSK +(1 row) + +SELECT '2014-10-25 20:00:00 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Oct 26 00:00:00 2014 MSK +(1 row) + +SELECT '2014-10-25 21:00:00 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Oct 26 01:00:00 2014 MSK +(1 row) + +SELECT '2014-10-25 21:59:59 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Oct 26 01:59:59 2014 MSK +(1 row) + +SELECT '2014-10-25 22:00:00 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Oct 26 01:00:00 2014 MSK +(1 row) + +SELECT '2014-10-25 22:00:01 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Oct 26 01:00:01 2014 MSK +(1 row) + +SELECT '2014-10-25 22:59:59 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Oct 26 01:59:59 2014 MSK +(1 row) + +SELECT '2014-10-25 23:00:00 UTC'::timestamptz; + timestamptz +------------------------------ + Sun Oct 26 02:00:00 2014 MSK +(1 row) + +RESET TimeZone; +SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Mar 27 00:00:00 2011 +(1 row) + +SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Mar 27 01:00:00 2011 +(1 row) + +SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Mar 27 01:59:59 2011 +(1 row) + +SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Mar 27 03:00:00 2011 +(1 row) + +SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Mar 27 03:00:01 2011 +(1 row) + +SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Mar 27 03:59:59 2011 +(1 row) + +SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Mar 27 04:00:00 2011 +(1 row) + +SELECT '2014-10-25 20:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Oct 26 00:00:00 2014 +(1 row) + +SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Oct 26 01:00:00 2014 +(1 row) + +SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Oct 26 01:59:59 2014 +(1 row) + +SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Oct 26 01:00:00 2014 +(1 row) + +SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Oct 26 01:00:01 2014 +(1 row) + +SELECT '2014-10-25 22:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Oct 26 01:59:59 2014 +(1 row) + +SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + timezone +-------------------------- + Sun Oct 26 02:00:00 2014 +(1 row) + +SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Mar 27 00:00:00 2011 +(1 row) + +SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Mar 27 01:00:00 2011 +(1 row) + +SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Mar 27 01:59:59 2011 +(1 row) + +SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Mar 27 03:00:00 2011 +(1 row) + +SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Mar 27 03:00:01 2011 +(1 row) + +SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Mar 27 03:59:59 2011 +(1 row) + +SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Mar 27 04:00:00 2011 +(1 row) + +SELECT '2014-10-25 20:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Oct 26 00:00:00 2014 +(1 row) + +SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Oct 26 01:00:00 2014 +(1 row) + +SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Oct 26 01:59:59 2014 +(1 row) + +SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Oct 26 01:00:00 2014 +(1 row) + +SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Oct 26 01:00:01 2014 +(1 row) + +SELECT '2014-10-25 22:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Oct 26 01:59:59 2014 +(1 row) + +SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; + timezone +-------------------------- + Sun Oct 26 02:00:00 2014 +(1 row) + diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index ec001f85f37..92b5bbc1ba6 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -290,3 +290,142 @@ SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'CLT'); SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, 'PST8PDT'); RESET TimeZone; + +-- +-- Test behavior with a dynamic (time-varying) timezone abbreviation. +-- These tests rely on the knowledge that MSK (Europe/Moscow standard time) +-- changed meaning in Mar 2011 and back again in Oct 2014. +-- + +SET TimeZone to 'UTC'; + +SELECT '2011-03-27 00:00:00 Europe/Moscow'::timestamptz; +SELECT '2011-03-27 01:00:00 Europe/Moscow'::timestamptz; +SELECT '2011-03-27 01:59:59 Europe/Moscow'::timestamptz; +SELECT '2011-03-27 02:00:00 Europe/Moscow'::timestamptz; +SELECT '2011-03-27 02:00:01 Europe/Moscow'::timestamptz; +SELECT '2011-03-27 02:59:59 Europe/Moscow'::timestamptz; +SELECT '2011-03-27 03:00:00 Europe/Moscow'::timestamptz; +SELECT '2011-03-27 03:00:01 Europe/Moscow'::timestamptz; +SELECT '2011-03-27 04:00:00 Europe/Moscow'::timestamptz; + +SELECT '2011-03-27 00:00:00 MSK'::timestamptz; +SELECT '2011-03-27 01:00:00 MSK'::timestamptz; +SELECT '2011-03-27 01:59:59 MSK'::timestamptz; +SELECT '2011-03-27 02:00:00 MSK'::timestamptz; +SELECT '2011-03-27 02:00:01 MSK'::timestamptz; +SELECT '2011-03-27 02:59:59 MSK'::timestamptz; +SELECT '2011-03-27 03:00:00 MSK'::timestamptz; +SELECT '2011-03-27 03:00:01 MSK'::timestamptz; +SELECT '2011-03-27 04:00:00 MSK'::timestamptz; + +SELECT '2014-10-26 00:00:00 Europe/Moscow'::timestamptz; +SELECT '2014-10-26 00:59:59 Europe/Moscow'::timestamptz; +SELECT '2014-10-26 01:00:00 Europe/Moscow'::timestamptz; +SELECT '2014-10-26 01:00:01 Europe/Moscow'::timestamptz; +SELECT '2014-10-26 01:59:59 Europe/Moscow'::timestamptz; +SELECT '2014-10-26 02:00:00 Europe/Moscow'::timestamptz; +SELECT '2014-10-26 02:00:01 Europe/Moscow'::timestamptz; +SELECT '2014-10-26 03:00:00 Europe/Moscow'::timestamptz; + +SELECT '2014-10-26 00:00:00 MSK'::timestamptz; +SELECT '2014-10-26 00:59:59 MSK'::timestamptz; +SELECT '2014-10-26 01:00:00 MSK'::timestamptz; +SELECT '2014-10-26 01:00:01 MSK'::timestamptz; +SELECT '2014-10-26 01:59:59 MSK'::timestamptz; +SELECT '2014-10-26 02:00:00 MSK'::timestamptz; +SELECT '2014-10-26 02:00:01 MSK'::timestamptz; +SELECT '2014-10-26 03:00:00 MSK'::timestamptz; + +SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + +SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'MSK'; +SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'MSK'; +SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'MSK'; +SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'MSK'; +SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'MSK'; +SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'MSK'; +SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'MSK'; +SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'MSK'; +SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'MSK'; + +SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-26 01:59:59'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-26 02:00:01'::timestamp AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-26 03:00:00'::timestamp AT TIME ZONE 'Europe/Moscow'; + +SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'MSK'; +SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'MSK'; +SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'MSK'; +SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'MSK'; +SELECT '2014-10-26 01:59:59'::timestamp AT TIME ZONE 'MSK'; +SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'MSK'; +SELECT '2014-10-26 02:00:01'::timestamp AT TIME ZONE 'MSK'; +SELECT '2014-10-26 03:00:00'::timestamp AT TIME ZONE 'MSK'; + +SELECT make_timestamptz(2014, 10, 26, 0, 0, 0, 'MSK'); +SELECT make_timestamptz(2014, 10, 26, 3, 0, 0, 'MSK'); + +SET TimeZone to 'Europe/Moscow'; + +SELECT '2011-03-26 21:00:00 UTC'::timestamptz; +SELECT '2011-03-26 22:00:00 UTC'::timestamptz; +SELECT '2011-03-26 22:59:59 UTC'::timestamptz; +SELECT '2011-03-26 23:00:00 UTC'::timestamptz; +SELECT '2011-03-26 23:00:01 UTC'::timestamptz; +SELECT '2011-03-26 23:59:59 UTC'::timestamptz; +SELECT '2011-03-27 00:00:00 UTC'::timestamptz; + +SELECT '2014-10-25 20:00:00 UTC'::timestamptz; +SELECT '2014-10-25 21:00:00 UTC'::timestamptz; +SELECT '2014-10-25 21:59:59 UTC'::timestamptz; +SELECT '2014-10-25 22:00:00 UTC'::timestamptz; +SELECT '2014-10-25 22:00:01 UTC'::timestamptz; +SELECT '2014-10-25 22:59:59 UTC'::timestamptz; +SELECT '2014-10-25 23:00:00 UTC'::timestamptz; + +RESET TimeZone; + +SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + +SELECT '2014-10-25 20:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-25 22:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; +SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow'; + +SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; + +SELECT '2014-10-25 20:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2014-10-25 22:59:59 UTC'::timestamptz AT TIME ZONE 'MSK'; +SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK'; diff --git a/src/timezone/known_abbrevs.txt b/src/timezone/known_abbrevs.txt index f309a48066b..db78cf3492b 100644 --- a/src/timezone/known_abbrevs.txt +++ b/src/timezone/known_abbrevs.txt @@ -85,6 +85,7 @@ IDT 10800 D IOT 21600 IRDT 16200 D IRKT 28800 +IRKT 32400 IRST 12600 IST 19800 IST 3600 D @@ -93,11 +94,13 @@ JST 32400 KGT 21600 KOST 39600 KRAT 25200 +KRAT 28800 KST 32400 LHDT 39600 D LHST 37800 LINT 50400 MAGT 36000 +MAGT 43200 MART -34200 MAWT 18000 MDT -21600 D @@ -107,6 +110,7 @@ MHT 43200 MIST 39600 MMT 23400 MSK 10800 +MSK 14400 MST -25200 MUT 14400 MVT 18000 @@ -115,6 +119,7 @@ NCT 39600 NDT -9000 D NFT 41400 NOVT 21600 +NOVT 25200 NPT 20700 NRT 43200 NST -12600 @@ -122,6 +127,7 @@ NUT -39600 NZDT 46800 D NZST 43200 OMST 21600 +OMST 25200 ORAT 18000 PDT -25200 D PET -18000 @@ -141,6 +147,7 @@ QYZT 21600 RET 14400 ROTT -10800 SAKT 36000 +SAKT 39600 SAMT 14400 SAST 7200 SBT 39600 @@ -165,6 +172,7 @@ UYT -10800 UZT 18000 VET -16200 VLAT 36000 +VLAT 39600 VOST 21600 VUT 39600 WAKT 43200 @@ -182,4 +190,6 @@ WSDT 50400 D WSST 46800 XJT 21600 YAKT 32400 +YAKT 36000 YEKT 18000 +YEKT 21600 diff --git a/src/timezone/localtime.c b/src/timezone/localtime.c index 85b227c9255..19a24e1d960 100644 --- a/src/timezone/localtime.c +++ b/src/timezone/localtime.c @@ -1292,9 +1292,9 @@ increment_overflow(int *number, int delta) } /* - * Find the next DST transition time after the given time + * Find the next DST transition time in the given zone after the given time * - * *timep is the input value, the other parameters are output values. + * *timep and *tz are input arguments, the other parameters are output values. * * When the function result is 1, *boundary is set to the time_t * representation of the next DST transition time after *timep, @@ -1445,6 +1445,110 @@ pg_next_dst_boundary(const pg_time_t *timep, } /* + * Identify a timezone abbreviation's meaning in the given zone + * + * Determine the GMT offset and DST flag associated with the abbreviation. + * This is generally used only when the abbreviation has actually changed + * meaning over time; therefore, we also take a UTC cutoff time, and return + * the meaning in use at or most recently before that time, or the meaning + * in first use after that time if the abbrev was never used before that. + * + * On success, returns TRUE and sets *gmtoff and *isdst. If the abbreviation + * was never used at all in this zone, returns FALSE. + * + * Note: abbrev is matched case-sensitively; it should be all-upper-case. + */ +bool +pg_interpret_timezone_abbrev(const char *abbrev, + const pg_time_t *timep, + long int *gmtoff, + int *isdst, + const pg_tz *tz) +{ + const struct state *sp; + const char *abbrs; + const struct ttinfo *ttisp; + int abbrind; + int cutoff; + int i; + const pg_time_t t = *timep; + + sp = &tz->state; + + /* + * Locate the abbreviation in the zone's abbreviation list. We assume + * there are not duplicates in the list. + */ + abbrs = sp->chars; + abbrind = 0; + while (abbrind < sp->charcnt) + { + if (strcmp(abbrev, abbrs + abbrind) == 0) + break; + while (abbrs[abbrind] != '\0') + abbrind++; + abbrind++; + } + if (abbrind >= sp->charcnt) + return FALSE; /* not there! */ + + /* + * Unlike pg_next_dst_boundary, we needn't sweat about extrapolation + * (goback/goahead zones). Finding the newest or oldest meaning of the + * abbreviation should get us what we want, since extrapolation would just + * be repeating the newest or oldest meanings. + * + * Use binary search to locate the first transition > cutoff time. + */ + { + int lo = 0; + int hi = sp->timecnt; + + while (lo < hi) + { + int mid = (lo + hi) >> 1; + + if (t < sp->ats[mid]) + hi = mid; + else + lo = mid + 1; + } + cutoff = lo; + } + + /* + * Scan backwards to find the latest interval using the given abbrev + * before the cutoff time. + */ + for (i = cutoff - 1; i >= 0; i--) + { + ttisp = &sp->ttis[sp->types[i]]; + if (ttisp->tt_abbrind == abbrind) + { + *gmtoff = ttisp->tt_gmtoff; + *isdst = ttisp->tt_isdst; + return TRUE; + } + } + + /* + * Not there, so scan forwards to find the first one after. + */ + for (i = cutoff; i < sp->timecnt; i++) + { + ttisp = &sp->ttis[sp->types[i]]; + if (ttisp->tt_abbrind == abbrind) + { + *gmtoff = ttisp->tt_gmtoff; + *isdst = ttisp->tt_isdst; + return TRUE; + } + } + + return FALSE; /* hm, not actually used in any interval? */ +} + +/* * If the given timezone uses only one GMT offset, store that offset * into *gmtoff and return TRUE, else return FALSE. */ diff --git a/src/timezone/tznames/America.txt b/src/timezone/tznames/America.txt index 54b51fe0054..9e6273207c1 100644 --- a/src/timezone/tznames/America.txt +++ b/src/timezone/tznames/America.txt @@ -47,7 +47,7 @@ AMT -14400 # Amazon Time # (America/Cuiaba) # (America/Manaus) # (America/Porto_Velho) -ART -10800 # Argentina Time +ART America/Argentina/Buenos_Aires # Argentina Time # (America/Argentina/Buenos_Aires) # (America/Argentina/Cordoba) # (America/Argentina/Tucuman) @@ -58,7 +58,7 @@ ART -10800 # Argentina Time # (America/Argentina/Mendoza) # (America/Argentina/Rio_Gallegos) # (America/Argentina/Ushuaia) -ARST -7200 D # Argentina Summer Time +ARST America/Argentina/Buenos_Aires # Argentina Summer Time # CONFLICT! AST is not unique # Other timezones: # - AST: Arabic Standard Time (Asia) @@ -228,7 +228,7 @@ GMT 0 # Greenwich Mean Time # (Etc/GMT) # (Europe/Dublin) # (Europe/London) -GYT -14400 # Guyana Time +GYT America/Guyana # Guyana Time # (America/Guyana) HADT -32400 D # Hawaii-Aleutian Daylight Time # (America/Adak) @@ -285,15 +285,15 @@ PST -28800 # Pacific Standard Time # (Pacific/Pitcairn) PYST -10800 D # Paraguay Summer Time # (America/Asuncion) -PYT -14400 # Paraguay Time +PYT America/Asuncion # Paraguay Time # (America/Asuncion) -SRT -10800 # Suriname Time +SRT America/Paramaribo # Suriname Time # (America/Paramaribo) UYST -7200 D # Uruguay Summer Time # (America/Montevideo) UYT -10800 # Uruguay Time # (America/Montevideo) -VET -16200 # Venezuela Time (caution: this used to mean -14400) +VET America/Caracas # Venezuela Time # (America/Caracas) WGST -7200 D # Western Greenland Summer Time # (America/Godthab) diff --git a/src/timezone/tznames/Antarctica.txt b/src/timezone/tznames/Antarctica.txt index 5a032506526..2359020ef87 100644 --- a/src/timezone/tznames/Antarctica.txt +++ b/src/timezone/tznames/Antarctica.txt @@ -16,11 +16,11 @@ CLST -10800 D # Chile Summer Time CLT -14400 # Chile Time # (America/Santiago) # (Antarctica/Palmer) -DAVT 25200 # Davis Time (Antarctica) +DAVT Antarctica/Davis # Davis Time (Antarctica) # (Antarctica/Davis) DDUT 36000 # Dumont-d`Urville Time (Antarctica) # (Antarctica/DumontDUrville) -MAWT 18000 # Mawson Time (Antarctica) (caution: this used to mean 21600) +MAWT Antarctica/Mawson # Mawson Time (Antarctica) # (Antarctica/Mawson) MIST 39600 # Macquarie Island Time # (Antarctica/Macquarie) diff --git a/src/timezone/tznames/Asia.txt b/src/timezone/tznames/Asia.txt index 8c3cb354713..bb286464297 100644 --- a/src/timezone/tznames/Asia.txt +++ b/src/timezone/tznames/Asia.txt @@ -15,17 +15,19 @@ ALMT 21600 # Alma-Ata Time # CONFLICT! AMST is not unique # Other timezones: # - AMST: Amazon Summer Time (America) -AMST 18000 D # Armenia Summer Time +AMST Asia/Yerevan # Armenia Summer Time # (Asia/Yerevan) # CONFLICT! AMT is not unique # Other timezones: # - AMT: Amazon Time (America) -AMT 14400 # Armenia Time +AMT Asia/Yerevan # Armenia Time # (Asia/Yerevan) -ANAST 46800 D # Anadyr Summer Time (obsolete) -ANAT 43200 # Anadyr Time +ANAST Asia/Anadyr # Anadyr Summer Time (obsolete) +ANAT Asia/Anadyr # Anadyr Time # (Asia/Anadyr) -AQTT 18000 # Aqtau Time (obsolete) +AQTST Asia/Aqtau # Aqtau Summer Time (obsolete) +AQTT Asia/Aqtau # Aqtau Time + # (Asia/Aqtau) # CONFLICT! AST is not unique # Other timezones: # - AST: Atlantic Standard Time (America) @@ -41,9 +43,9 @@ AST 10800 # Arabia Standard Time # (Asia/Kuwait) # (Asia/Qatar) # (Asia/Riyadh) -AZST 18000 D # Azerbaijan Summer Time +AZST Asia/Baku # Azerbaijan Summer Time # (Asia/Baku) -AZT 14400 # Azerbaijan Time +AZT Asia/Baku # Azerbaijan Time # (Asia/Baku) BDT 21600 # Bangladesh Time # (Asia/Dhaka) @@ -54,7 +56,7 @@ BTT 21600 # Bhutan Time # (Asia/Thimphu) CCT 28800 # China Coastal Time (not in zic) CHOST 36000 D # Choibalsan Summer Time (obsolete) -CHOT 28800 # Choibalsan Time (caution: this used to mean 32400) +CHOT Asia/Choibalsan # Choibalsan Time # (Asia/Choibalsan) CIT 28800 # Central Indonesia Time (obsolete, WITA is now preferred) EEST 10800 D # East-Egypt Summer Time @@ -105,9 +107,8 @@ EET 7200 # East-Egypt Time # (Europe/Vilnius) # (Europe/Zaporozhye) EIT 32400 # East Indonesia Time (obsolete, WIT is now preferred) -GEST 14400 D # Georgia Summer Time (obsolete) - # (Asia/Tbilisi) -GET 14400 # Georgia Time (caution: this used to mean 10800) +GEST Asia/Tbilisi # Georgia Summer Time (obsolete) +GET Asia/Tbilisi # Georgia Time # (Asia/Tbilisi) # CONFLICT! GST is not unique # Other timezones: @@ -117,7 +118,7 @@ GST 14400 # Gulf Standard Time # (Asia/Muscat) HKT 28800 # Hong Kong Time (not in zic) HOVST 28800 D # Hovd Summer Time (obsolete) -HOVT 25200 # Hovd Time +HOVT Asia/Hovd # Hovd Time # (Asia/Hovd) ICT 25200 # Indochina Time # (Asia/Bangkok) @@ -126,12 +127,12 @@ ICT 25200 # Indochina Time # (Asia/Vientiane) IDT 10800 D # Israel Daylight Time # (Asia/Jerusalem) -IRDT 16200 D # Iran Daylight Time +IRDT Asia/Tehran # Iran Daylight Time # (Asia/Tehran) -IRKST 32400 D # Irkutsk Summer Time (obsolete) -IRKT 28800 # Irkutsk Time (caution: this used to mean 32400) +IRKST Asia/Irkutsk # Irkutsk Summer Time (obsolete) +IRKT Asia/Irkutsk # Irkutsk Time # (Asia/Irkutsk) -IRST 12600 # Iran Standard Time +IRST Asia/Tehran # Iran Standard Time # (Asia/Tehran) IRT 12600 # Iran Time (not in zic) # CONFLICT! IST is not unique @@ -151,35 +152,34 @@ JST 32400 # Japan Standard Time # (Asia/Tokyo) KDT 36000 D # Korean Daylight Time (not in zic) KGST 21600 D # Kyrgyzstan Summer Time (obsolete) +KGT Asia/Bishkek # Kyrgyzstan Time # (Asia/Bishkek) -KGT 21600 # Kyrgyzstan Time (caution: this used to mean 18000) - # (Asia/Bishkek) -KRAST 28800 D # Krasnoyarsk Summer Time (obsolete) -KRAT 25200 # Krasnoyarsk Time (caution: this used to mean 28800) +KRAST Asia/Krasnoyarsk # Krasnoyarsk Summer Time (obsolete) +KRAT Asia/Krasnoyarsk # Krasnoyarsk Time # (Asia/Krasnoyarsk) KST 32400 # Korean Standard Time # (Asia/Pyongyang) -LKT 21600 # Lanka Time (obsolete) -MAGST 43200 D # Magadan Summer Time (obsolete) -MAGT 36000 # Magadan Time (caution: this used to mean 43200) +LKT Asia/Colombo # Lanka Time (obsolete) +MAGST Asia/Magadan # Magadan Summer Time (obsolete) +MAGT Asia/Magadan # Magadan Time # (Asia/Magadan) MMT 23400 # Myanmar Time # (Asia/Rangoon) MYT 28800 # Malaysia Time # (Asia/Kuala_Lumpur) # (Asia/Kuching) -NOVST 25200 D # Novosibirsk Summer Time (obsolete) -NOVT 21600 # Novosibirsk Time (caution: this used to mean 25200) +NOVST Asia/Novosibirsk # Novosibirsk Summer Time (obsolete) +NOVT Asia/Novosibirsk # Novosibirsk Time # (Asia/Novosibirsk) NPT 20700 # Nepal Time # (Asia/Katmandu) -OMSST 25200 D # Omsk Summer Time (obsolete) -OMST 21600 # Omsk Time (caution: this used to mean 25200) +OMSST Asia/Omsk # Omsk Summer Time (obsolete) +OMST Asia/Omsk # Omsk Time # (Asia/Omsk) -ORAT 18000 # Oral Time +ORAT Asia/Oral # Oral Time # (Asia/Oral) -PETST 46800 D # Petropavlovsk-Kamchatski Summer Time (obsolete) -PETT 43200 # Petropavlovsk-Kamchatski Time +PETST Asia/Kamchatka # Petropavlovsk-Kamchatski Summer Time (obsolete) +PETT Asia/Kamchatka # Petropavlovsk-Kamchatski Time # (Asia/Kamchatka) PHT 28800 # Philippine Time # (Asia/Manila) @@ -189,10 +189,10 @@ PKST 21600 D # Pakistan Summer Time # (Asia/Karachi) QYZT 21600 # Kizilorda Time # (Asia/Qyzylorda) -SAKST 39600 D # Sakhalin Summer Time (obsolete) -SAKT 36000 # Sakhalin Time (caution: this used to mean 39600) +SAKST Asia/Sakhalin # Sakhalin Summer Time (obsolete) +SAKT Asia/Sakhalin # Sakhalin Time # (Asia/Sakhalin) -SGT 28800 # Singapore Time +SGT Asia/Singapore # Singapore Time # (Asia/Singapore) SRET 39600 # Srednekolymsk Time # (Asia/Srednekolymsk) @@ -200,10 +200,10 @@ TJT 18000 # Tajikistan Time # (Asia/Dushanbe) TLT 32400 # East Timor Time # (Asia/Dili) -TMT 18000 # Turkmenistan Time +TMT Asia/Ashgabat # Turkmenistan Time # (Asia/Ashgabat) ULAST 32400 D # Ulan Bator Summer Time (obsolete) -ULAT 28800 # Ulan Bator Time +ULAT Asia/Ulaanbaatar # Ulan Bator Time # (Asia/Ulaanbaatar) UZST 21600 D # Uzbekistan Summer Time # (Asia/Samarkand) @@ -211,8 +211,8 @@ UZST 21600 D # Uzbekistan Summer Time UZT 18000 # Uzbekistan Time # (Asia/Samarkand) # (Asia/Tashkent) -VLAST 39600 D # Vladivostok Summer Time (obsolete) -VLAT 36000 # Vladivostok Time (caution: this used to mean 39600) +VLAST Asia/Vladivostok # Vladivostok Summer Time (obsolete) +VLAT Asia/Vladivostok # Vladivostok Time # (Asia/Vladivostok) WIB 25200 # Waktu Indonesia Barat # (Asia/Jakarta) @@ -223,9 +223,9 @@ WITA 28800 # Waktu Indonesia Tengah # (Asia/Makassar) XJT 21600 # Xinjiang Time # (Asia/Urumqi) -YAKST 36000 D # Yakutsk Summer Time (obsolete) -YAKT 32400 # Yakutsk Time (caution: this used to mean 36000) +YAKST Asia/Yakutsk # Yakutsk Summer Time (obsolete) +YAKT Asia/Yakutsk # Yakutsk Time # (Asia/Yakutsk) YEKST 21600 D # Yekaterinburg Summer Time (obsolete) -YEKT 18000 # Yekaterinburg Time (caution: this used to mean 21600) +YEKT Asia/Yekaterinburg # Yekaterinburg Time # (Asia/Yekaterinburg) diff --git a/src/timezone/tznames/Atlantic.txt b/src/timezone/tznames/Atlantic.txt index c65734b0aee..1d34d1ed4be 100644 --- a/src/timezone/tznames/Atlantic.txt +++ b/src/timezone/tznames/Atlantic.txt @@ -48,11 +48,11 @@ AZOST 0 D # Azores Summer Time # (Atlantic/Azores) AZOT -3600 # Azores Time # (Atlantic/Azores) -CVT -3600 # Cape Verde Time +CVT Atlantic/Cape_Verde # Cape Verde Time # (Atlantic/Cape_Verde) -FKST -10800 # Falkland Islands Summer Time (now used all year round) +FKST Atlantic/Stanley # Falkland Islands Summer/Standard Time # (Atlantic/Stanley) -FKT -14400 # Falkland Islands Time (obsolete) +FKT Atlantic/Stanley # Falkland Islands Time (obsolete) GMT 0 # Greenwich Mean Time # (Africa/Abidjan) # (Africa/Bamako) diff --git a/src/timezone/tznames/Australia.txt b/src/timezone/tznames/Australia.txt index 837309326d3..92c296840f9 100644 --- a/src/timezone/tznames/Australia.txt +++ b/src/timezone/tznames/Australia.txt @@ -52,7 +52,7 @@ EAST 36000 # East Australian Standard Time (not in zic) # Other timezones: # - EST: Eastern Standard Time (America) EST 36000 # Eastern Standard Time (not in zic) -LHDT 39600 D # Lord Howe Daylight Time +LHDT Australia/Lord_Howe # Lord Howe Daylight Time # (Australia/Lord_Howe) LHST 37800 # Lord Howe Standard Time # (Australia/Lord_Howe) diff --git a/src/timezone/tznames/Default b/src/timezone/tznames/Default index 9e5209e779b..a8b8eac5182 100644 --- a/src/timezone/tznames/Default +++ b/src/timezone/tznames/Default @@ -54,7 +54,7 @@ AKST -32400 # Alaska Standard Time # (America/Juneau) # (America/Nome) # (America/Yakutat) -ART -10800 # Argentina Time +ART America/Argentina/Buenos_Aires # Argentina Time # (America/Argentina/Buenos_Aires) # (America/Argentina/Cordoba) # (America/Argentina/Tucuman) @@ -65,7 +65,7 @@ ART -10800 # Argentina Time # (America/Argentina/Mendoza) # (America/Argentina/Rio_Gallegos) # (America/Argentina/Ushuaia) -ARST -7200 D # Argentina Summer Time +ARST America/Argentina/Buenos_Aires # Argentina Summer Time BOT -14400 # Bolivia Time # (America/La_Paz) BRA -10800 # Brazil Time (not in zic) @@ -170,7 +170,7 @@ FNST -3600 D # Fernando de Noronha Summer Time (not in zic) # (America/Noronha) GFT -10800 # French Guiana Time # (America/Cayenne) -GYT -14400 # Guyana Time +GYT America/Guyana # Guyana Time # (America/Guyana) MDT -21600 D # Mexico Mountain Daylight Time # Mountain Daylight Time @@ -219,13 +219,13 @@ PST -28800 # Pacific Standard Time # (Pacific/Pitcairn) PYST -10800 D # Paraguay Summer Time # (America/Asuncion) -PYT -14400 # Paraguay Time +PYT America/Asuncion # Paraguay Time # (America/Asuncion) UYST -7200 D # Uruguay Summer Time # (America/Montevideo) UYT -10800 # Uruguay Time # (America/Montevideo) -VET -16200 # Venezuela Time (caution: this used to mean -14400) +VET America/Caracas # Venezuela Time # (America/Caracas) WGST -7200 D # Western Greenland Summer Time # (America/Godthab) @@ -234,13 +234,13 @@ WGT -10800 # West Greenland Time #################### ANTARCTICA #################### -DAVT 25200 # Davis Time (Antarctica) +DAVT Antarctica/Davis # Davis Time (Antarctica) # (Antarctica/Davis) DDUT 36000 # Dumont-d'Urville Time (Antarctica) # (Antarctica/DumontDUrville) # (Antarctica/Palmer) # (America/Santiago) -MAWT 18000 # Mawson Time (Antarctica) (caution: this used to mean 21600) +MAWT Antarctica/Mawson # Mawson Time (Antarctica) # (Antarctica/Mawson) #################### ASIA #################### @@ -253,19 +253,19 @@ ALMST 25200 D # Alma-Ata Summer Time (obsolete) # CONFLICT! AMST is not unique # Other timezones: # - AMST: Amazon Summer Time (America) -AMST 18000 D # Armenia Summer Time +AMST Asia/Yerevan # Armenia Summer Time # (Asia/Yerevan) # CONFLICT! AMT is not unique # Other timezones: # - AMT: Amazon Time (America) -AMT 14400 # Armenia Time +AMT Asia/Yerevan # Armenia Time # (Asia/Yerevan) -ANAST 46800 D # Anadyr Summer Time (obsolete) -ANAT 43200 # Anadyr Time +ANAST Asia/Anadyr # Anadyr Summer Time (obsolete) +ANAT Asia/Anadyr # Anadyr Time # (Asia/Anadyr) -AZST 18000 D # Azerbaijan Summer Time +AZST Asia/Baku # Azerbaijan Summer Time # (Asia/Baku) -AZT 14400 # Azerbaijan Time +AZT Asia/Baku # Azerbaijan Time # (Asia/Baku) BDT 21600 # Bangladesh Time # (Asia/Dhaka) @@ -275,9 +275,8 @@ BORT 28800 # Borneo Time (Indonesia) (not in zic) BTT 21600 # Bhutan Time # (Asia/Thimphu) CCT 28800 # China Coastal Time (not in zic) -GEST 14400 D # Georgia Summer Time (obsolete) - # (Asia/Tbilisi) -GET 14400 # Georgia Time (caution: this used to mean 10800) +GEST Asia/Tbilisi # Georgia Summer Time (obsolete) +GET Asia/Tbilisi # Georgia Time # (Asia/Tbilisi) HKT 28800 # Hong Kong Time (not in zic) ICT 25200 # Indochina Time @@ -287,8 +286,8 @@ ICT 25200 # Indochina Time # (Asia/Vientiane) IDT 10800 D # Israel Daylight Time # (Asia/Jerusalem) -IRKST 32400 D # Irkutsk Summer Time (obsolete) -IRKT 28800 # Irkutsk Time (caution: this used to mean 32400) +IRKST Asia/Irkutsk # Irkutsk Summer Time (obsolete) +IRKT Asia/Irkutsk # Irkutsk Time # (Asia/Irkutsk) IRT 12600 # Iran Time (not in zic) # CONFLICT! IST is not unique @@ -302,33 +301,32 @@ JST 32400 # Japan Standard Time # (Asia/Tokyo) KDT 36000 D # Korean Daylight Time (not in zic) KGST 21600 D # Kyrgyzstan Summer Time (obsolete) +KGT Asia/Bishkek # Kyrgyzstan Time # (Asia/Bishkek) -KGT 21600 # Kyrgyzstan Time (caution: this used to mean 18000) - # (Asia/Bishkek) -KRAST 28800 D # Krasnoyarsk Summer Time (obsolete) -KRAT 25200 # Krasnoyarsk Time (caution: this used to mean 28800) +KRAST Asia/Krasnoyarsk # Krasnoyarsk Summer Time (obsolete) +KRAT Asia/Krasnoyarsk # Krasnoyarsk Time # (Asia/Krasnoyarsk) KST 32400 # Korean Standard Time # (Asia/Pyongyang) -LKT 21600 # Lanka Time (obsolete) -MAGST 43200 D # Magadan Summer Time (obsolete) -MAGT 36000 # Magadan Time (caution: this used to mean 43200) +LKT Asia/Colombo # Lanka Time (obsolete) +MAGST Asia/Magadan # Magadan Summer Time (obsolete) +MAGT Asia/Magadan # Magadan Time # (Asia/Magadan) MMT 23400 # Myanmar Time # (Asia/Rangoon) MYT 28800 # Malaysia Time # (Asia/Kuala_Lumpur) # (Asia/Kuching) -NOVST 25200 D # Novosibirsk Summer Time (obsolete) -NOVT 21600 # Novosibirsk Time (caution: this used to mean 25200) +NOVST Asia/Novosibirsk # Novosibirsk Summer Time (obsolete) +NOVT Asia/Novosibirsk # Novosibirsk Time # (Asia/Novosibirsk) NPT 20700 # Nepal Time # (Asia/Katmandu) -OMSST 25200 D # Omsk Summer Time (obsolete) -OMST 21600 # Omsk Time (caution: this used to mean 25200) +OMSST Asia/Omsk # Omsk Summer Time (obsolete) +OMST Asia/Omsk # Omsk Time # (Asia/Omsk) -PETST 46800 D # Petropavlovsk-Kamchatski Summer Time (obsolete) -PETT 43200 # Petropavlovsk-Kamchatski Time +PETST Asia/Kamchatka # Petropavlovsk-Kamchatski Summer Time (obsolete) +PETT Asia/Kamchatka # Petropavlovsk-Kamchatski Time # (Asia/Kamchatka) PHT 28800 # Philippine Time # (Asia/Manila) @@ -336,14 +334,14 @@ PKT 18000 # Pakistan Time # (Asia/Karachi) PKST 21600 D # Pakistan Summer Time # (Asia/Karachi) -SGT 28800 # Singapore Time +SGT Asia/Singapore # Singapore Time # (Asia/Singapore) TJT 18000 # Tajikistan Time # (Asia/Dushanbe) -TMT 18000 # Turkmenistan Time +TMT Asia/Ashgabat # Turkmenistan Time # (Asia/Ashgabat) ULAST 32400 D # Ulan Bator Summer Time (obsolete) -ULAT 28800 # Ulan Bator Time +ULAT Asia/Ulaanbaatar # Ulan Bator Time # (Asia/Ulaanbaatar) UZST 21600 D # Uzbekistan Summer Time # (Asia/Samarkand) @@ -351,16 +349,16 @@ UZST 21600 D # Uzbekistan Summer Time UZT 18000 # Uzbekistan Time # (Asia/Samarkand) # (Asia/Tashkent) -VLAST 39600 D # Vladivostok Summer Time (obsolete) -VLAT 36000 # Vladivostok Time (caution: this used to mean 39600) +VLAST Asia/Vladivostok # Vladivostok Summer Time (obsolete) +VLAT Asia/Vladivostok # Vladivostok Time # (Asia/Vladivostok) XJT 21600 # Xinjiang Time # (Asia/Urumqi) -YAKST 36000 D # Yakutsk Summer Time (obsolete) -YAKT 32400 # Yakutsk Time (caution: this used to mean 36000) +YAKST Asia/Yakutsk # Yakutsk Summer Time (obsolete) +YAKT Asia/Yakutsk # Yakutsk Time # (Asia/Yakutsk) YEKST 21600 D # Yekaterinburg Summer Time (obsolete) -YEKT 18000 # Yekaterinburg Time (caution: this used to mean 21600) +YEKT Asia/Yekaterinburg # Yekaterinburg Time # (Asia/Yekaterinburg) #################### ATLANTIC #################### @@ -406,9 +404,9 @@ AZOST 0 D # Azores Summer Time # (Atlantic/Azores) AZOT -3600 # Azores Time # (Atlantic/Azores) -FKST -10800 # Falkland Islands Summer Time (now used all year round) +FKST Atlantic/Stanley # Falkland Islands Summer/Standard Time # (Atlantic/Stanley) -FKT -14400 # Falkland Islands Time (obsolete) +FKT Atlantic/Stanley # Falkland Islands Time (obsolete) #################### AUSTRALIA #################### @@ -443,7 +441,7 @@ AWST 28800 # Australian Western Standard Time # (Australia/Perth) CADT 37800 D # Central Australia Daylight-Saving Time (not in zic) CAST 34200 # Central Australia Standard Time (not in zic) -LHDT 39600 D # Lord Howe Daylight Time +LHDT Australia/Lord_Howe # Lord Howe Daylight Time # (Australia/Lord_Howe) LHST 37800 # Lord Howe Standard Time # (Australia/Lord_Howe) @@ -639,9 +637,10 @@ MET 3600 # Middle Europe Time (not in zic) METDST 7200 D # Middle Europe Summer Time (not in zic) MEZ 3600 # Mitteleuropaeische Zeit (German) (not in zic) MSD 14400 D # Moscow Daylight Time (obsolete) -MSK 10800 # Moscow Time (caution: this used to mean 14400) +MSK Europe/Moscow # Moscow Time # (Europe/Moscow) -VOLT 14400 # Volgograd Time (obsolete) + # (Europe/Volgograd) +VOLT Europe/Volgograd # Volgograd Time (obsolete) WET 0 # Western Europe Time # (Africa/Casablanca) # (Africa/El_Aaiun) @@ -659,7 +658,7 @@ WETDST 3600 D # Western Europe Summer Time CXT 25200 # Christmas Island Time (Indian Ocean) # (Indian/Christmas) -IOT 21600 # British Indian Ocean Territory (Chagos) (there was a timezone change recently in 1996) +IOT Indian/Chagos # British Indian Ocean Territory (Chagos) # (Indian/Chagos) MUT 14400 # Mauritius Island Time # (Indian/Mauritius) @@ -682,11 +681,11 @@ CHAST 45900 # Chatham Standard Time (New Zealand) # (Pacific/Chatham) CHUT 36000 # Chuuk Time # (Pacific/Chuuk) -CKT -36000 # Cook Islands Time (caution: this used to mean 43200) +CKT Pacific/Rarotonga # Cook Islands Time # (Pacific/Rarotonga) -EASST -18000 D # Easter Island Summer Time (Chile) +EASST Pacific/Easter # Easter Island Summer Time (Chile) # (Pacific/Easter) -EAST -21600 # Easter Island Time (Chile) +EAST Pacific/Easter # Easter Island Time (Chile) # (Pacific/Easter) FJST 46800 D # Fiji Summer Time # (Pacific/Fiji) @@ -701,9 +700,9 @@ GILT 43200 # Gilbert Islands Time HST -36000 # Hawaiian Standard Time # (Pacific/Honolulu) # (Pacific/Johnston) -KOST 39600 # Kosrae Time +KOST Pacific/Kosrae # Kosrae Time # (Pacific/Kosrae) -LINT 50400 # Line Islands Time (Kiribati) +LINT Pacific/Kiritimati # Line Islands Time (Kiribati) # (Pacific/Kiritimati) MART -34200 # Marquesas Time # (Pacific/Marquesas) @@ -715,7 +714,7 @@ MPT 36000 # North Mariana Islands Time (not in zic) # Other timezones: # - NFT: Norfolk Time (Pacific) NFT -12600 # Newfoundland Time (not in zic) -NUT -39600 # Niue Time +NUT Pacific/Niue # Niue Time # (Pacific/Niue) NZDT 46800 D # New Zealand Daylight Time # (Antarctica/McMurdo) @@ -725,7 +724,7 @@ NZST 43200 # New Zealand Standard Time # (Pacific/Auckland) PGT 36000 # Papua New Guinea Time # (Pacific/Port_Moresby) -PHOT 46800 # Phoenix Islands Time (Kiribati) +PHOT Pacific/Enderbury # Phoenix Islands Time (Kiribati) # (Pacific/Enderbury) PONT 39600 # Ponape Time (Micronesia) # (Pacific/Ponape) @@ -733,7 +732,7 @@ PWT 32400 # Palau Time # (Pacific/Palau) TAHT -36000 # Tahiti Time (zic says "TAHT", other sources "THAT") # (Pacific/Tahiti) -TKT 46800 # Tokelau Time (caution: this used to mean -36000) +TKT Pacific/Fakaofo # Tokelau Time # (Pacific/Fakaofo) TOT 46800 # Tonga Time # (Pacific/Tongatapu) diff --git a/src/timezone/tznames/Europe.txt b/src/timezone/tznames/Europe.txt index c6b37bdd5ed..421f8f18ad4 100644 --- a/src/timezone/tznames/Europe.txt +++ b/src/timezone/tznames/Europe.txt @@ -186,12 +186,13 @@ MET 3600 # Middle Europe Time (not in zic) METDST 7200 D # Middle Europe Summer Time (not in zic) MEZ 3600 # Mitteleuropäische Zeit (German) (not in zic) MSD 14400 D # Moscow Daylight Time (obsolete) -MSK 10800 # Moscow Time (caution: this used to mean 14400) +MSK Europe/Moscow # Moscow Time # (Europe/Moscow) -SAMST 18000 D # Samara Summer Time (obsolete) -SAMT 14400 # Samara Time + # (Europe/Volgograd) +SAMST Europe/Samara # Samara Summer Time (obsolete) +SAMT Europe/Samara # Samara Time # (Europe/Samara) -VOLT 14400 # Volgograd Time (obsolete) +VOLT Europe/Volgograd # Volgograd Time (obsolete) WEST 3600 D # Western Europe Summer Time # (Africa/Casablanca) # (Atlantic/Canary) diff --git a/src/timezone/tznames/Indian.txt b/src/timezone/tznames/Indian.txt index c77c9919a1c..634660075ff 100644 --- a/src/timezone/tznames/Indian.txt +++ b/src/timezone/tznames/Indian.txt @@ -23,7 +23,7 @@ EAT 10800 # East Africa Time # (Indian/Antananarivo) # (Indian/Comoro) # (Indian/Mayotte) -IOT 21600 # British Indian Ocean Territory (Chagos) (there was a timezone change recently in 1996) +IOT Indian/Chagos # British Indian Ocean Territory (Chagos) # (Indian/Chagos) MUT 14400 # Mauritius Island Time # (Indian/Mauritius) diff --git a/src/timezone/tznames/Pacific.txt b/src/timezone/tznames/Pacific.txt index 2f988140014..1d205589bbc 100644 --- a/src/timezone/tznames/Pacific.txt +++ b/src/timezone/tznames/Pacific.txt @@ -16,14 +16,14 @@ ChST 36000 # Chamorro Standard Time (lower case "h" is as in zic) # (Pacific/Saipan) CHUT 36000 # Chuuk Time # (Pacific/Chuuk) -CKT -36000 # Cook Islands Time (caution: this used to mean 43200) +CKT Pacific/Rarotonga # Cook Islands Time # (Pacific/Rarotonga) -EASST -18000 D # Easter Island Summer Time (Chile) +EASST Pacific/Easter # Easter Island Summer Time (Chile) # (Pacific/Easter) # CONFLICT! EAST is not unique # Other timezones: # - EAST: East Australian Standard Time (Australia) -EAST -21600 # Easter Island Time (Chile) +EAST Pacific/Easter # Easter Island Time (Chile) # (Pacific/Easter) FJST 46800 D # Fiji Summer Time (caution: this used to mean -46800) # (Pacific/Fiji) @@ -38,9 +38,9 @@ GILT 43200 # Gilbert Islands Time HST -36000 # Hawaiian Standard Time # (Pacific/Honolulu) # (Pacific/Johnston) -KOST 39600 # Kosrae Time +KOST Pacific/Kosrae # Kosrae Time # (Pacific/Kosrae) -LINT 50400 # Line Islands Time (Kiribati) +LINT Pacific/Kiritimati # Line Islands Time (Kiribati) # (Pacific/Kiritimati) MART -34200 # Marquesas Time # (Pacific/Marquesas) @@ -55,9 +55,9 @@ NCT 39600 # New Caledonia Time # - NFT: Newfoundland Time (America) NFT 41400 # Norfolk Time # (Pacific/Norfolk) -NRT 43200 # Nauru Time +NRT Pacific/Nauru # Nauru Time # (Pacific/Nauru) -NUT -39600 # Niue Time +NUT Pacific/Niue # Niue Time # (Pacific/Niue) NZDT 46800 D # New Zealand Daylight Time # (Antarctica/McMurdo) @@ -67,7 +67,7 @@ NZST 43200 # New Zealand Standard Time # (Pacific/Auckland) PGT 36000 # Papua New Guinea Time # (Pacific/Port_Moresby) -PHOT 46800 # Phoenix Islands Time (Kiribati) +PHOT Pacific/Enderbury # Phoenix Islands Time (Kiribati) # (Pacific/Enderbury) PONT 39600 # Ponape Time (Micronesia) # (Pacific/Ponape) @@ -87,7 +87,7 @@ SST -39600 # South Sumatran Time # (Pacific/Pago_Pago) TAHT -36000 # Tahiti Time (zic says "TAHT", other sources "THAT") # (Pacific/Tahiti) -TKT 46800 # Tokelau Time (caution: this used to mean -36000) +TKT Pacific/Fakaofo # Tokelau Time # (Pacific/Fakaofo) TOT 46800 # Tonga Time # (Pacific/Tongatapu) diff --git a/src/timezone/tznames/README b/src/timezone/tznames/README index 6cb0ae88c93..c80caa37869 100644 --- a/src/timezone/tznames/README +++ b/src/timezone/tznames/README @@ -6,26 +6,29 @@ tznames This directory contains files with timezone sets for PostgreSQL. The problem is that time zone abbreviations are not unique throughout the world and you might find out that a time zone abbreviation in the `Default' set collides -with the one you wanted to use. All other files except for `Default' are -intended to override values from the `Default' set. So you might already have -a file here that serves your needs. If not, you can create your own. +with the one you wanted to use. This can be fixed by selecting a timezone +set that defines the abbreviation the way you want it. There might already +be a file here that serves your needs. If not, you can create your own. In order to use one of these files, you need to set timezone_abbreviations = 'xyz' in any of the usual ways for setting a parameter, where xyz is the filename -that contains the desired time zone names. +that contains the desired time zone abbreviations. -If you do not find an appropriate set of time zone names for your geographic +If you do not find an appropriate set of abbreviations for your geographic location supplied here, please report this to <pgsql-hackers@postgresql.org>. -Your set of time zone names can then be included in future releases. +Your set of time zone abbreviations can then be included in future releases. For the time being you can always add your own set. +Typically a custom abbreviation set is made by including the `Default' set +and then adding or overriding abbreviations as necessary. For examples, +see the `Australia' and `India' files. + The files named Africa.txt, etc, are not intended to be used directly as time zone abbreviation files. They contain reference definitions of time zone -names that can be copied into a custom abbreviation file as needed. - -Note that these files (*.txt) are already a subset of the zic timezone -database files: we tried to list only those time zones that (according to -the zic timezone database) appear to be still in use. +abbreviations that can be copied into a custom abbreviation file as needed. +Note that these files (*.txt) are already a subset of the IANA timezone +database files: we tried to list only those time zone abbreviations that +(according to the IANA timezone database) appear to be still in use. diff --git a/src/timezone/zic.c b/src/timezone/zic.c index 13baf73d3c1..1a7ec68d7c0 100644 --- a/src/timezone/zic.c +++ b/src/timezone/zic.c @@ -1771,7 +1771,7 @@ writezone(const char *name, const char *string) /* Print current timezone abbreviations if requested */ if (print_abbrevs && - (ats[i] >= print_cutoff || i == thistimelim - 1)) + (i == thistimelim - 1 || ats[i + 1] > print_cutoff)) { unsigned char tm = typemap[types[i]]; char *thisabbrev = &thischars[indmap[abbrinds[tm]]]; |