timestamp with time zone output incorrect
Hi,
We are having trouble with the output of timestamp with time zone with
versions 8.1.10 and 8.3.1.
It seems reversed, and change over times are incorrect.
timezone for both is:
=> show timezone ;
TimeZone
-------------
NZST-12NZDT
(1 row)
Note, change over times for this year is:
Sun Apr 06 02:59:59 NZDT 2008 --> Sun Apr 06 02:00:00 NZST 2008
Sun Sep 28 01:59:59 NZST 2008 --> Sun Sep 28 03:00:00 NZDT 2008
On both versions:
=> select timestamp with time zone '2008-01-01 00:00:00';
timestamptz
------------------------
2008-01-01 00:00:00+12
=> select timestamp with time zone '2008-05-01 00:00:00';
timestamptz
------------------------
2008-05-01 00:00:00+13
(1 row)
It seems that the time zone off set is reversed.
Also it seems to be using the old change over times.
=> select timestamp with time zone '2008-03-09 01:00:00';
timestamptz
------------------------
2008-03-09 01:00:00+12
(1 row)
=> select timestamp with time zone '2008-03-09 03:00:00';
timestamptz
------------------------
2008-03-09 03:00:00+13
(1 row)
Checked "postgresql-8.3.1/src/timezone/data/australasia" and the
information here seems correct.
The date on the system (HPUX 11.23) is correct, e.g.
% date
Thu Apr 24 18:22:42 NZST 2008
% echo $TZ
NZST-12NZDT
The database seems to know we are using the New Zealand time zone. It
seems to think summer is coming it is winter.
Any ideas anyone?
Thanks
Steve Martin
Steve Martin wrote:
Hi,
We are having trouble with the output of timestamp with time zone with
versions 8.1.10 and 8.3.1.
It seems reversed, and change over times are incorrect.timezone for both is:
=> show timezone ;
TimeZone -------------
NZST-12NZDT
(1 row)Note, change over times for this year is:
Sun Apr 06 02:59:59 NZDT 2008 --> Sun Apr 06 02:00:00 NZST 2008
Sun Sep 28 01:59:59 NZST 2008 --> Sun Sep 28 03:00:00 NZDT 2008On both versions:
=> select timestamp with time zone '2008-01-01 00:00:00';
timestamptz ------------------------
2008-01-01 00:00:00+12=> select timestamp with time zone '2008-05-01 00:00:00';
timestamptz ------------------------
2008-05-01 00:00:00+13
(1 row)It seems that the time zone off set is reversed.
Those are correct. I just looked up New Zealand on
<http://www.worldtimezone.net/wtz020.php>
for example, and it shows NZST as "GMT+12", plus one more for Daylight Saving
Time.
Likewise,
<http://en.wikipedia.org/wiki/Wellington>
lists Wellington's time zone as
Time zone NZST (UTC+12)
- Summer (DST) NZDT (UTC+13)
--
Lew
On Thu, Apr 24, 2008 at 06:30:27PM +1200, Steve Martin wrote:
Hi,
We are having trouble with the output of timestamp with time zone with
versions 8.1.10 and 8.3.1.
It seems reversed, and change over times are incorrect.timezone for both is:
=> show timezone ;
TimeZone
-------------
NZST-12NZDT
(1 row)
I have no idea what timezone that it. Presumably it switches between
daylight savings and non-daylight savings based on the US rules? I
can't find this timezone anywhere on my system. Perhaps you should try
the timezone Pacific/Auckland to get closer to what you want.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
Martijn van Oosterhout <kleptog@svana.org> writes:
On Thu, Apr 24, 2008 at 06:30:27PM +1200, Steve Martin wrote:
=> show timezone ;
TimeZone
-------------
NZST-12NZDT
(1 row)
I have no idea what timezone that it. Presumably it switches between
daylight savings and non-daylight savings based on the US rules?
Yeah, that's a POSIX zone spec. See
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#DATATYPE-TIMEZONES
As noted there, if the OP really really wants to spell his zone name
that way, he could fool with the "posixrules" file in the timezone
database. But Pacific/Auckland is probably better. (I don't remember
whether 8.1 would honor changes in "posixrules".)
regards, tom lane
Tom Lane wrote:
Martijn van Oosterhout <kleptog@svana.org> writes:
On Thu, Apr 24, 2008 at 06:30:27PM +1200, Steve Martin wrote:
=> show timezone ;
TimeZone
-------------
NZST-12NZDT
(1 row)I have no idea what timezone that it. Presumably it switches between
daylight savings and non-daylight savings based on the US rules?Yeah, that's a POSIX zone spec. See
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#DATATYPE-TIMEZONES
As noted there, if the OP really really wants to spell his zone name
that way, he could fool with the "posixrules" file in the timezone
database. But Pacific/Auckland is probably better. (I don't remember
whether 8.1 would honor changes in "posixrules".)regards, tom lane
Hi,
Thanks Martijn and Tom for your feedback.
Setting the timezone to Pacific/Auckland works.
Re-read the document reference Tom pointed to and found I missed the
comment about being wary of POSIX-style time zones.
Thanks
Steve Martin