BUG #4100: GMT offsets are wrong

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

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