Skip site navigation (1) Skip section navigation (2)

Re: BUG #1993: Adding/subtracting negative time intervals

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Michael Fuhr <mike(at)fuhr(dot)org>,Michael Glaesemann <grzm(at)myrealbox(dot)com>,Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au>, Russell Smith <mr-russ(at)pws(dot)com(dot)au>,pgsql-bugs(at)postgresql(dot)org, Nicholas <hb(at)x256(dot)com>
Subject: Re: BUG #1993: Adding/subtracting negative time intervals
Date: 2005-10-25 17:28:00
Message-ID: 18319.1130261280@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2005-10-25 18:08:08
Subject: Re: determining random_page_cost value
Previous:From: Yohanes SantosoDate: 2005-10-25 16:29:21
Subject: determining random_page_cost value

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-10-25 18:16:08
Subject: Re: RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3
Previous:From: Jean-Pierre PelletierDate: 2005-10-25 16:02:34
Subject: RIGHT JOIN is only supported with merge-joinable join conditions, PostgreSQL 8.1 beta3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group