Re: BUG #1993: Adding/subtracting negative time intervals - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #1993: Adding/subtracting negative time intervals
Date
Msg-id 18319.1130261280@sss.pgh.pa.us
Whole thread Raw
In response to Re: BUG #1993: Adding/subtracting negative time intervals  (Bruce Momjian <pgman@candle.pha.pa.us>)
Responses Re: BUG #1993: Adding/subtracting negative time intervals
List pgsql-bugs
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Right.  Interval multiplication has always spilled fractional months
>> over to seconds, but never the reverse.  We have to have that same
>> policy now for fractional days.

> OK, I think that makes sense.

I've applied this change to interval_mul and interval_div, but the
justify_hours call is still there in timestamp_mi.  Taking that one out
causes quite a lot of changes in the regression test outputs, so I'm
a bit hesitant to do it.  Arguably, we need separate versions of
timestamp_mi and timestamptz_mi, with a DST-aware calculation in the
latter, but that seems a bit large of a change for late beta.  The
reason is that with 8.1, we have this discrepancy:

regression=# select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval;
        ?column?
------------------------
 2005-10-30 13:22:00-05
(1 row)

regression=# select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29 13:22:00-04'::timestamptz;
    ?column?
----------------
 1 day 01:00:00
(1 row)

ISTM that given the former result, the latter calculation ought to
produce '1 day', not something else.

Another problem I've noticed is that interval output works with a
"struct tm" as intermediate data structure, which means that it cannot
cope with intervals containing a "time" field exceeding 2^31 hours,
because the tm_hour field overflows.  With the new version of
interval_mul this is easily exposed by this test case:

regression=# select 10000 * '1000000 hours'::interval;
     ?column?
------------------
 2147483647:00:00
(1 row)

but it was possible to get the same problem in other ways before,
so I don't think this is interval_mul's fault.  Rather, interval2tm
has got to be replaced with something that can handle the full range of
representable interval values.

Finally, I notice there are no overflow checks in any of the interval
or timestamp arithmetic routines.  This seems like a bad omission,
particularly in the integer-timestamp case where overflow won't be even
a little bit graceful.

So, a few TODO items for future releases:

* Improve timestamptz subtraction to be DST-aware
* Fix interval display to support values exceeding 2^31 hours
* Add overflow checking to timestamp and interval arithmetic

            regards, tom lane

pgsql-bugs by date:

Previous
From: "Jean-Pierre Pelletier"
Date:
Subject: RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3
Next
From: Tom Lane
Date:
Subject: Re: RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3