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

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(dot)klaver(at)aklaver(dot)com