Обсуждение: timestamp/date bug

I've come across an odd bug in Postgres 7 (7.0.3 I think) that's probably
best explained with an example:

SELECT to_char(timestamp '23-Mar-2000' + 1, 'Mon DD');
 to_char
---------
 Mar 24
(1 row)

Which is correct.

SELECT to_char(timestamp '23-Mar-2000' + 2, 'Mon DD');
 to_char
---------
 Mar 25
(1 row)

Also fine, but...

SELECT to_char(timestamp '23-Mar-2000' + 3, 'Mon DD');
 to_char
---------
 Mar 25
(1 row)

Which is wrong, however:

SELECT to_char(timestamp '23-Mar-2000' + 4, 'Mon DD');
 to_char
---------
 Mar 27
(1 row)

is correct.

This odd date bug seems to skip over March 26 2000, and it occurs also
on March 25 2001 and March 30 2002.

It caught me by surprise as I've been using Postgres for a while now and it's been
working really well, and the other day I got a phone call saying there was
no March 25. A current workaround is for me to redo the whole project with
dates and times instead of timestamps but that's a bit of a big job as they
are using the database everyday now.

Regards,
Juan

_________________________________________________________________________
Juan Flynn                                             juan@netsoc.tcd.ie
P.R.O., Dublin University Internet Society                 www.netsoc.tcd.ie

juan@netsoc.tcd.ie (Juan Flynn) writes:
> This odd date bug seems to skip over March 26 2000, and it occurs also
> on March 25 2001 and March 30 2002.

Would those happen to be daylight-savings-time transition dates in your
timezone?  If so, this is a known bug; it's fixed for 7.1.

            regards, tom lane

On Sun, 4 Feb 2001, Juan Flynn wrote:

> I've come across an odd bug in Postgres 7 (7.0.3 I think) that's probably
> best explained with an example:

> SELECT to_char(timestamp '23-Mar-2000' + 3, 'Mon DD');
>  to_char
> ---------
>  Mar 25
> (1 row)


in 7.1:

test=# SELECT to_char(timestamp '23-Mar-2000' + 3, 'Mon DD');
 to_char
---------
 Mar 26
(1 row)

test=# select timestamp '23-Mar-2000' + 3;
  ?column?
------------
 2000-03-26
(1 row)


 It's probably already fixed bug with timezones and "magic" dates :-)

            Karel

Juan Flynn wrote:

> SELECT to_char(timestamp '23-Mar-2000' + 3, 'Mon DD');
>  to_char
> ---------
>  Mar 25
> (1 row)
>
> Which is wrong, however:

Dates are interpreted "at a special time" (midnight).
Mar 25 is most likely a daylightsaving day for your timezone.

Just add some hours to the result and you're fine.
(In your example substitute 3 with timespan("3 days 4 hours"))
or something like that.
AFAIK this has been fixed in 7.1

--
 Nabil Sayegh
 GPG-Key available at http://www.sayegh.de
 (see http://www.gnupg.org for details)