I've completed my first cut of adding a day field to the interval
struct and patched up the regression tests for places where it failed
due to the new behavior (e.g., interval '19:00' + interval '6:00' =
interval '25:00'). I haven't added any regression tests for the DST
behavior, but it works (and this could be the start of the regression
tests). Note: DST changed on 2005-04-03:
test=# SET TIME ZONE 'CST7CDT';
SET
test=# SELECT * FROM TIMESTAMPTZ_TBL; t1
------------------------
2005-03-31 22:00:00-07
2005-04-01 22:00:00-07
2005-04-02 22:00:00-07
2005-04-03 23:00:00-06
(4 rows)
test-# , t1 + INTERVAL '24 hours' as "+24 hours" FROM
TIMESTAMPTZ_TBL; t1 | +1 day | +24 hours
------------------------+------------------------
+------------------------
2005-03-31 22:00:00-07 | 2005-04-01 22:00:00-07 | 2005-04-01 22:00:00-07
2005-04-01 22:00:00-07 | 2005-04-02 22:00:00-07 | 2005-04-02 22:00:00-07
2005-04-02 22:00:00-07 | 2005-04-03 22:00:00-06 | 2005-04-03 23:00:00-06
2005-04-03 23:00:00-06 | 2005-04-04 23:00:00-06 | 2005-04-04 23:00:00-06
(4 rows)
test=# SELECT t1, t1 - INTERVAL '1 day' as "-1 day"
test-# , t1 - INTERVAL '24 hours' as "-24 hours" FROM
TIMESTAMPTZ_TBL; t1 | -1 day | -24 hours
------------------------+------------------------
+------------------------
2005-03-31 22:00:00-07 | 2005-03-30 22:00:00-07 | 2005-03-30 22:00:00-07
2005-04-01 22:00:00-07 | 2005-03-31 22:00:00-07 | 2005-03-31 22:00:00-07
2005-04-02 22:00:00-07 | 2005-04-01 22:00:00-07 | 2005-04-01 22:00:00-07
2005-04-03 23:00:00-06 | 2005-04-02 23:00:00-07 | 2005-04-02 22:00:00-07
(4 rows)
test=# SELECT INTERVAL '1 day' = INTERVAL '24 hours';
?column?
----------
t
(1 row)
One interesting fallout of this is that adding two SQL-compliant
intervals can produce non-SQL-compliant output:
test=# select interval '3 days 16:39' + interval '1 day 15:32' as
"interesting"; interesting
-----------------
4 days 32:11:00
(1 row)
On a related issue, Tom commented:
> The spec says what results you must get from
> spec-compliant input; I don't think it says we may take only
> spec-compliant input. (If we were to read it that way, we'd have
> to rip out every PG extension, not only the interval-related ones.)
>
> The entire *point* of this change is to be able to distinguish
> "25 hours" from "1 day 1 hour", so you can hardly argue that being
> able to do that is not what we want it to do...
I've added a interval_simplify function which assumes 1 day = 24
hours and puts the interval in SQL-spec form. This could be exposed
to let people "reduce" their intervals. However, I'm concerned this
is surprising behavior.
Thoughts?
Michael Glaesemann
grzm myrealbox com