interval->day first cut
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
Michael,
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:
This looks good so far. I could have really used this for 2 calendar
applicaitons, and *will* use it for my next one. This is exactly the kind of
behavior that calendar applications need.
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
I personally don't have a problem with this if the my/dw/hms split is fully
documented. Does it put is in violation of the SQL spec, though? If so, do
we care?
Anyone know how Oracle/DB2 handles this? ( I know how MSSQL handles it --
badly.)
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.
Yes, well, we'll have to document it prominently in the release notes and
elsewhere.
--
Josh Berkus
Aglio Database Solutions
San Francisco