On Sun, Sep 19, 2021 at 10:56 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Corey Huinker <corey.huin...@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. > 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.