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@xxxxxxxxxxx
