I wrote:
> So the idea I'm toying with (again, haven't tried to code this) is to say
> that *if* we can match the abbreviation to something in the referenced
> zone then we'll use that, but otherwise we fall back to treating the
> abbreviation as a macro for the zone name.
This turned out to require only fairly localized code changes. So
I now propose that we leave the tznames files as-is and apply the
attached patch (plus documentation changes, which I've not done yet).
Any objections?
regards, tom lane
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 965c3b4..45ba7cd 100644
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
*************** static void AdjustFractDays(double frac,
*** 56,63 ****
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);
--- 56,64 ----
int scale);
static int DetermineTimeZoneOffsetInternal(struct pg_tm * tm, pg_tz *tzp,
pg_time_t *tp);
! static bool DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t,
! const char *abbr, pg_tz *tzp,
! int *offset, int *isdst);
static pg_tz *FetchDynamicTimeZone(TimeZoneAbbrevTable *tbl, const datetkn *tp);
*************** overflow:
*** 1689,1707 ****
* 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);
}
--- 1690,1729 ----
* 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.
+ * (However, that happens only if we can match the given abbreviation to some
+ * abbreviation that appears in the IANA timezone data. Otherwise, we fall
+ * back to doing DetermineTimeZoneOffset().)
*/
int
DetermineTimeZoneAbbrevOffset(struct pg_tm * tm, const char *abbr, pg_tz *tzp)
{
pg_time_t t;
+ int zone_offset;
+ int abbr_offset;
+ int abbr_isdst;
/*
* 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.)
*/
! zone_offset = DetermineTimeZoneOffsetInternal(tm, tzp, &t);
! /*
! * Try to match the abbreviation to something in the zone definition.
! */
! if (DetermineTimeZoneAbbrevOffsetInternal(t, abbr, tzp,
! &abbr_offset, &abbr_isdst))
! {
! /* Success, so use the abbrev-specific answers. */
! tm->tm_isdst = abbr_isdst;
! return abbr_offset;
! }
!
! /*
! * No match, so use the answers we already got from
! * DetermineTimeZoneOffsetInternal.
! */
! return zone_offset;
}
*************** DetermineTimeZoneAbbrevOffsetTS(Timestam
*** 1715,1733 ****
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;
--- 1737,1777 ----
pg_tz *tzp, int *isdst)
{
pg_time_t t = timestamptz_to_time_t(ts);
+ int zone_offset;
+ int abbr_offset;
+ int tz;
+ struct pg_tm tm;
+ fsec_t fsec;
! /*
! * If the abbrev matches anything in the zone data, this is pretty easy.
! */
! if (DetermineTimeZoneAbbrevOffsetInternal(t, abbr, tzp,
! &abbr_offset, isdst))
! return abbr_offset;
!
! /*
! * Else, break down the timestamp so we can use DetermineTimeZoneOffset.
! */
! if (timestamp2tm(ts, &tz, &tm, &fsec, NULL, tzp) != 0)
! ereport(ERROR,
! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
! errmsg("timestamp out of range")));
!
! zone_offset = DetermineTimeZoneOffset(&tm, tzp);
! *isdst = tm.tm_isdst;
! return zone_offset;
}
/* DetermineTimeZoneAbbrevOffsetInternal()
*
* Workhorse for above two functions: work from a pg_time_t probe instant.
! * On success, return GMT offset and DST status into *offset and *isdst.
*/
! static bool
! DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t, const char *abbr, pg_tz *tzp,
! int *offset, int *isdst)
{
char upabbr[TZ_STRLEN_MAX + 1];
unsigned char *p;
*************** DetermineTimeZoneAbbrevOffsetInternal(pg
*** 1739,1756 ****
*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;
}
--- 1783,1799 ----
*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))
! {
! /* Change sign to agree with DetermineTimeZoneOffset() */
! *offset = (int) -gmtoff;
! return true;
! }
! return false;
}
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 67f26db..2bfc13a 100644
*** a/src/test/regress/expected/timestamptz.out
--- b/src/test/regress/expected/timestamptz.out
*************** SELECT '2007-12-09 07:30:00 UTC'::timest
*** 2603,2605 ****
--- 2603,2625 ----
Sun Dec 09 03:00:00 2007
(1 row)
+ --
+ -- Test that the pg_timezone_names and pg_timezone_abbrevs views are
+ -- more-or-less working. We can't test their contents in any great detail
+ -- without the outputs changing anytime IANA updates the underlying data,
+ -- but it seems reasonable to expect at least one entry per major meridian.
+ -- (At the time of writing, the actual counts are around 38 because of
+ -- zones using fractional GMT offsets, so this is a pretty loose test.)
+ --
+ select count(distinct utc_offset) >= 24 as ok from pg_timezone_names;
+ ok
+ ----
+ t
+ (1 row)
+
+ select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
+ ok
+ ----
+ t
+ (1 row)
+
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index c023095..ce9d1c2 100644
*** a/src/test/regress/sql/timestamptz.sql
--- b/src/test/regress/sql/timestamptz.sql
*************** SELECT '2007-12-09 07:00:00 UTC'::timest
*** 468,470 ****
--- 468,481 ----
SELECT '2007-12-09 07:00:01 UTC'::timestamptz AT TIME ZONE 'VET';
SELECT '2007-12-09 07:29:59 UTC'::timestamptz AT TIME ZONE 'VET';
SELECT '2007-12-09 07:30:00 UTC'::timestamptz AT TIME ZONE 'VET';
+
+ --
+ -- Test that the pg_timezone_names and pg_timezone_abbrevs views are
+ -- more-or-less working. We can't test their contents in any great detail
+ -- without the outputs changing anytime IANA updates the underlying data,
+ -- but it seems reasonable to expect at least one entry per major meridian.
+ -- (At the time of writing, the actual counts are around 38 because of
+ -- zones using fractional GMT offsets, so this is a pretty loose test.)
+ --
+ select count(distinct utc_offset) >= 24 as ok from pg_timezone_names;
+ select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
