Обсуждение: 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
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.
Вложения
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
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