From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | "Wright, George" <George(dot)Wright(at)infimatic(dot)com> |
Cc: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: timestamp problem |
Date: | 2007-10-31 15:11:19 |
Message-ID: | D78F315C-36B5-4D2E-A63A-3169CBB986AD@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Wright, George | 2007-10-31 15:39:10 | Re: timestamp problem |
Previous Message | Wright, George | 2007-10-31 14:07:52 | timestamp problem |