Re: Undocumented AT TIME ZONE INTERVAL syntax

Corey Huinker <corey.huinker@gmail.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:

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.