Thread: 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:

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

Corey Huinker <corey.huinker@gmail.com> writes:
> Attached is an attempt at an explanation of the edge cases I was
> encountering, as well as some examples. If nothing else, the examples will
> draw eyes and searches to the explanations that were already there.

I looked this over and have a few thoughts:

* I don't think your explanation of the behavior of colon-separated
times is quite correct; for example, it doesn't correctly describe
this:

regression=# select INTERVAL '2:03:04' minute to second;
 interval
----------
 02:03:04
(1 row)

I think the actual rule is that hh:mm:ss is always interpreted that
way regardless of the typmod (though we may then drop low-order
fields if the typmod says to).  Two colon-separated numbers are
interpreted as hh:mm by default, but as mm:ss if the typmod is
exactly "minute to second".  (This might work better in a separate
para; the one you've modified here is mostly about what we do with
unmarked quantities, but the use of colons makes these numbers
not unmarked.)

* I'm not sure I would bother with examples for half-broken formats
like "2:".  People who really want to know about that can experiment,
while for the rest of us it seems like adding confusion.

* In the same vein, I think your 0002 example adds way more confusion
than illumination.  Maybe better would be a less contrived offset,
say

# SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00' AT TIME ZONE INTERVAL '3:00:00';
      timezone
---------------------
 2001-02-16 23:38:40
(1 row)

which could be put after the second example and glossed as "The third
example rotates a timestamp specified in UTC to the zone three hours
east of Greenwich, using a constant interval as the time zone
specification".

            regards, tom lane