I just noticed that in the pg_timezone_names system table, the name and
abbrev of the "Etc/%" timezones appear to be inverted with their
utc_offset value.
I never noticed before, as I filter these zones out (among others) and
do not use them in my app. I was just interested as to why the suggested
abbrev field and utc_offset field are the opposite sign of each other.
Is this a bug or is there a reason for this? I have included the output
from the view with the relevant time zones below.
E.g., I am in Melbourne, Australia, which I have always known as GMT+10
(ignoring DST). According to the below list, however, the timezone that
has the matching utc_offset to mine is called "Etc/GMT-10", which to me
seems wrong. Were I presented with a list of the names, I'd select
"Etc/GMT+10" as my time zone, incorrectly telling the app that my
timezone's utc_offset is -10:00:00.
I'm pretty certain that this is a bug. Can anyone confirm?
Regards,
- Naz.
select * from pg_timezone_names where name ilike 'etc/%' order by
utc_offset, abbrev, name;
name | abbrev | utc_offset | is_dst
---------------+--------+------------+--------
Etc/GMT+12 | GMT+12 | -12:00:00 | f
Etc/GMT+11 | GMT+11 | -11:00:00 | f
Etc/GMT+10 | GMT+10 | -10:00:00 | f
Etc/GMT+9 | GMT+9 | -09:00:00 | f
Etc/GMT+8 | GMT+8 | -08:00:00 | f
Etc/GMT+7 | GMT+7 | -07:00:00 | f
Etc/GMT+6 | GMT+6 | -06:00:00 | f
Etc/GMT+5 | GMT+5 | -05:00:00 | f
Etc/GMT+4 | GMT+4 | -04:00:00 | f
Etc/GMT+3 | GMT+3 | -03:00:00 | f
Etc/GMT+2 | GMT+2 | -02:00:00 | f
Etc/GMT+1 | GMT+1 | -01:00:00 | f
Etc/GMT | GMT | 00:00:00 | f
Etc/GMT+0 | GMT | 00:00:00 | f
Etc/GMT-0 | GMT | 00:00:00 | f
Etc/GMT0 | GMT | 00:00:00 | f
Etc/Greenwich | GMT | 00:00:00 | f
Etc/UCT | UCT | 00:00:00 | f
Etc/UTC | UTC | 00:00:00 | f
Etc/Universal | UTC | 00:00:00 | f
Etc/Zulu | UTC | 00:00:00 | f
Etc/GMT-1 | GMT-1 | 01:00:00 | f
Etc/GMT-2 | GMT-2 | 02:00:00 | f
Etc/GMT-3 | GMT-3 | 03:00:00 | f
Etc/GMT-4 | GMT-4 | 04:00:00 | f
Etc/GMT-5 | GMT-5 | 05:00:00 | f
Etc/GMT-6 | GMT-6 | 06:00:00 | f
Etc/GMT-7 | GMT-7 | 07:00:00 | f
Etc/GMT-8 | GMT-8 | 08:00:00 | f
Etc/GMT-9 | GMT-9 | 09:00:00 | f
Etc/GMT-10 | GMT-10 | 10:00:00 | f
Etc/GMT-11 | GMT-11 | 11:00:00 | f
Etc/GMT-12 | GMT-12 | 12:00:00 | f
Etc/GMT-13 | GMT-13 | 13:00:00 | f
Etc/GMT-14 | GMT-14 | 14:00:00 | f
(35 rows)
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
