From: | Corey Huinker <corey(dot)huinker(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Undocumented AT TIME ZONE INTERVAL syntax |
Date: | 2021-09-19 21:01:49 |
Message-ID: | CADkLM=eaTHe9KOt9ppRPE6bz=+5E+hVRow0srfhh6U7GRxAgHA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2021-09-19 21:04:24 | Re: [PATCH] Add `verify-system` sslmode to use system CA pool for server cert |
Previous Message | Hannu Krosing | 2021-09-19 19:12:37 | Re: WIP: System Versioned Temporal Table |