Intrerval oddities

There are certain oddities in current interval behaviour:

template1=# select version();
version
------------------------------------------------------------------
PostgreSQL 7.3devel on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

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).

Then there seem to be some problems with large numbers:

template1=# select '111101.56772 seconds'::interval(6);
interval
----------------------
1 day 06:51:41.56772
(1 row)

template1=# select '1111101.56772 seconds'::interval(6);
interval
-----------------------------
12 days 20:38:21.5677199999
(1 row)

template1=# select '111101.56772 seconds'::interval(2);
interval
-------------------
1 day 06:51:41.57
(1 row)

template1=# select '1111101.56772 seconds'::interval(2);
interval
-----------------------------
12 days 20:38:21.5700000001
(1 row)

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;
interval
----------
00:00
(1 row)

lfix=# select '900 days':: interval year to month;
interval
----------
00:00
(1 row)

and shouldn't this return '50:00' or else give an out of range error?

lfix=# select '50 hours'::interval hour to minute;
interval
----------
02:00
(1 row)

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.)

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)"

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

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"We are troubled on every side, yet not distressed; we
are perplexed, but not in despair; persecuted, but not
forsaken; cast down, but not destroyed; Always bearing
about in the body the dying of the Lord Jesus, that
the life also of Jesus might be made manifest in our
body." II Corinthians 4:8-10