From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: Sebastien FLAESCH <sf@4js.com>
To: pgsql-general@postgresql.org
To: mmoncure@gmail.com
Subject: Re: INTERVAL SECOND limited to 59 seconds?
Date: Tue, 09 Jun 2009 18:59:27 -0400
Message-ID: <1256.1244588367@sss.pgh.pa.us> (raw)
In-Reply-To: <208.1244587299@sss.pgh.pa.us>
References: <4A127038.3010103@4js.com>
<4A2C0E02.9070908@cheapcomplexdevices.com>
<17203.1244402910@sss.pgh.pa.us>
<4A2DABF5.3070906@cheapcomplexdevices.com>
<208.1244587299@sss.pgh.pa.us>
I wrote:
> I'm inclined to say that these two cases are out of line with what
> the rest of the code does and we should change them.
Specifically, I'm thinking of a patch like this:
inline patch 1: datetime.c (+3/-3 lines) download inline-1.patchIndex: datetime.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.206
diff -c -r1.206 datetime.c
*** datetime.c 1 Jun 2009 16:55:11 -0000 1.206
--- datetime.c 9 Jun 2009 22:47:50 -0000
***************
*** 2917,2933 ****
break;
case INTERVAL_MASK(HOUR):
case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
- case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):- case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
type = DTK_HOUR;
break;
case INTERVAL_MASK(MINUTE):
case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
type = DTK_MINUTE;
break;
case INTERVAL_MASK(SECOND):
- case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
type = DTK_SECOND;
break;
default:
--- 2917,2933 ----
break;
case INTERVAL_MASK(HOUR):
case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
type = DTK_HOUR;
break;
case INTERVAL_MASK(MINUTE):
case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
+ case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
type = DTK_MINUTE;
break;
case INTERVAL_MASK(SECOND):
case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
+ case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):+ case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
type = DTK_SECOND;
break;
default:
Experimenting with this, I confirm that it does these things:
regression=# select '100':: interval day to minute;
interval
----------
01:40:00
(1 row)
regression=# select '100':: interval day to second;
interval
----------
00:01:40
(1 row)
which seem a lot less surprising than the current behavior.
It also causes these changes in the regression tests:
inline patch 2: src/test/regress/results/interval.out (+9/-15 lines) download inline-2.patch*** src/test/regress/expected/interval.out Mon Jun 1 19:29:37 2009
--- src/test/regress/results/interval.out Tue Jun 9 18:49:32 2009
***************
*** 466,476 ****
(1 row)
SELECT interval '1 2' day to minute;
! interval
! ----------------
! 1 day 02:00:00
! (1 row)
!
SELECT interval '1 2:03' day to minute;
interval
----------------
--- 466,474 ----
(1 row)
SELECT interval '1 2' day to minute;
! ERROR: invalid input syntax for type interval: "1 2"
! LINE 1: SELECT interval '1 2' day to minute;
! ^
SELECT interval '1 2:03' day to minute;
interval
----------------
***************
*** 484,494 ****
(1 row)
SELECT interval '1 2' day to second;
! interval
! ----------------
! 1 day 02:00:00
! (1 row)
!
SELECT interval '1 2:03' day to second;
interval
----------------
--- 482,490 ----
(1 row)
SELECT interval '1 2' day to second;
! ERROR: invalid input syntax for type interval: "1 2"
! LINE 1: SELECT interval '1 2' day to second;
! ^
SELECT interval '1 2:03' day to second;
interval
----------------
***************
*** 605,615 ****
(1 row)
SELECT interval '1 2.345' day to second(2);
! interval
! ----------------
! 1 day 02:20:42
! (1 row)
!
SELECT interval '1 2:03' day to second(2);
interval
----------------
--- 601,609 ----
(1 row)
SELECT interval '1 2.345' day to second(2);
! ERROR: invalid input syntax for type interval: "1 2.345"
! LINE 1: SELECT interval '1 2.345' day to second(2);
! ^
SELECT interval '1 2:03' day to second(2);
interval
----------------
Now, all three of these cases throw "invalid input syntax" in 8.3,
so this is not a regression from released behavior. The question
is does anyone think that these syntaxes should be valid? They're
not legal per spec, for sure, and they seem pretty ambiguous to me.
regards, tom lane
Patches:download all (2 files, .tar.gz)
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: tgl@sss.pgh.pa.us, rm_pg@cheapcomplexdevices.com, sf@4js.com, pgsql-general@postgresql.org, mmoncure@gmail.com
Subject: Re: INTERVAL SECOND limited to 59 seconds?
In-Reply-To: <1256.1244588367@sss.pgh.pa.us>
* 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