Thread: 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/

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

Sean

  ----- Original Message -----=20
  From: Bruno Pr=E9vost=20
  To: pgsql-bugs@postgresql.org=20
  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

=20=20=20

Bruno Prévost <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