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

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