Re: Interval->day proposal

On Jun 1, 2005, at 1:42 PM, Michael Glaesemann wrote:


> -- v8.0.3
> test=# select '25 hours'::interval;
>     interval
> ----------------
> 1 day 01:00:00
> (1 row)
>
> -- new interval code
> 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?
>


I've dug a bit, and this is definitely not spec compliant, as
interval hours must be in the range 0-23. From Section 4.6.3 of SQL/
Foundation (2003 draft)

Table 6 — Valid values for fields in INTERVAL values
Keyword    Valid values of INTERVAL fields
YEAR     Unconstrained except by <interval leading field precision>
MONTH    Months (within years) (0-11)
DAY    Unconstrained except by <interval leading field precision>
HOUR    Hours (within days) (0-23)
MINUTE    Minutes (within hours) (0-59)
SECOND    Seconds (within minutes) (0-59.999...)

I'm not quite sure what the parenthetical "within days" adds to the
definition. However, the point of keeping a separate day field is so
we can keep the number of days separate from the number of 24-hour
units, and limiting the range of HOUR between 0 and 23 makes that
impossible, it appears.

I'd be happy if someone can let me know I'm reading the spec wrong!

Michael Glaesemann
grzm myrealbox com