Re: INTERVAL SECOND limited to 59 seconds?

Sebastien FLAESCH wrote:
> I think it should be clarified in the documentation...

Please don't top-quote. And yes, I think you're right.

Hmm a quick google for: [sql "interval second"] suggests that it's not
the right thing. I see some mention of 2 digit precision for a leading
field, but no "clipping".

Looking at the manuals and indeed a quick \dT I don't see "interval
second" listed as a separate type though. A bit of exploring in
pg_attribute with a test table suggests it's just using "interval" with
a type modifier. Which you seem to confirm from the docs:

 > The PostgreSQL documentation says:
 >
 > The interval type has an additional option, which is to restrict the set
 > of stored
 > fields by writing one of these phrases:
 >
 >     YEAR
 >     MONTH
...
 > Does that mean that the [field] option of the INTERVAL type is just
 > there to save
 > storage space?

My trusty copy of the 8.3 source suggests that AdjustIntervalForTypmod()
is the function we're interested in and it lives in
backend/utils/adt/timestamp.c - it looks like it just zeroes out the
fields you aren't interested in. No space saving.

So - not a bug, but perhaps not the behaviour you would expect.

> Actually I would like to use this new INTERVAL type to store
> IBM/Informix INTERVALs,
> which can actually be used like this with DATETIME types:
>
>  > create table t1 (
>  >     k int,
>  >     dt1 datetime hour to minute,
>  >     dt2 datetime hour to minute,
>  >     i interval hour(5) to minute );
> Table created.
>
>  > insert into t1 values ( 1, '14:45', '05:10', '-145:10' );
> 1 row(s) inserted.
>
>  > select dt1 - dt2 from t1;
> (expression)
>   9:35                        <- INTERVAL expression

  SELECT ('14:45'::time - '05:10'::time);
  ?column?
----------
  09:35:00
(1 row)


>  > select 15 * ( dt1 - dt2 ) from t1;
> (expression)
>        143:45                        <- INTERVAL expressio

=> SELECT 15 * ('14:45'::time - '05:10'::time);
  ?column?
-----------
  143:45:00
(1 row)

If you can live with the zero seconds appearing, it should all just
work*. Other than formatting as text, I don't know of a way to suppress
them though.

* Depending on whether you need to round up if you ever get odd seconds etc.

--
   Richard Huxton
   Archonet Ltd