Re: INTERVAL SECOND limited to 59 seconds?

Tom Lane wrote:
> Ron Mayer <rm_pg@cheapcomplexdevices.com> 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?