That's odd, because I believe that daylight savings time starts on Sunday, April 7th, not on Sunday, March 31st. And
evenat 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
