From: Sebastien FLAESCH <sf@4js.com>
To: pgsql-general@postgresql.org
Subject: Re: INTERVAL SECOND limited to 59 seconds?
Date: Tue, 19 May 2009 11:17:13 +0200
Message-ID: <4A127919.7080105@4js.com> (raw)
In-Reply-To: <4A12737F.1020207@archonet.com>
References: <4A127038.3010103@4js.com>
<4A12737F.1020207@archonet.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: sf@4js.com, pgsql-general@postgresql.org
Subject: Re: INTERVAL SECOND limited to 59 seconds?
In-Reply-To: <4A127919.7080105@4js.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox