Thread: to_char miscalculation on April Fool's Day, the start of daylight savings

straka (straka@acunet.net) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
to_char miscalculation on April Fool's Day, the start of daylight savings

Long Description
Nip this one in the bud because knowledge of it will hinder adoption of PostgreSQL in commercial applications. Probably
relatedto bug 249 - Daylight savings happened in the US on April 1. 

It's April 3 and I'm developing an update routine to maintain expired records, some of which expired on April 1.  When
theserecords didn't get updated, I investigated and identified the alleged bug (which is potentially devastating
financiallybased 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;

You get a correct result of 2000-03-01 if you enter:
select to_char(now() -33, 'YYYY-MM-DD');

My system is Eastern Daylight Time. I'm running on Red Hat Linux 6.2 - select version() returns the following:
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled

Sample Code
thedb=# select to_char(now(), 'YYYY-MM-DD');
  to_char
------------
 2001-04-03
(1 row)

thedb=# select to_char(now() -1, 'YYYY-MM-DD');
  to_char
------------
 2001-04-02
(1 row)

thedb=# select to_char(now() -2, 'YYYY-MM-DD');
  to_char
------------
 2001-03-31
(1 row)

thedb=# select to_char(now() -3, 'YYYY-MM-DD');
  to_char
------------
 2001-03-31
(1 row)

thedb=# select to_char(now() -33, 'YYYY-MM-DD');
  to_char
------------
 2001-03-01
(1 row)


No file was uploaded with this report

On Tue, Apr 03, 2001 at 11:24:02AM -0400, pgsql-bugs@postgresql.org wrote:

> Short Description
> to_char miscalculation on April Fool's Day, the start of daylight savings

 This is common date/time bug in 7.0.x with daylight savings date.=20
Already fixed in 7.1.

 Thanks
    Karel

--=20
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
=20
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz