Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Tom Lane wrote: > Ron Mayer writes: >> regression=# select interval '1 1' hour; > > Hmm, not sure about that one. We decided a week or two back that we > don't want the thing discarding higher-order field values, and this > seems pretty close to that. As the code is set up (plus my patch) > I think it's the case that only the rightmost field specification > of the interval qualifier makes any difference for parsing the value; > the leftmost field doesn't really affect what we think the constant > means. That seems like a nice simple consistency property ... Sounds good. I'm happy with it then. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Ron Mayer writes: > It still accepts one odd input that 8.3 rejected: > regression=# select interval '1 1' hour; > Perhaps the additional patch below fixes that? Hmm, not sure about that one. We decided a week or two back that we don't want the thing discarding higher-order field values, and this seems pretty close to that. As the code is set up (plus my patch) I think it's the case that only the rightmost field specification of the interval qualifier makes any difference for parsing the value; the leftmost field doesn't really affect what we think the constant means. That seems like a nice simple consistency property ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Tom Lane wrote: > 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. > ... > 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. Seems to do a sane thing for all sane inputs I threw at it. It still accepts one odd input that 8.3 rejected: regression=# select interval '1 1' hour; Perhaps the additional patch below fixes that? *** *** 3022,3028 DecodeInterval(char **field, int *ftype, int nf, int range, tm->tm_hour += val; AdjustFractSeconds(fval, tm, fsec, SECS_PER_HOUR); tmask = DTK_M(HOUR); ! type = DTK_DAY; /* set for next field */ break; case DTK_DAY: --- 3022,3029 tm->tm_hour += val; AdjustFractSeconds(fval, tm, fsec, SECS_PER_HOUR); tmask = DTK_M(HOUR); ! if (range == (INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR))) ! type = DTK_DAY; /* set for next field */ break; case DTK_DAY: It also gives different answers than 8.3 for "select interval '1 1:' hour" but I guess that's intended, right? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
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: Index: 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 - 1.206 --- datetime.c 9 Jun 2009 22:47:50 - *** *** 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: *** 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)
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Ron Mayer writes: > Tom Lane wrote: >> That is a bit odd, especially seeing that eg. '1' hour to second >> comes out as 1 second. What's making it do that? > Code-wise, it seems because around line 2906 in DecodeInterval: > switch (range) ... > case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | > INTERVAL_MASK(SECOND): > type=DTK_HOUR; > But if I naively change that by making it DTK_SECOND, > I'd break "select interval '1 2' day to second;". I guess I'd need > to tweak it to say: if it follows a days filed it means hours; but > by itself it means seconds? Well, remember that that code is dealing with an unlabeled rightmost field. In all the cases except DAY TO MINUTE and DAY TO SECOND, the choice is to assume that such a field corresponds to the rightmost field of the declared interval type. So the question is do we want the current behavior, or do we want to rearrange the switch() so that these two cases assume MINUTE and SECOND respectively? It's certainly a trivial code change, but it's not clear what's the right thing. The cases of interest seem to be outside the spec, so it's not much help. It says that 22) Let N be the number of s in the precision of the , as specified by . The being defined shall contain N datetime components. and the syntaxes for multi-component literals are mostly not very ambiguous --- the rightmost component is generally supposed to have punctuation that disambiguates what it is. 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. It's a judgement call, but I can't offhand see a case where the current behavior wouldn't seem surprising. Plan C would be to say that these cases are ambiguous and we should throw error. I'm not too thrilled with that, though, especially since we didn't throw error in prior versions. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Tom Lane wrote: > Ron Mayer writes: >> Looks like the original questions from the thread >> got resolved, but I found this behaviour surprising: > >> regression=# select interval '1' day to second; >> interval >> -- >> @ 1 hour >> (1 row) > >> Should this be 1 second? > > That is a bit odd, especially seeing that eg. '1' hour to second > comes out as 1 second. What's making it do that? What from a design point of view? Seems like it's a side effect of the logic that makes: select interval '1 2'; know that the 2 means hours rather than seconds. Code-wise, it seems because around line 2906 in DecodeInterval: switch (range) ... case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND): type=DTK_HOUR; But if I naively change that by making it DTK_SECOND, I'd break "select interval '1 2' day to second;". I guess I'd need to tweak it to say: if it follows a days filed it means hours; but by itself it means seconds? There's a bit of other odd stuff around there. It seems CVS head accepts "select interval '1 2' hour;" but not "select interval '1 2' hour to minute;" regression=# select interval '1 2' hour; interval 1 day 02:00:00 (1 row) and I would have guessed that either both should succeed or both should fail. And if both succeed I wouldn't have expected 1 day 2 hours.. I'd still be happy to send a patch, but am still trying to figure out what the desired behavior is. My current impression: What's the desired behavior for each of these: select interval '1' day to second; --- should it be 1 second to be consistent with "select interval 1;"? --- or an error as Sebastien argued in a different part of the thread? select interval '1 2' hour; --- should be an error as "select interval '1 2' hour to minute" is? --- should be "1 day 2 hours" as cvs head treats "select interval '1 day 2 hours' hour to minute;"? --- should be 2 hours? select interval '1 2' hour to minute; --- should be an error as "select interval '1 2' hour to minute" is? --- should be "1 day 2 hours" as cvs head treats "select interval '1 day 2 hours' hour to minute;"? --- should be 2 hours? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
This is of course unexpected to me (one day becomes an hour)... Actually I would even expect an error, because there are missing interval parts. To represent a valid day to second interval, you should write '1 00:00:00' ... '1' would be a valid day to day interval. Always providing all interval units would clarify the user code (avoid complex rules to get defaults), IMHO. Just to compare with IFX interval literals: == > select interval( 1, day to second ) from systables where tabid=1; 201: A syntax error has occurred. Error in line 1 Near character position 37 > select interval( 1 ) day to second from systables where tabid=1; 1262: Non-numeric character in datetime or interval. Error in line 1 Near character position 36 > select interval ( 1 11:22:33 ) day to second from systables where tabid=1; (constant) 1 11:22:33 1 row(s) retrieved. > select interval ( 1 ) day to day from systables where tabid=1; (constant) 1 1 row(s) retrieved. == Seb Ron Mayer wrote: Finally got around to looking at this thread. Looks like the original questions from the thread got resolved, but I found this behaviour surprising: regression=# select interval '1' day to second; interval -- @ 1 hour (1 row) Should this be 1 second? If so I can send a patch. regression=# select version(); version --- PostgreSQL 8.4beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3), 32-bit (1 row) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Ron Mayer writes: > Looks like the original questions from the thread > got resolved, but I found this behaviour surprising: > regression=# select interval '1' day to second; > interval > -- > @ 1 hour > (1 row) > Should this be 1 second? That is a bit odd, especially seeing that eg. '1' hour to second comes out as 1 second. What's making it do that? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Finally got around to looking at this thread. Looks like the original questions from the thread got resolved, but I found this behaviour surprising: regression=# select interval '1' day to second; interval -- @ 1 hour (1 row) Should this be 1 second? If so I can send a patch. regression=# select version(); version --- PostgreSQL 8.4beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3), 32-bit (1 row) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Thank you Tom for looking at this. I would be pleased to help on testing the fix when available. My plan is to store Informix INTERVALs (coming from the 4gl applications we support) into PostgreSQL INTERVALs, and I have a bunch of tests for that... I believe Informix INTERVALs (and related operators and functions) are not 100% SQL99, but they are close... Thanks a lot! Seb Tom Lane wrote: Sebastien FLAESCH writes: I would expect that an INTERVAL SECOND can store more that 59 seconds. I took a look into the SQL spec and I think that we do indeed have a spec compliance issue here. SQL99 section 4.7 saith Within a value of type interval, the first field is constrained only by the of the associated . Table 8, "Valid values for fields in INTERVAL values", specifies the constraints on subsequent field values. [ Table 8 says about what you'd expect, eg 0..23 for HOUR ] Values in interval fields other than SECOND are integers and have precision 2 when not the first field. SECOND, however, can be defined to have an that indicates the number of decimal digits maintained following the decimal point in the seconds value. When not the first field, SECOND has a precision of 2 places before the decimal point. So in other words, "999 seconds" is a valid value for a field of type INTERVAL SECOND, *and should come out the same way*, not as "00:16:39", and certainly not as "00:00:39". It might be a relatively easy fix to not truncate the input value incorrectly. I haven't looked, but I think we should look now, because 8.4 has already changed the behavior in this area and it would be good not to change it twice. The focus of the 8.4 work was to make sure that we would correctly interpret the values of spec-compliant interval literals, but this example shows we are not there yet. We are fairly far away from being able to make it print out as the spec would suggest, because interval_out simply doesn't have access to the information that the field is constrained to be INTERVAL SECOND rather than some other kind of interval. We also have got no concept at all of , only of , so constraining the leading field to only a certain number of integral digits isn't possible either. I don't foresee anything getting done about either of those points for 8.4. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Sebastien FLAESCH writes: > I would expect that an INTERVAL SECOND can store more that 59 seconds. I took a look into the SQL spec and I think that we do indeed have a spec compliance issue here. SQL99 section 4.7 saith Within a value of type interval, the first field is constrained only by the of the associated . Table 8, "Valid values for fields in INTERVAL values", specifies the constraints on subsequent field values. [ Table 8 says about what you'd expect, eg 0..23 for HOUR ] Values in interval fields other than SECOND are integers and have precision 2 when not the first field. SECOND, however, can be defined to have an that indicates the number of decimal digits maintained following the decimal point in the seconds value. When not the first field, SECOND has a precision of 2 places before the decimal point. So in other words, "999 seconds" is a valid value for a field of type INTERVAL SECOND, *and should come out the same way*, not as "00:16:39", and certainly not as "00:00:39". It might be a relatively easy fix to not truncate the input value incorrectly. I haven't looked, but I think we should look now, because 8.4 has already changed the behavior in this area and it would be good not to change it twice. The focus of the 8.4 work was to make sure that we would correctly interpret the values of spec-compliant interval literals, but this example shows we are not there yet. We are fairly far away from being able to make it print out as the spec would suggest, because interval_out simply doesn't have access to the information that the field is constrained to be INTERVAL SECOND rather than some other kind of interval. We also have got no concept at all of , only of , so constraining the leading field to only a certain number of integral digits isn't possible either. I don't foresee anything getting done about either of those points for 8.4. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Sebastien FLAESCH wrote: Actually it's not limited to the usage of INTERVAL SECOND, I am writing a PostgreSQL driver for our 4GL virtual machine... I need to store all possible Informix INTERVAL types such as: INTERVAL MONTH(8) TO MONTH INTERVAL DAY(8) TO MINUTE INTERVAL SECOND TO FRACTION(5) In Postgres, you should just store it as an INTERVAL which (unlike some other RDBMS') has the ability to store ranges from fractional seconds to thousands of years. Then if you need to output it in the above format, make a view that splits the actual interval into month, minute and fractional second pieces. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Sebastien FLAESCH wrote: Actually it's not limited to the usage of INTERVAL SECOND, I am writing a PostgreSQL driver for our 4GL virtual machine... I need to store all possible Informix INTERVAL types such as: INTERVAL MONTH(8) TO MONTH INTERVAL DAY(8) TO MINUTE INTERVAL SECOND TO FRACTION(5) ... etc ... ... If PostgreSQL is not able to store months > 11, hours > 23 and minutes or seconds > 59 Well, it's not storage it's formatting. Doesn't make any difference to your problem though. >, it looks like I will need to deal with PostgreSQL's INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND(5) ... and make conversions, to store all possible Informix INTERVALs... If you know a little "C" you could build some custom types to match your needs. It should just be a matter of applying the correct formatting as a wrapper around the existing "interval" type. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Actually it's not limited to the usage of INTERVAL SECOND, I am writing a PostgreSQL driver for our 4GL virtual machine... I need to store all possible Informix INTERVAL types such as: INTERVAL MONTH(8) TO MONTH INTERVAL DAY(8) TO MINUTE INTERVAL SECOND TO FRACTION(5) ... etc ... ... If PostgreSQL is not able to store months > 11, hours > 23 and minutes or seconds > 59, it looks like I will need to deal with PostgreSQL's INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND(5) ... and make conversions, to store all possible Informix INTERVALs... Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Sebastien FLAESCH wrote:
I think it should be clarified in the documentation...
Please don't top-quote. And yes, I think you're right.
Hmm a quick google for: [sql "interval second"] suggests that it's not
the right thing. I see some mention of 2 digit precision for a leading
field, but no "clipping".
Looking at the manuals and indeed a quick \dT I don't see "interval
second" listed as a separate type though. A bit of exploring in
pg_attribute with a test table suggests it's just using "interval" with
a type modifier. Which you seem to confirm from the docs:
> 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
...
> Does that mean that the [field] option of the INTERVAL type is just
> there to save
> storage space?
My trusty copy of the 8.3 source suggests that AdjustIntervalForTypmod()
is the function we're interested in and it lives in
backend/utils/adt/timestamp.c - it looks like it just zeroes out the
fields you aren't interested in. No space saving.
So - not a bug, but perhaps not the behaviour you would expect.
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
SELECT ('14:45'::time - '05:10'::time);
?column?
--
09:35:00
(1 row)
> select 15 * ( dt1 - dt2 ) from t1;
(expression)
143:45<- INTERVAL expressio
=> SELECT 15 * ('14:45'::time - '05:10'::time);
?column?
---
143:45:00
(1 row)
If you can live with the zero seconds appearing, it should all just
work*. Other than formatting as text, I don't know of a way to suppress
them though.
* Depending on whether you need to round up if you ever get odd seconds etc.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
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. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INTERVAL SECOND limited to 59 seconds?
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, ':59' );
ERROR: interval field value out of range: ":59"
LINE 1: insert into t2 values ( 2, ':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, '- 18:59:59' );
INSERT 0 1
test1=> insert into t3 values ( 1, '999 18:59:59' );
INSERT 0 1
test1=> select * from t3;
k | i
---+---
1 | - days +18:59:59
1 | 999 days 18:59:59
(2 rows)
Thanks a lot!
Seb
/*
Version:8.4.beta1
Created by: s...@4js.com
Problem with INTERVAL input format
--
After executing this program, 2 rows are present in the table.
Only the first has the expected values...
Why does the second insert fail to insert "123 11" in INTERVAL DAY TO HOUR?
Diagnostic info:
SQL State: 22007
Message : invalid input syntax for type interval: " 123 11"
Why does the third row show "00:00:00" in first INTERVAL YEAR column?
[...@fox problems]$ psql test1 -U pgsuser
psql (8.4beta1)
Type "help" for help.
test1=> select * from t1;
k | i1 |i2
---+--+---
1 | -12345 years | 123 days 11:00:00
3 | 00:00:00 | 123 days 11:00:00
(2 rows)
When inserting rows with psql, the format used by the C program are supported:
test1=> insert into t1 values ( 4, '-12345', '123 11' );
INSERT 0 1
test1=> select * from t1 where k=4;
k | i1 |i2
---+--+---
4 | -12345 years | 123 days 11:00:00
(1 row)
So what am I doing wrong here?
*/
#include
#include
static int checkResult(PGresult * r)
{
if (r == NULL)
return 0;
switch (PQresultStatus(r)) {
case PGRES_COMMAND_OK:
case PGRES_TUPLES_OK:
return 1;
default:
return 0;
}
}
static void getErrorInfo(PGresult * r)
{
if (r == NULL)
return;
fprintf(stderr, "Diagnostic info:\n");
fprintf(stderr, " SQL State: %s\n", PQresultErrorField(r,
PG_DIAG_SQLSTATE));
fprintf(stderr, " Message : %s\n", PQresultErrorField(r,
PG_DIAG_MESSAGE_PRIMARY));
}
int main(int argc, char **argv)
{
PGresult *r;
PGconn *c;
Oid paramTypes[10];
const char *paramValues[10];
fprintf(stdout,"++ Connecting...\n");
c = PQconnectdb("dbname='test1' user='pgsuser' password='fourjs'");
if (c == NULL) {
fprintf(stderr,">> Could not connect.\n");
exit(1);
}
fprintf(stdout,"++ Creating table t1 ...\n");
r = PQexec(c, "DROP TABLE t1");
PQclear(r);
r = PQexec(c, "CREATE TABLE t1 ( k INT, i1 INTERVAL YEAR, i2 INTERVAL DAY
TO HOUR)");
if (!checkResult(r)) {
fprintf(stderr,">> Could not create table 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
fprintf(stdout,"++ Preparing INSERT ...\n");
paramTypes[0] = 23; /* INT4 */
paramTypes[1] = 1186; /* INTERVAL */
paramTypes[2] = 1186; /* INTERVAL */
r = PQprepare(c, "s1",
"INSERT INTO t1 VALUES ( $1, $2, $3 )",
3, (const Oid *) paramTypes);
if (!checkResult(r)) {
fprintf(stderr,">> Could not prepare stmt 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is working */
fprintf(stdout,"++ Executing INSERT (1) ...\n");
paramValues[0] = "1";
paramValues[1] = "-12345 years";
paramValues[2] = " 123 11:00";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,"++ Executing INSERT (2) ...\n");
paramValues[0] = "2";
paramValues[1] = "-12345";
paramValues[2] = " 12
