Re: Interval oddities

> There are certain oddities in current interval behaviour:
> template1=# select '1200005.567772 seconds'::interval(12);
> ERROR: INTERVAL(12) precision must be between 0 and 6
> The documentation says 0 and 13 (users' manual 3.5.1.6).

OK, docs need fixing.

> Then there seem to be some problems with large numbers:
> template1=# select '1111101.56772 seconds'::interval(2);
> 12 days 20:38:21.5700000001

This is due to floating point rounding issues on output. Try configuring
and compiling with --enable-integer-datetimes and you should see this go
away.

> I see you've started to implement the SQL99 interval type. Shouldn't
> these give an error?
> lfix=# select '5 years'::interval day to second;
> lfix=# select '900 days':: interval year to month;

Probably. I apparently zero out the other fields rather than throwing an
error. Pretty sure that is easy to change.

> and shouldn't this return '50:00' or else give an out of range error?
> lfix=# select '50 hours'::interval hour to minute;
> 02:00

Seems like it should.

> The existing precision implements fractional seconds precision. Are you
> planning to implement the interval leading field precision? (So that I
> can say INTERVAL HOUR(4) TO MINUTE to allow values up to 9999 hours.)

Had not even thought about it.

> At the moment "interval(4) hour to second" is valid syntax for the
> fractional precision, whereas the standard's syntax is "interval hour to
> second(4)"

Yuck. If that is the standard then we need to figure out how to monkey
around the syntax. I haven't looked to see if it makes things easier in
the parser; it might...

> Are you doing any more work on intervals? If not, I would like to
> implement the standard's definition.

The standard does not allow mixing year/month intervals with
day/hour/min/sec intervals. We need to not enforce this restriction, at
least for intervals without explicit unit qualification. I'd also like
to see this data type *not* blow up into a huge footprint,
slow-to-calculate type. Or at least have one of the interval data types
not blow up.

One possibility is to implement another interval type for qualified
intervals (that is, for intervals specified with explicit YEAR, MONTH,
DAY, ... clauses). Then the storage and enforcement overhead are present
only if you need it. The standard does not allow an unadorned interval
anyway, and does not allow the units mixing that we do. I don't want to
see our extended capabilities go away, but we could add the restricted
"standard type" in parallel.

- Thomas