Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #4100: GMT offsets are wrong

BUG #4100: GMT offsets are wrong

From
"Peter Coxhead"
Date:
The following bug has been logged online:

Bug reference:      4100
Logged by:          Peter Coxhead
Email address:      pdcoxhead@gmail.com
PostgreSQL version: 7.4
Operating system:   Linux
Description:        GMT offsets are wrong
Details:

I find the results to selecting the time in a GMT offset are backwards, so

SET TIME ZONE "GMT+10:00";
SELECT NOW();

gives "2008-04-07 16:23:28.877908-10" which is a time in the western
hemisphere and

SET TIME ZONE "GMT-10:00";
SELECT NOW();

"2008-04-08 12:27:27.268616+10"

gives a time in the eastern hemisphere.  The time zone I was trying to get
was Australia/Brisbane time (GMT+10:00 i.e. 01:00 in London = 11:00
GMT+10:00).

I think I checked this on an 8.2 version of postgres with the same results
(and was mildly surprised to see it still there).

Let me know if I'm doing something daft, I don't think I am though.

Peter

PS I know there's an 'Australia/Brisbane' time zone I can use

Re: BUG #4100: GMT offsets are wrong

From
Magnus Hagander
Date:
Peter Coxhead wrote:
>
> The following bug has been logged online:
>
> Bug reference:      4100
> Logged by:          Peter Coxhead
> Email address:      pdcoxhead@gmail.com
> PostgreSQL version: 7.4
> Operating system:   Linux
> Description:        GMT offsets are wrong
> Details:
>
> I find the results to selecting the time in a GMT offset are
> backwards, so
>
> SET TIME ZONE "GMT+10:00";
> SELECT NOW();
>
> gives "2008-04-07 16:23:28.877908-10" which is a time in the western
> hemisphere and
>
> SET TIME ZONE "GMT-10:00";
> SELECT NOW();
>
> "2008-04-08 12:27:27.268616+10"
>
> gives a time in the eastern hemisphere.  The time zone I was trying
> to get was Australia/Brisbane time (GMT+10:00 i.e. 01:00 in London =
> 11:00 GMT+10:00).
>
> I think I checked this on an 8.2 version of postgres with the same
> results (and was mildly surprised to see it still there).
>
> Let me know if I'm doing something daft, I don't think I am though.

I think you're being bitten by the stupid rules of POSIX timezones.
They declare + as being west of GMT, and - as east. Which is not what
you'd expect, but it's what the standard says.

> PS I know there's an 'Australia/Brisbane' time zone I can use

Yup, that's the one that would give you what you'd expect...

//Magnus

Re: BUG #4100: GMT offsets are wrong

From
Tom Lane
Date:
Magnus Hagander <magnus@hagander.net> writes:
> Peter Coxhead wrote:
>> I find the results to selecting the time in a GMT offset are
>> backwards, so

> I think you're being bitten by the stupid rules of POSIX timezones.
> They declare + as being west of GMT, and - as east. Which is not what
> you'd expect, but it's what the standard says.

Well, it's just as sane as the other way.  The problem is that POSIX
specified one sign convention and ISO-8601 chose the other one ...
so we're kinda stuck here.

The general rule is that PG follows the ISO convention for everything
*except* POSIX-style timezone names, where obviously we must do what
POSIX said.  I'm not entirely certain if we were consistent about this
as far back as 7.4, though.  Our timezone code got pretty heavily
revamped in 8.0.

            regards, tom lane