Timezone issue - Is it me or is this a massive bug?

On Friday 20 June 2008 1:19 pm, Collin Peters wrote:

I have a server of which the OS timezone is set to Pacific time
(currently -7). I run the following query on it

SELECT now(), now() AT TIME ZONE 'GMT+10:00', now() AT TIME ZONE
'GMT-10:00', now() AT TIME ZONE 'Australia/Melbourne'

I would expect this to return:
* column 1 - the current time in the pacific (-7) - "2008-06-20
13:09:39.245641-07"
* column 2 - the GMT +10 - "2008-06-21 06:09:39.245641"
* column 3 - the GMT -10 - "2008-06-20 10:09:39.245641"
* column 4 - the current time in Melbourne Australia - "2008-06-21
06:09:39.245641"

Instead it returns:
* column 1 - the current time in the pacific (-7) ("2008-06-20
13:09:39.245641-07" - CORRECT)
* column 2 - the current time MINUS 10 ("2008-06-20 10:09:39.245641" -
WRONG) * column 3 - the current time PLUS 10 ("2008-06-21 06:09:39.245641"
- WRONG) * column 4 - the current time in Melbourne Australia ("2008-06-21
06:09:39.245641" - CORRECT)

Am I missing something obvious? Seems when I specify GMT+10:00 it
returns GMT-10:00 and vice versa. Note that column 2 & 3 are
timestamp withOUT timezone while 1 & 4 are timestamp WITH timezone.
But I still see this as totally wrong.

Regards,
Collin Peters

See this message for the explanation:
http://archives.postgresql.org/pgsql-bugs/2008-04/msg00077.php
--
Adrian Klaver
aklaver@comcast.net

I have read the post and understand the issue. I am wondering why
this is not mentioned in the documentation. Or even worse why the
PostgreSQL documentation explicitly lists all the timezones correctly
in table B-4 http://www.postgresql.org/docs/8.1/static/datetime-keywords.html#DATETIME-TIMEZONE-INPUT-TABLE

In that table it has Melbourne, Australia as
LIGT +10:00 Melbourne, Australia

But according to the post you linked to that is not correct... I must
instead specifiy -10:00. Should the documentation not note this?

Collin Peters escribi�:

I have read the post and understand the issue. I am wondering why
this is not mentioned in the documentation. Or even worse why the
PostgreSQL documentation explicitly lists all the timezones correctly
in table B-4 http://www.postgresql.org/docs/8.1/static/datetime-keywords.html#DATETIME-TIMEZONE-INPUT-TABLE

In that table it has Melbourne, Australia as
LIGT +10:00 Melbourne, Australia

But according to the post you linked to that is not correct... I must
instead specifiy -10:00. Should the documentation not note this?

Absolutely. Care to submit a patch?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"Collin Peters" <cadiolis@gmail.com> writes:

I have read the post and understand the issue. I am wondering why
this is not mentioned in the documentation.

It is. Per
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#DATATYPE-TIMEZONES

"One should be wary that the POSIX-style time zone feature can lead to
silently accepting bogus input, since there is no check on the
reasonableness of the zone abbreviations. For example, SET TIMEZONE TO
FOOBAR0 will work, leaving the system effectively using a rather
peculiar abbreviation for UTC. Another issue to keep in mind is that in
POSIX time zone names, positive offsets are used for locations west of
Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention
that positive timezone offsets are east of Greenwich."

regards, tom lane