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.
>