From: | Michael Glaesemann <grzm(at)myrealbox(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | interval->day first cut |
Date: | 2005-06-12 14:26:53 |
Message-ID: | 0E887DA6-D63E-40BB-A479-7EFC9463B688@myrealbox.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-06-12 15:08:01 | Re: creating WITH HOLD cursors using SPI |
Previous Message | Yann Michel | 2005-06-12 10:46:14 | Re: User/Group Quotas Revisited |