Re: Interval->day proposal

On May 31, 2005, at 1:40 AM, Tom Lane wrote:

> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>
>
>> Unfortunately, it appears that tri-partitioning INTERVAL ( year/
>> month ;
>> week/day ; hour/minute/second ) is a violation of the SQL spec
>> which has only
>> the two partitions ( year/month ; week/day/hour/minute/second ).
>>
>>
>
> I think it's an extension of the spec, not a violation. In
> particular, if you were working in a daylight-savings-less timezone,
> you could not tell the difference (could you?)
>

I've started working on this change, and one difference has shown up
immediately in the regression tests. v8.0.3 currently returns:

SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
9 years...
----------------------------------
! 9 years 1 mon -11 days -10:46:00
(1 row)

With my first round of changes,

SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
9 years...
----------------------------------
! 9 years 1 mon -12 days +13:14:00
(1 row)

These are equivalent in both CVS and my branch, as '1 day'::interval
= '24 hours'::interval. I haven't checked the SQL spec yet (and
intend to do so), but is there a canonical form for intervals that we
need to return? I can imagine there might be, and if the spec
considers only months and time, I could see where they might want
days and time to have the same sign, and put results in "simplest
form". Even if the spec doesn't require it, the behavior is
definitely changed even outside of DST-aware code.

-- v8.0.3
test=# select '9 years 1 mon -11 days -10:46:00'::interval;
interval
----------------------------------
9 years 1 mon -11 days -10:46:00
(1 row)

test=# select '9 years 1 mon -12 days +13:14:00'::interval;
interval
----------------------------------
9 years 1 mon -11 days -10:46:00
(1 row)

test=# select '25 hours'::interval;
interval
----------------
1 day 01:00:00
(1 row)

-- new interval code
test=# select ' 9 years 1 mon -11 days -10:46:00'::interval;
interval
----------------------------------
9 years 1 mon -11 days -10:46:00
(1 row)

test=# select '9 years 1 mon -12 days +13:14:00'::interval;
interval
----------------------------------
9 years 1 mon -12 days +13:14:00
(1 row)

test=# select '25 hours'::interval;
interval
----------
25:00:00
(1 row)

I'll be digging into the spec later and post what I find. Thoughts?

Michael Glaesemann
grzm myrealbox com