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