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?
On Sat, Jun 21, 2008 at 9:54 AM, Adrian Klaver <aklaver@comcast.net> wrote:
> 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
>
