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 17:40:38
Message-ID: 51548D6D5BEB57468163194A8C1A0E9831954F@MAGPTCPEXC02.na.mag-ias.net (view raw or flat)
Thread:
Lists: pgsql-novice
Fair enough.

In the Bruce Momjian PostGreSQL Introduction and Concepts book:

In the temporal section it mentions

Function			Returns
isfinite(col)		BOOLEAN indicating whether col is a valid date.

That seems to be where I went wrong.

Is there a calendar validation routine in PostGreSQL? I didn't see one
in the \df listing.



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


On Oct 31, 2007, at 10:39 , Wright, George wrote:

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

I can see where you're coming from, but I don't necessarily agree.  
2007-02-29 is an invalid date, regardless of time zone. 24:00:01 is  
an invalid time regardless of time zone. Perhaps this is a question  
of not being strict enough on input, but given how often DST and time  
zone rules change, I don't think I'd want a situation where  
PostgreSQL might accept a particular string as a valid timestamp one  
day and reject it the next due to an update of time zone data files.

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

There's no such thing as an epoch second with respect to a time zone.

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

Well, that would make it appear that what you did properly updated  
the time zone data for PostgreSQL. But as you noted, the PostgreSQL  
time zone files are different from the systems.

> The name of the function isfinite is a bit vague. Maybe as
> you suggest, its purpose is not really to do a calendar validation.

My question is why did you even think it did? The docs (and the  
function name) are pretty clear what it does:

isfinite(timestamp)	boolean	Test for finite time stamp (not equal to

infinity)	isfinite(timestamp '2001-02-16 21:28:30')	true

http://www.postgresql.org/docs/8.2/interactive/functions- 
datetime.html#FUNCTIONS-DATETIME-TABLE

Michael Glaesemann
grzm seespotcode net



In response to

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2007-10-31 17:51:57
Subject: Re: timestamp problem
Previous:From: Michael GlaesemannDate: 2007-10-31 16:04:57
Subject: Re: timestamp problem

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