Thread

1. timestamp/date bug

   Juan Flynn <juan@netsoc.tcd.ie> — 2001-02-04T18:31:54Z

   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
   
   

2. Re: timestamp/date bug

   Karel Zak <zakkr@zf.jcu.cz> — 2001-02-05T16:22:26Z

   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
             
   
   
   
   

3. Re: timestamp/date bug

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-05T16:22:35Z

   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
   
   

4. Re: timestamp/date bug

   Nabil Sayegh <nsmail@sayegh.de> — 2001-02-05T16:38:45Z

   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)