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

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

> 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

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

> > 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