Обсуждение: datetime regression test fails at daylight savings transitions

Anyone else notice that the datetime regression test is barfing today?
For example,

SELECT ('today'::datetime = ('tomorrow'::datetime - '1 day'::timespan));

is returning FALSE instead of the expected TRUE.  I thought I'd broken
something in my installation, but further investigation tells the tale:

regression=> select 'today'::datetime;
?column?
----------------------------
Sun Oct 25 00:00:00 1998 EDT
(1 row)

regression=> select 'tomorrow'::datetime;
?column?
----------------------------
Mon Oct 26 00:00:00 1998 EST
(1 row)

regression=> select 'tomorrow'::datetime - '1 day'::timespan;
?column?
----------------------------
Sun Oct 25 01:00:00 1998 EDT
(1 row)

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.

It's debatable whether this is a bug in the date/time datatypes,
or just sloppy coding of the regression test.  It probably oughta be
documented somewhere, either way.  Also, whichever way we decide the
above expressions ought to be handled, the docs ought to explain
how to get the other functionality.
        regards, tom lane

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

> It's debatable whether this is a bug in the date/time datatypes,
> or just sloppy coding of the regression test.  It probably oughta be
> documented somewhere, either way.  Also, whichever way we decide the
> above expressions ought to be handled, the docs ought to explain
> how to get the other functionality.

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

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


The real lesson to me is to never put out a "last call" to ask people to
run the regression tests on the day of a daylight savings time
transition...
                      - Tom

"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

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

Well, no I wasn't expecting that really :)

I just wanted to be sure to test 'yesterday' and 'tomorrow' behavior,
and didn't want to omit those tests just because they might fail for ~1%
of the year.

Making 'day' a qualitative time is probably possible, just chewing up
another 4 bytes of storage (for 16 bytes rather than 12). But we'll have
to think it through to make sure there aren't other side effects or
other no-so-expected behavior under other conditions.
                   - Tom

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> Making 'day' a qualitative time is probably possible, just chewing up
> another 4 bytes of storage (for 16 bytes rather than 12). But we'll have
> to think it through to make sure there aren't other side effects or
> other no-so-expected behavior under other conditions.

Well, actually, this is a hard problem, and I don't think it's really
possible to get it right with a pure data-type-based approach (unless
you are willing to have many more and more-complex types than Postgres
now does).

To take one example, if we were to distinguish '24 hours' (absolute) and
'1 day' (symbolic) in timespans, which flavor should subtracting two
datetime values produce?  Right now we get stuff like

play=> select '1998-10-26'::datetime - '1998-10-24'::datetime;
?column?
---------------
@ 2 days 1 hour
(1 row)

which is correct (if "day" == "24 hours") but probably unexpected.

In fact it's worse than that, because the behavior depends on
timezone setting:

play=> set timezone = 'GMT';
SET VARIABLE
play=> select '1998-10-26'::datetime - '1998-10-24'::datetime;
?column?
--------
@ 2 days
(1 row)

Here the timezone is (I presume) being applied during conversion of the
string constants into datetime values, while the subtract operator isn't
paying attention to it.  But the operator's behavior would also become
timezone-sensitive if it had to produce a "symbolic" timespan.  I
suspect we don't really want operator behavior varying depending on the
timezone setting --- it's bad enough that the I/O behavior varies.

I think it's easy to come up with examples of applications that would
want either behavior, so you can't expect a single subtract operator
to solve everyone's problem.  Probably best to leave the behavior of
the data types alone --- a person who wants the kind of behavior I'm
thinking of will need to do his own programming.  (In practice, you
can't do this sort of arithmetic with only timestamps as input anyway;
you need context information about timezone, holidays, etc, and you
probably want to pass the context info explicitly rather than via a
central global variable.  Otherwise dealing with more than one timezone
is a nightmare.)

So I withdraw the suggestion that we need to rethink the behavior of
the date/time datatypes.  But I do suggest that we document somewhere
that the datetime regression test will fail near daylight savings
transitions...  perhaps the README in the test/regress directory is the
right place.  Or could we put a comment into the SQL test code, so that
it would be visible right in the regression diffs when the problem
occurs?
        regards, tom lane