Re: A date bug: number of day of October

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Guillaume Perréal <perreal(at)lyon(dot)cemagref(dot)fr>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: A date bug: number of day of October
Date: 2000-04-14 23:43:56
Message-ID: Pine.LNX.4.21.0004141943520.401-100000@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Ryan Kirkpatrick 2000-04-14 23:48:18 Re: Minimal patches for PostgreSQL 7.0b3 on NetBSD/alpha 1.4.1....
Previous Message Kamaev Vladimir 2000-04-14 16:23:40 Bad result?