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

From: "Collin Peters" <cadiolis(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Timezone issue - Is it me or is this a massive bug?
Date: 2008-06-20 20:19:13
Message-ID: df01c91b0806201319q69f3522cp33a738fb1da3da84@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2008-06-20 22:38:23 Re: Forcibly vacating locks
Previous Message Michael Fuhr 2008-06-20 14:37:36 Re: test aggregate functions without a dummy table