Thread

1. to_char miscalculation on April Fool's Day - the start of daylight savings

   Chris Straka <cstraka@incontactnow.com> — 2001-04-03T15:22:12Z

   Likely related to bug #249.  I also entered this bug in the bug tool 
   database
   
   Hello,
   It's April 3 and I'm developing an update routine to maintain expired 
   records, some of which expired on April 1.  When these records didn't get 
   updated, I investigated and identified the alleged bug (which is 
   potentially devastating based on date intensive calculations in financial 
   applications).
   
   The quickest demonstration is as follows:
   select to_char(now(), 'YYYY-MM-DD');
   
   Based on the date you see, subtract an integer value from now() so the 
   query result shows 2-Apr.  Assuming it's April 3, enter:
   select to_char(now() -1, 'YYYY-MM-DD');
   
   It comes back fine with 2001-04-02.
   
   Now decrement by x + 1 to see the bug.  Assuming it's April 3, enter:
   select to_char(now() - 2, 'YYYY-MM-DD');
   
   It comes back incorrectly with 2001-03-31;
   
   The bug is specific to April 1.  Assuming it's April 3, you get a correct 
   result of 2000-03-01 if you enter:
   select to_char(now() -33, 'YYYY-MM-DD');
   
   I'm running on Red Hat Linux 6.2 - select version() returns the following:
   PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
   
   Please advise if you need more info.
   
   Chris Straka
   
   

2. Re: to_char miscalculation on April Fool's Day - the start of daylight savings

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-04-06T13:54:53Z

   > It's April 3 and I'm developing an update routine to maintain expired
   > records, some of which expired on April 1.  When these records didn't get
   > updated, I investigated and identified the alleged bug (which is
   > potentially devastating based on date intensive calculations in financial
   > applications).
   
   A known problem which is fixed in the upcoming release. Workaround
   follows...
   
   > The quickest demonstration is as follows:
   > select to_char(now(), 'YYYY-MM-DD');
   
   Have you tried to use "date 'today'" rather than "now()"? As in
   
     select to_char(date 'today' - 1, 'YYYY-MM-DD');
   
   which uses the DATE type rather than ABSTIME/TIMESTAMP returned from
   now(). That should eliminate the problem, since the DATE type does not
   try to carry along time zone information. Seems to work for me on 7.0.3.
   
                         - Thomas
   
   

3. Re: Re: to_char miscalculation on April Fool's Day - the start of daylight savings

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-06T14:22:45Z

   Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
   > Have you tried to use "date 'today'" rather than "now()"? As in
   >   select to_char(date 'today' - 1, 'YYYY-MM-DD');
   
   That will still fail in 7.0.* when the selected date is a DST transition
   day, because of the bug in date-to-timestamp conversion (which will
   happen at the input to to_char()).
   
   Although that problem is fixed in 7.1, there's a definitional problem
   that's not fixed:
   
   regression=# select timestamp 'today';
           ?column?
   ------------------------
    2001-04-06 00:00:00-04
   (1 row)
   
   regression=# select timestamp 'today' - interval '4 days';
           ?column?
   ------------------------
    2001-04-02 00:00:00-04
   (1 row)
   
   regression=# select timestamp 'today' - interval '5 days';
           ?column?
   ------------------------
    2001-03-31 23:00:00-05
   (1 row)
   
   This is correct if you consider interval '5 days' to mean interval
   5 * 24 hours, but I think most people would consider the result wrong.
   
   IMHO we need timestamp and interval calculations to maintain three
   values not two: months, days, and seconds.  The only way to do the
   above in an unsurprising fashion is for days to be symbolic rather
   than hard-wired as 86400 seconds.  It's exactly the same as the
   problem with 1 month not being a fixed number of days.
   
   			regards, tom lane
   
   

4. Re: Re: to_char miscalculation on April Fool's Day - the start of daylight savings

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-04-06T15:12:30Z

   > > Have you tried to use "date 'today'" rather than "now()"? As in
   > >   select to_char(date 'today' - 1, 'YYYY-MM-DD');
   > That will still fail in 7.0.* when the selected date is a DST transition
   > day, because of the bug in date-to-timestamp conversion (which will
   > happen at the input to to_char()).
   
   Ah, right. I had tested in the GMT time zone, which cures all ills :(
   
                       - Thomas