Thread: April 1

April 1

From

"Andrew Bartley"

Date:

03 March 2002, 23:04:10

I'm having trouble calculating a date of April 1 2002.

Postgres 7.1.3 Linux 2.4.14

select date(date('2002-03-30') + interval('1 day'))

result 2002-03-31

select date(date('2002-03-31') + interval('1 day'))

result 2002-03-31

It seems as though the "+ interval('1 day'))" only adds 23 hours rather than 24.

So adding "interval( '1 day')" to the march 31 returns march 31.

It may have something to do with day light savings.

Can any one suggest a work around?

Is this a bug?

Thanks

Andrew Bartley

Re: April 1

From

Tom Lane

Date:

04 March 2002, 00:21:26

"Andrew Bartley" <abartley@evolvosystems.com> writes:
> I'm having trouble calculating a date of April 1 2002.

What is the daylight savings transition day in your timezone?
(I'm betting March 31.)

> It seems as though the "+ interval('1 day'))" only adds 23 hours rather tha=
> n 24.

No, interval('1 day') is exactly 24 hours.  But March 31 is longer than
24 hours.  You're computing March 31 23:00 hours, and then truncating
that back to March 31.

If you want to calculate at the date level I'd suggest calculating with
dates, not timestamps.

regression=# select date('2002-03-31') + 1;
  ?column?
------------
 2002-04-01
(1 row)

            regards, tom lane

Re: April 1

From

"Raymond O'Donnell"

Date:

04 March 2002, 09:36:10

On 4 Mar 2002 at 12:05, Andrew Bartley wrote:

> Is this a bug?

...or an April Fool gag?? <g>

--Ray.

---------------------------------------------------------
Raymond O'Donnell            http://www.iol.ie/~rod/organ
rod@iol.ie                      The Irish Pipe Organ Page
---------------------------------------------------------