Etc/% timezones

Started by Naz Gassiepover 18 years ago4 messagesgeneral
Jump to latest
#1Naz Gassiep
naz@mira.net

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)

#2Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Naz Gassiep (#1)
Re: Etc/% timezones

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)

#3Naz Gassiep
naz@mira.net
In reply to: Alvaro Herrera (#2)
Re: Etc/% timezones

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.

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Naz Gassiep (#3)
Re: Etc/% timezones

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