Re: A date bug: number of day of October

Guillaume Perréal writes:

> Try computing (replace XXXX with some differents years):
>     'XXXX-10-01'::datetime + '1 month'::interval
> Depending on the year, you can get 'XXXX-11-01' as expected or 'XXXX-10-31'
> which is bug.

In the CET zone (where you probably live) daylight savings time ends in
October, so it is true that

    1999-10-01 00:00:00 + (24h * 31 days) = 1999-10-31 23:00:00

Of course a month is not defined as "24h * 30/31 days" but instead as the
time between xxxx-yy-zz and xxxx-(yy+1)-zz, so the above could be condered
wrong. It is especially wrong that the same thing happens if you use DATE
instead of TIMESTAMP (or DATETIME, now deprecated).

You get the above behaviour between 1996 and 2037. Before 1996 you can
observe the same behaviour with September, because back then we switched
in September! The system is pretty smart. Of course after 2037 we're
doomed anyway.

The bottom line is that INTERVAL in its current implementation has
deficiencies and it's not SQL compliant either.


--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden