April 1

On Mon, Mar 04, 2002 at 09:57:56AM +1100, Andrew Bartley wrote:

Hi

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?

Someone actually answered this on this list not so long ago. Your problem is
that due to daylight savings, March 31 is actually 25 hours long, not 24.
Your use of interval promotes the date to a datetime, adds 24 hours and
truncates back to a date leaving you with the same date.

Two solutions:

1. Don't use interval.

# select date('2002-03-31') + 1;
result 2002-04-01

2. Add 28 hours instead
# select date(date('2002-03-31') + interval('28 hours'))
result 2002-04-01

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

Thanks,

I have no Idea how this question was posted again. I certainly did not
repost this question.

----- Original Message -----
From: "Martijn van Oosterhout" <kleptog@svana.org>
To: "Andrew Bartley" <abartley@evolvosystems.com>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, March 08, 2002 9:32 AM
Subject: Re: [GENERAL] April 1

On Mon, Mar 04, 2002 at 09:57:56AM +1100, Andrew Bartley wrote:

Hi

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?

Someone actually answered this on this list not so long ago. Your problem

is

That's odd, because I believe that daylight savings time starts on Sunday, April 7th, not on Sunday, March 31st. And even at that, that day is only 23 hours long (Not 25), as you move your clock ahead in the spring (The 2am - 3am hour is skipped.)

Martijn van Oosterhout <kleptog@svana.org> 03/07/02 04:32PM >>>

On Mon, Mar 04, 2002 at 09:57:56AM +1100, Andrew Bartley wrote:

Hi

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?

Someone actually answered this on this list not so long ago. Your problem is
that due to daylight savings, March 31 is actually 25 hours long, not 24.
Your use of interval promotes the date to a datetime, adds 24 hours and
truncates back to a date leaving you with the same date.

Two solutions:

1. Don't use interval.

# select date('2002-03-31') + 1;
result 2002-04-01

2. Add 28 hours instead
# select date(date('2002-03-31') + interval('28 hours'))
result 2002-04-01

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/

If the company that invents a cure for AIDS is expected to make their
money back in 17 years, why can't we ask the same of the company that
markets big-titted lip-syncing chicks and goddamn cartoon mice?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

That's odd, because I believe that daylight savings time starts on Sunday, April 7th...

Only in some time zones. I'm not sure which zone the original example
was run in, but all things are possible somewhere in the world ;)

- Thomas

On Thu, Mar 07, 2002 at 04:43:53PM -0600, Dale Anderson wrote:

That's odd, because I believe that daylight savings time starts on Sunday,
April 7th, not on Sunday, March 31st. And even at that, that day is only
23 hours long (Not 25), as you move your clock ahead in the spring (The
2am - 3am hour is skipped.)

It depends. I'm in the southern hemisphere and thus daylight saving is
*ending* and the day is 25 hours.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/