Re: Interval->day proposal

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


> Josh Berkus <josh@agliodbs.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