Thread: timestamp with time zone output incorrect

timestamp with time zone output incorrect

From

Steve Martin

Date:

26 April 2008, 08:00:01

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

Re: timestamp with time zone output incorrect

From

Martijn van Oosterhout

Date:

26 April 2008, 14:55:04

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/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Attachment

signature.asc

Re: timestamp with time zone output incorrect

From

Tom Lane

Date:

26 April 2008, 17:19:03

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

Re: timestamp with time zone output incorrect

From

Steve Martin

Date:

28 April 2008, 06:15:25

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

Re: timestamp with time zone output incorrect

From

Lew

Date:

28 April 2008, 06:15:41

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 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.

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