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