Thread: Intrerval oddities
There are certain oddities in current interval behaviour:
template1=# select version(); version
------------------------------------------------------------------ PostgreSQL 7.3devel on i686-pc-linux-gnu, compiled
byGCC 2.95.4 (1 row) template1=# select '1200005.567772 seconds'::interval(12); ERROR: INTERVAL(12) precision
mustbe 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
(1row) template1=# select '111101.56772 seconds'::interval(2); interval ------------------- 1
day06: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
'900days':: 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.Elphick@lfix.co.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
thelife also of Jesus might be made manifest in our body." II Corinthians 4:8-10
> 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
> > 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.
> Hey, where is this compile-time option documented? It may have part of the
> functionality I need.
./configure --help
Isn't anywhere else yet.
- Thomas
Thomas, > ./configure --help > > Isn't anywhere else yet. Not seeing it. Is this a 7.3 thing? What does it do? -- -Josh Berkus
> > ./configure --help
> > Isn't anywhere else yet.
> Not seeing it. Is this a 7.3 thing? What does it do?
Sorry, yes it is a 7.3 thing.
- Thomas
Thomas, > > > ./configure --help > > > Isn't anywhere else yet. > > Not seeing it. Is this a 7.3 thing? What does it do? > > Sorry, yes it is a 7.3 thing. What does --enable-interval-integers do? I don't want to bother writing up issues you've already taken care of. -- -Josh Berkus
> > > > ./configure --help
> > > > Isn't anywhere else yet.
> > > Not seeing it. Is this a 7.3 thing? What does it do?
> > Sorry, yes it is a 7.3 thing.
> What does --enable-interval-integers do? I don't want to bother writing up
> issues you've already taken care of.
Not implemented afaik. Or are you asking about
--enable-integer-datetimes ?
That implements timestamps and intervals as 64-bit integers with
microsecond precision. Without it (and for the last few years of
releases), you get a double precision float (52 bits of precision).
- Thomas