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