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.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 
      the life also of Jesus might be made manifest in our 
      body."        II Corinthians 4:8-10
22 May 2002 09:23:57 +0100
Oliver Elphick <olly@lfix.co.uk>
Intrerval 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

Wed, 22 May 2002 07:20:15 -0700
Thomas Lockhart <lockhart@fourpalms.org>
Re: Interval oddities
> > 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

Wed, 22 May 2002 17:31:11 -0700
Thomas Lockhart <lockhart@fourpalms.org>
Re: Interval oddities

Thomas,

> ./configure --help
> 
> Isn't anywhere else yet.

Not seeing it.  Is this a 7.3 thing?  What does it do?

-- 
-Josh Berkus



Wed, 22 May 2002 17:51:12 -0700
Josh Berkus <josh@agliodbs.com>
Re: Interval oddities
> > ./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

Wed, 22 May 2002 18:12:25 -0700
Thomas Lockhart <lockhart@fourpalms.org>
Re: Interval oddities
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


Mon, 27 May 2002 14:19:35 -0700
Josh Berkus <josh@agliodbs.com>
Re: Interval oddities
> > > > ./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

Tue, 28 May 2002 15:12:29 -0700
Thomas Lockhart <lockhart@fourpalms.org>
Re: Interval oddities