Re: Undocumented AT TIME ZONE INTERVAL syntax - Mailing list pgsql-hackers
| From | Corey Huinker |
|---|---|
| Subject | Re: Undocumented AT TIME ZONE INTERVAL syntax |
| Date | |
| Msg-id | CADkLM=eaTHe9KOt9ppRPE6bz=+5E+hVRow0srfhh6U7GRxAgHA@mail.gmail.com Whole thread Raw |
| In response to | Re: Undocumented AT TIME ZONE INTERVAL syntax (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Undocumented AT TIME ZONE INTERVAL syntax
|
| List | pgsql-hackers |
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)
interval
----------
04:02:00
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.
> 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.
pgsql-hackers by date: