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?