Skip site navigation (1) Skip section navigation (2)

Re: timestamp problem

From: "Wright, George" <George(dot)Wright(at)infimatic(dot)com>
To: "Michael Glaesemann" <grzm(at)seespotcode(dot)net>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: timestamp problem
Date: 2007-10-31 15:39:10
Message-ID: 51548D6D5BEB57468163194A8C1A0E9831954E@MAGPTCPEXC02.na.mag-ias.net (view raw or flat)
Thread:
Lists: pgsql-novice
The version is 8.1.5

I would expect:
myhost=> select isfinite(timestamp '2007-10-14 00:00:00 BRST');

to error out just like:

myhost=> select isfinite(date '2007-02-29 00:00:01');
ERROR:  date/time field value out of range: "2007-02-29 00:00:01"

as there really shouldn't be an epoch representation of a non-existent
time. The next epoch second after 2007-10-13 23:59:59 is 2007-10-14
01:00:00, not 2007-10-14 00:00:00 in this time zone.

As for PostGreSQL using the correct files, I copied the timezone files
to both the OS and the Postgres locations: (and rebooted of course)

/usr/share/zoneinfo
/usr/share/postgresql/timezone  (I assume these are used by PG)

Running the following queries shows the time zone change appropriately
to reflect DST:

myhost=> select timestamp with time zone '2007-10-13 23:59:59';    
      timestamptz       
------------------------
 2007-10-13 23:59:59-03
(1 row)

myhost=> select timestamp with time zone '2007-10-14 01:00:01';
      timestamptz       
------------------------
 2007-10-14 01:00:01-02
(1 row)

myhost=> select timestamp with time zone '2007-10-13 23:59:59 BRT';
      timestamptz       
------------------------
 2007-10-13 23:59:59-03
(1 row)

myhost=> select timestamp with time zone '2007-10-14 01:00:01 BRST';
      timestamptz       
------------------------
 2007-10-14 01:00:01-02
(1 row)

This was not the case before I installed the time zone files.
I believe this shows that the time zone files are installed correctly
for PostGreSQL.

I guess I assumed that since '2007-10-14 00:00:00 BRST' or '2007-10-14
00:00:00 BRT' are invalid, that the conversion to internal epoch seconds
would fail. The name of the function isfinite is a bit vague. Maybe as
you suggest, its purpose is not really to do a calendar validation.



-----Original Message-----
From: Michael Glaesemann [mailto:grzm(at)seespotcode(dot)net] 
Sent: Wednesday, October 31, 2007 11:11 AM
To: Wright, George
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] timestamp problem


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



In response to

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2007-10-31 16:04:57
Subject: Re: timestamp problem
Previous:From: Michael GlaesemannDate: 2007-10-31 15:11:19
Subject: Re: timestamp problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group