Re: timestamp problem - Mailing list pgsql-novice
From | Michael Glaesemann |
---|---|
Subject | Re: timestamp problem |
Date | |
Msg-id | D78F315C-36B5-4D2E-A63A-3169CBB986AD@seespotcode.net Whole thread Raw |
In response to | timestamp problem ("Wright, George" <George.Wright@infimatic.com>) |
List | pgsql-novice |
On Oct 31, 2007, at 9:07 , Wright, George wrote: > The OS is Suse 10.2, timezone files have been updated with the > files in tzdata2007h.tar.gz. What version of PostgreSQL? I believe in recent versions PostgreSQL uses its own time zone library, not the system time zone files. > The settings on the box are Brazil East. The hour of 2007-10-14 > 00:00:00 to 2007-10-14 00:59:59 does not exist. In that part of > Brazil they move the clock from midnight to 1am as they step > forward into DST. Time zones and DST are tricky. I believe PostgreSQL takes the input and > myhost=> select isfinite(timestamp '2007-10-14 00:00:00 BRST'); > > isfinite > > ---------- > > t > > (1 row) Out of curiosity, what would you expect it to return? AIUI, isfinite just returns true or false if the timestamp is finite or not, not whether or not it's a valid time. test=# select isfinite('infinity'::timestamp with time zone); isfinite ---------- f (1 row) test=# select isfinite('-infinity'::timestamp with time zone); isfinite ---------- f (1 row) test=# select isfinite('2007-10-14 00:00:00'::timestamp with time zone); isfinite ---------- t (1 row) Timestamps with time zones are stored internally at UTC: display with time zone is determined by client settings (which default to the server time zone setting). I don't think PostgreSQL will reject a timestamp string just because it's an hour that's skipped due to a DST change: it'll convert it to UTC and display it in the appropriate time zone for the client. For example, there was a DST shift at 2006-04-02 02:00:00 in US/Central: test=# show time zone; TimeZone ------------ US/Central (1 row) test=# select '2006-04-02 01:00:00'::timestamp with time zone; timestamptz ------------------------ 2006-04-02 01:00:00-06 (1 row) test=# select '2006-04-02 02:00:00'::timestamp with time zone; timestamptz ------------------------ 2006-04-02 03:00:00-05 (1 row) test=# select '2006-04-02 02:30:00'::timestamp with time zone; timestamptz ------------------------ 2006-04-02 03:30:00-05 (1 row) While officially the hour between 02:00 and 03:00 was officially skipped, PostgreSQL takes the input and converts it appropriately. Note it displays the timestamp correctly, taking into account the DST shift. > badger=> select isfinite(date '2007-10-14 24:00:01'); > > ERROR: date/time field value out of range: "2007-10-14 24:00:01" What time is 24:00:01? I believe that's your problem. It's not a valid input format for a timestamp. test=# select '2007-10-14 24:00:00'::date; date ------------ 2007-10-14 (1 row) test=# select '2007-10-14 24:00:01'::date; ERROR: date/time field value out of range: "2007-10-14 24:00:01" You've got three different, unconnected issues here: 1) I doubt PostgreSQL is using the time zone files you think it is; 2) PostgreSQL doesn't store time zone information; 3) isfinite doesn't have anything to do with the issue you're seeing. Michael Glaesemann grzm seespotcode net
pgsql-novice by date: