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