Problem with interval

On Wed, Oct 06, 2004 at 04:26:06PM -0400, Bruno Pr?vost wrote:

I'm using postgres version 7.4.5 and had a problem with interval

Here is my query :
select '2004/10/31'::timestamptz + '1 day'::interval;

Here is the answer :
10/31/2004 11:00:00 PM

I'd guess that Daylight Saving Time is the culprit -- most places
that use it revert to Standard Time on 31 Oct this year. Suppose
you're in the EST5EDT time zone, which is 4 hours behind UTC during
the summer. '2004/10/31'::timestamptz is '2004-10-31 00:00:00-04'.
If you add one day, that would be '2004-11-01 00:00:00-04'. Only
by then you're 5 hours behind UTC instead of 4 hours behind, so the
time becomes '2004-10-31 23:00:00-05', which is the same time adjusted
for your time zone.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

=?iso-8859-1?Q?Bruno_Pr=E9vost?= <bp@interaction.ws> writes:

I'm using postgres version 7.4.5 and had a problem with interval

Here is my query :
select '2004/10/31'::timestamptz + '1 day'::interval;

Here is the answer :
10/31/2004 11:00:00 PM

If you are in a US time zone then that's the expected answer, because
2004/10/31 is a DST transition day. '1 day' is currently always taken
to mean '24 hours', and 2004/10/31 is in fact 25 hours long...

Personally I would like to see the interval type redone so that days are
separate from smaller units and this case behaves more intuitively.
But that's been on the wish list for years and it's not gotten to the
top of anyone's to-do list.

In the meantime, you might ask yourself if you really want to use
type timestamptz at all, as opposed to date. ('2004/10/31'::date + 1
would give the answer you want.) Or consider using timestamp without
time zone.

regards, tom lane

Daylight savings time falls back an hour and 11/01/2004 00:00:00 becomes the answer you got because 10/31/2004 has 25 hours in it. Sort of a bug in "real time" the interval was correct, but the day was longer than the interval.

Sean

----- Original Message -----
From: Bruno Prévost
To: pgsql-bugs@postgresql.org
Sent: Wednesday, October 06, 2004 1:26 PM
Subject: [BUGS] Problem with interval

Hi,

I'm using postgres version 7.4.5 and had a problem with interval

Here is my query :
select '2004/10/31'::timestamptz + '1 day'::interval;

Here is the answer :
10/31/2004 11:00:00 PM

Is it a bug or I do something wrong?
I tested my query on 3 server and have always the same answer.

Thanks
Regards