On 5/18/21 11:31 PM, Bryn Llewellyn wrote:
Some time zones have abbreviations that are identical to their names.
This query:
Here’s what seems to me to be a closely related dilemma. I’d thought
that an abbrev uniquely specified the utc_offset. But this test shows
that it doesn’t:
with
v1 as (
select distinct abbrev, utc_offset
from pg_timezone_names),
v2 as (
select abbrev, count(*)
from v1
group by abbrev
having count(*) > 1)
select name, abbrev, utc_offset, is_dst
from pg_timezone_names
where abbrev in (select abbrev from v2)
order by abbrev;
It gets 46 rows. Here’s an interesting subset:
America/Monterrey | CDT | -05:00:00 | t
America/Havana | CDT | -04:00:00 | t
ROC | CST | 08:00:00 | f
America/Costa_Rica | CST | -06:00:00 | f
Eire | IST | 01:00:00 | f
Asia/Kolkata | IST | 05:30:00 | f
So here, the same text, even when used as abbrev, can denote different
utc_offset values. (But note that there seems to be no way, in the 'at
time zone' clause, that I can say that I want a text value to be taken
as a name and not as an abbreviation, or vice versa.)
This seems to be at odds with what section “8.5.3. Time Zones” at
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
<https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES>
says:
«
A time zone abbreviation, for example PST. Such a specification merely
defines a particular offset from UTC, in contrast to full time zone
names which can imply a set of daylight savings transition rules as
well. The recognized abbreviations are listed in
the pg_timezone_abbrevs view (see Section 51.91). You cannot set the
configuration parameters TimeZone or log_timezone to a time zone
abbreviation, but you can use abbreviations in date/time input values
and with the AT TIME ZONE operator.
»
This claims (as I read it) that a time zone abbreviation uniquely
determines an offset from UTC.
It says no such thing and would be lying if it did. Take a look at this:
https://en.wikipedia.org/wiki/List_of_time_zone_abbreviations
and see the abbreviations that share offsets.
What it is saying that, for example, the timezone America/Los_Angeles
has two timezone abbreviations PDT(what I'm currently in) and PST. If
you use an abbreviation you don't get the DST transition rules that a
full timezone name has.
It seems that the result of this is therefore undefined because CDT
denotes two different utc_offset values.:
select ('2000-01-01 12:00:00 +00:00'::timestamptz) at time zone 'CDT';
The same goes for this:
select ('2000-01-01 12:00:00 +00:00'::timestamptz) at time zone 'EET';
In summary, each of these texts, for two kinds of reason, can denote two
different utc_offset values.:
CET
EET
CDT
CST
IST
Am I missing an essential clue to resolving what seems to me to be a
paradox? Or am I seeing two kinds of bug?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
