Re: INTERVAL SECOND limited to 59 seconds?

Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> 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 <primary datetime field>s in the
precision of the <interval literal>, as specified by <interval
qualifier>.

The <interval literal> 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