Thread: Timezone issue - Is it me or is this a massive bug?
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
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? 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 >
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