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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
