Some abbrev values from pg_timezone_names are not found in pg_timezone_abbrevs

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Some abbrev values from pg_timezone_names are not found in pg_timezone_abbrevs
Date: 2021-05-19 06:22:44
Message-ID: F46A3BF2-B0FA-4F4B-9741-75D4F5240800@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This query:

select distinct abbrev as a
from pg_timezone_names
where abbrev like '%+%' or abbrev like '%-%'
order by 1;

gets lots of rows with names like these:

+00
+01
+12
-07
-08

This query shows that none of these is found in pg_timezone_abbrevs:

with v as (
select distinct abbrev as a
from pg_timezone_names
where abbrev like '%+%' or abbrev like '%-%')
select abbrev
from pg_timezone_abbrevs
where abbrev in (select a from v);

I suppose that these strangely named abbreviations are special and that they shouldn't appear in the official pg_timezone_abbrevs.

But try this:

select distinct abbrev
from pg_timezone_names
where
abbrev not like '%+%' and abbrev not like '%-%' and
abbrev not in (select abbrev from pg_timezone_abbrevs)
order by abbrev;

It gets this result:

CAT
ChST
HDT
SST
WEST
WIB
WIT
WITA

Is this a bug? If not, what's the rationale for omitting them?

From section “8.5.3. Time Zones” at

https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES

«
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.
»

So I s'pose that it's to be expected that this:

select ('2000-01-01 12:00:00 +00:00'::timestamptz) at time zone 'CAT';

causes this error:

time zone "CAT" not recognized

It's the same with the other abbrev values from pg_timezone_names that aren't in pg_timezone_abbrevs.

If this is not a bug, then why are these eight abbreviations special?

Browse pgsql-general by date

  From Date Subject
Next Message Bryn Llewellyn 2021-05-19 06:31:57 The contents of the pg_timezone_names view bring some surprises
Previous Message Jeremy Smith 2021-05-18 15:52:16 Re: Logical Replication: SELECT pg_catalog.set_config Statement