Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value - Mailing list pgsql-general

On 3/26/21 2:16 PM, Bryn Llewellyn wrote:
> /Tom Lane wrote:/
> 

> Finally, I discovered that this is OK:
> 
> *create table t(i interval);*
> 
> But I can’t find a definition of the semantics of a bare interval. 
> However, I did find a column headed “Mixed Interval”  at 
> https://www.postgresql.org/docs/11/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE 
> <https://www.postgresql.org/docs/11/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE>. 
> But the example values in the column are consistent with this:
> 
> *select ((interval '2 years, 3 months, 4 days, 5 hours, 6 minutes 7.8 
> seconds')::interval)::text as i;*
> 
> This is the result:
> 
> *2 years 3 mons 4 days 05:06:07.8*
> 
> If you repeat the “select” using the typecast “*::interval month*” then 
> the other components are silently thrown away. But if you repeat it 
> using the typecast “*::interval second*” then all components are 
> preserved just as with bare “*interval*”. This muddies my idea that 
> there were three distinct interval flavors: horological, cultural, and 
> hybrid. Is the behavior that I’ve just shown intended?


https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT

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

So you get:

test(5432)=> select '2 years 3 mons 4 days 05:06:07.8'::interval month;
     interval
----------------
  2 years 3 mons
(1 row)

Equivalent to:

test(5432)=> select '2 years 3 mons 4 days 05:06:07.8'::interval YEAR TO 
MONTH;
     interval
----------------
  2 years 3 mons


test(5432)=> select '2 years 3 mons 4 days 05:06:07.8'::interval second;
              interval
----------------------------------
  2 years 3 mons 4 days 05:06:07.8
(1 row)

Equivalent to:

select '2 years 3 mons 4 days 05:06:07.8'::interval HOUR TO SECOND;
              interval
----------------------------------
  2 years 3 mons 4 days 05:06:07.8
(1 row)

> 
> I can guess the rules for the outcome when such a hybrid is added to a 
> timestamptz value. It’s possible to design edge case tests where you’d 
> get different outcomes if: (a) the cultural component is added first and 
> only then the horological component is added; or (b) the components are 
> added in the other order. It seems to me that the outcome is governed by 
> rule (a). Am I right?
> 
> B.t.w., I think that the specific complexities of the proleptic 
> Gregorian calendar are cleanly separable from the basic idea that 
> (considering only the requirements statement space) there is a real 
> distinction to be drawn between “horological” and “cultural”—no matter 
> what calendar rules might be used.


Looking for logic in dates/times/calendars is a recipe for a continuous 
pounding headache. Not the least because horological = cultural.

-- 
Adrian Klaver
adrian.klaver@aklaver.com