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
«
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?