Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group