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

"Thomas G. Lockhart" <lockhart(at)alumni(dot)caltech(dot)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