Re: Undocumented AT TIME ZONE INTERVAL syntax

Corey Huinker <corey(dot)huinker(at)gmail(dot)com> writes:
>> SELECT '2018-03-04' AT TIME ZONE INTERVAL '2' HOUR TO MINUTE;

> ... But none of this is in our own documentation.

That's not entirely true. [1] says

When writing an interval constant with a fields specification, or when
assigning a string to an interval column that was defined with a
fields specification, the interpretation of unmarked quantities
depends on the fields. For example INTERVAL '1' YEAR is read as 1
year, whereas INTERVAL '1' means 1 second. Also, field values “to the
right” of the least significant field allowed by the fields
specification are silently discarded. For example, writing INTERVAL '1
day 2:03:04' HOUR TO MINUTE results in dropping the seconds field, but
not the day field.

But I'd certainly agree that a couple of examples are not a specification.
Looking at DecodeInterval, it looks like the rule is that unmarked or
ambiguous fields are matched to the lowest field mentioned by the typmod
restriction. Thus

regression=# SELECT INTERVAL '4:2' HOUR TO MINUTE;
interval
----------
04:02:00
(1 row)

regression=# SELECT INTERVAL '4:2' MINUTE TO SECOND;
interval
----------
00:04:02
(1 row)

If you wanted to improve this para it'd be cool with me.

> Before I write a patch to add this to the documentation, I'm curious what
> level of sloppiness we should tolerate in the interval calculation. Should
> we enforce the time string to actually conform to the format laid out in
> the X TO Y spec?

We have never thrown away high-order fields:

regression=# SELECT INTERVAL '1 day 4:2' MINUTE TO SECOND;
interval
----------------
1 day 00:04:02
(1 row)

AFAICS we consider that the typmod provides a rounding rule, not a
license to transform the value to something entirely different.

I'm not sure what the SQL spec says here, but I'd be real hesitant to
change the behavior of cases that we've accepted for twenty-plus
years, unless they're just obviously insane. Which these aren't IMO.

regards, tom lane

[1] https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT