Re: Undocumented AT TIME ZONE INTERVAL syntax

On Sun, Sep 19, 2021 at 10:56 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.
>

That text addresses the case of the unadorned string (seconds) and the
overflow
case (more string values than places to put them), but doesn't really
address
the underflow.

>
> 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)

# SELECT INTERVAL '04:02' HOUR TO SECOND;

interval

----------

04:02:00

This result was a bit unexpected, and the existing documentation doesn't
address underflow cases like this.

So, restating all this to get ready to document it, the rule seems to be:

1. Integer strings with no spaces or colons will always apply to the
rightmost end of the restriction given, lack of a restriction means seconds.

Example:

# SELECT INTERVAL '2' HOUR TO SECOND, INTERVAL '2' HOUR TO MINUTE, INTERVAL
'2';
interval | interval | interval
----------+----------+----------
00:00:02 | 00:02:00 | 00:00:02
(1 row)

2. Strings with time context (space separator for days, : for everything
else) will apply starting with the leftmost part of the spec that fits,
continuing to the right until string values are exhausted.

Examples:

# SELECT INTERVAL '4:2' HOUR TO SECOND, INTERVAL '4:2' DAY TO SECOND;
interval | interval
----------+----------
04:02:00 | 04:02:00

(1 row)

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

I think people's eyes are naturally drawn to the example tables, and
because the rules for handling string underflow are subtle, I think a few
concrete examples are the way to go.

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

And with the above I'm now clear that we're fine with the existing behavior
for underflow.

>
> 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.
>

Yeah, I really didn't expect to change the behavior, but wanted to make
sure that the existing behavior was understood. I'll whip up a patch.