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