Message-ID: <4A127919.7080105@4js.com>
Date: Tue, 19 May 2009 11:17:13 +0200
From: Sebastien FLAESCH <sf@4js.com>
Organization: Four J's Development Tools
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: Re: INTERVAL SECOND limited to 59 seconds?
References: <4A127038.3010103@4js.com> <4A12737F.1020207@archonet.com>
In-Reply-To: <4A12737F.1020207@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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