Re: INTERVAL SECOND limited to 59 seconds?

I think it should be clarified in the documentation...

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
1 row(s) retrieved.

 > select 15 * ( dt1 - dt2 ) from t1;
(expression)
        143:45                        <- INTERVAL expression
1 row(s) retrieved.



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
     DAY
     HOUR
     MINUTE
     SECOND
     YEAR TO MONTH
     DAY TO HOUR
     DAY TO MINUTE
     DAY TO SECOND
     HOUR TO MINUTE
     MINUTE TO SECOND

Does that mean that the [field] option of the INTERVAL type is just there to save
storage space?

Confusing...

Seb

Richard Huxton wrote:
> Sebastien FLAESCH wrote:
>> Hello,
>>
>> Can someone explain this:
>>
>> test1=> create table t1 ( k int, i interval second );
>> CREATE TABLE
>> test1=> insert into t1 values ( 1, '-67 seconds' );
>> INSERT 0 1
>> test1=> insert into t1 values ( 2, '999 seconds' );
>> INSERT 0 1
>> test1=> select * from t1;
>>  k |     i
>> ---+-----------
>>  1 | -00:00:07
>>  2 | 00:00:39
>> (2 rows)
>>
>> I would expect that an INTERVAL SECOND can store more that 59 seconds.
>
> I didn't even know we had an "interval second" type. It's not entirely
> clear to me what such a value means. Anyway - what's happening is that
> it's going through "interval" first. So - '180 seconds' will yield
> '00:03:00' and the seconds part of that is zero.
>
> The question I suppose is whether that's correct or not. An interval can
> clearly store periods longer than 59 seconds. It's reasonable to ask for
> an interval to be displayed as "61 seconds". If "interval second" means
> the seconds-only part of an interval though, then it's doing the right
> thing.
>