Re: [HACKERS] datetime regression test fails at daylight savings transitions

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
>> Whoops.  Looks like "'1 day'::timespan" is treated as "24 hours",
>> not as "the same local time next day".  Twice a year (if you observe
>> DST) there is a difference.

> Sure. But timespan doesn't know anything about date context. What you
> are really asking about is the behavior with math across two date/time
> data types.

Right.  The routines that add or subtract a timespan to/from a datetime
would have to do this if we want it done.

> Hmm. So the debate won't cover whether Tom got it right, just how wrong
> he got it, eh? :)

Didn't mean to ruffle your feathers ... the reason that I'm sensitive
to this issue is that I've blown it several times myself ;-).  In the
applications that I'm working on (financial trading), it's critical that
the right things happen at the right times each day ... and the right
time is generally defined in terms of local time.  Daylight savings time
bugs have caused us more headaches than I expect to have for the Y2K
rollover.

> The docs actually mention the distinction between "qualitative time"
> (years and months) and "quantitative time" (days, hours, minutes,
> seconds) when discussing the "timespan" data type.
> You will find that months and years do behave the way you would expect,
> since they are stored as "qualitative" times. So adding a month to a
> time at the end of the previous month will try to line things up:
> tgl=> select '1998-01-31'::datetime + '1 month'::timespan;
> ----------------------------
> Sat Feb 28 00:00:00 1998 GMT

Hmm.  This offers a potential solution, then.  I propose that "day"
ought to be considered a qualitative time interval, and that'now'::datetime + '1 day'::timespan
need not yield the same thing as'now'::datetime + '24 hours'::timespan
Instead the first form ought to yield the same local time on the target
day.  (I assume adding months or years does preserve the local time
of day already?)

Changing things in that way might be infeasible because of backwards
compatibility constraints, but I think this is what the natural
interpretation would be.  (Clearly it's what the writer of the datetime
regression test was expecting...)
        regards, tom lane