INTERVAL SECOND limited to 59 seconds?

From: Sebastien FLAESCH <sf(at)4js(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: mmoncure(at)gmail(dot)com
Subject: INTERVAL SECOND limited to 59 seconds?
Date: 2009-05-19 08:39:20
Message-ID: 4A127038.3010103@4js.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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.

Same question for INTERVAL MINUTE TO SECOND (but here we get an overflow error):

test1=> create table t2 ( k int, i interval minute to second );
CREATE TABLE
test1=> insert into t2 values ( 2, '9999:59' );
ERROR: interval field value out of range: "9999:59"
LINE 1: insert into t2 values ( 2, '9999:59' );
^
test1=> insert into t2 values ( 2, '999:59' );
ERROR: interval field value out of range: "999:59"
LINE 1: insert into t2 values ( 2, '999:59' );
^
test1=> insert into t2 values ( 2, '99:59' );
ERROR: interval field value out of range: "99:59"
LINE 1: insert into t2 values ( 2, '99:59' );
^
test1=> insert into t2 values ( 1, '59:59' );
INSERT 0 1

test1=> insert into t2 values ( 2, '-123:59' );
INSERT 0 1

test1=> select * from t2;
k | i
---+-----------
1 | 00:59:59
2 | -00:59:00
(2 rows)

It's ok when using DAYs:

test1=> create table t3 ( k int, i interval day to second );
CREATE TABLE
test1=> insert into t3 values ( 1, '-9999 18:59:59' );
INSERT 0 1
test1=> insert into t3 values ( 1, '9999999 18:59:59' );
INSERT 0 1
test1=> select * from t3;
k | i
---+-----------------------
1 | -9999 days +18:59:59
1 | 9999999 days 18:59:59
(2 rows)

Thanks a lot!
Seb

Attachment Content-Type Size
libpqtest1.c text/plain 3.8 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scara Maccai 2009-05-19 08:51:26 Re: how the planner decides between bitmap/index scan
Previous Message Scara Maccai 2009-05-19 08:30:32 how the planner decides between bitmap/index scan

Browse pgsql-hackers by date

  From Date Subject
Next Message Richard Huxton 2009-05-19 08:53:19 Re: INTERVAL SECOND limited to 59 seconds?
Previous Message Sebastien FLAESCH 2009-05-19 08:08:37 INTERVAL data type and libpq - what format?