Thread: 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)