Обсуждение: Etc/% timezones

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)

Naz Gassiep wrote:

> I'm pretty certain that this is a bug. Can anyone confirm?

It is a bug -- in the SQL standard definition.  The meaning of the sign
is inverted w.r.t. the relevant POSIX (?) standard, AFAIU.
Unsurprisingly, we're following the SQL standard here.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)

>> I'm pretty certain that this is a bug. Can anyone confirm?
>>
>
> It is a bug -- in the SQL standard definition.  The meaning of the sign
> is inverted w.r.t. the relevant POSIX (?) standard, AFAIU.
> Unsurprisingly, we're following the SQL standard here.

Wow. Seriously, wow.
Good thing I'm filtering them out then, else confusion would ensue. Has
anyone brought the stupidity of this to the attention of the SQL team? Is
there any rationale behind this? I've been working with timezone stuff for
the last few weeks and I'm really getting into it, so I'm just interested
to know how it was concluded that this is a good idea.

Naz Gassiep <naz@mira.net> writes:
> Good thing I'm filtering them out then, else confusion would ensue. Has
> anyone brought the stupidity of this to the attention of the SQL team? Is

Do you really think they're unaware of that?

Actually, the bug is in the POSIX definition, which is out of step with
the rest of the world.  The SQL spec is following ISO 8601.

            regards, tom lane