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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend