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