On 3/29/21 3:32 PM, Bryn Llewellyn wrote:
On 27-Mar-2021, at 09:16, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
…
Re Adrian’s quote of the doc thus:
Also, field values “to the right” of the least significant field allowed by the
fields specification are silently discarded.
Yes, I do take this point. But there’s some murkiness here. All of my tests
confirm that, for example, the declaration “interval minute” to second” has the
same effect as “interval day to second”. But you can’t even write “interval
month to second” or “interval “year to second”. This suggests that there’s a
hard boundary (using those words rather loosely) between “months” and
“days”. This is consistent with this statement from the SQL Standard (1992
edition):
« There are two classes of intervals. One class, called year-month intervals,
has an express or implied datetime precision that includes no fields other than
YEAR and MONTH, though not both are required. The other class, called day-time
intervals, has an express or implied interval precision that can include any
fields other than YEAR or MONTH. »
Oracle Database honors this by allowing only two spellings of interval
declaration “year to month” and “day to second”. But the fact that PostgreSQL
allows a bare “interval” declaration that allows values of all six fields
(years, months, days, hours, minutes, and seconds) is at odds with this. (In
fact, it allows no fewer than _fourteen_ distinct ways to declare an
interval—but these condense into only seven semantically distinct declarations.
https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
"Also, a combination of years and months can be specified with a dash;
for example '200-10' is read the same as '200 years 10 months'. (These
shorter forms are in fact the only ones allowed by the SQL standard, and
are used for output when IntervalStyle is set to sql_standard.)"
My stock suggestion to anyway going over datatype-datetime.html and
associated pages is to read them several times, take a break, then read
them again several times. Then assume you still have not nailed down the
if, and/or's, and buts and be prepared to go over them again.
Red Adrian’s comment:
Looking for logic in dates/times/calendars is a recipe for a continuous
pounding headache. Not the least because horological = cultural.
I’m looking for a sufficient, and clear, way to describe the rules of what the
PostgreSQL implementation actually does. And I want to believe that at least
some logical thinking informed the design. I rehearsed my argument that a
genuine, and useful, distinction can be made between the two terms of art in my
reply to Francisco Olarte. I wrote:
Again if you are looking for logic you are in the wrong place. If you
really want to know what is going on then I suggest taking a look at the
source, in particular:
src/backend/utils/adt/datetime.c
Bring aspirin and/or a stiff glass of something medicinal.
« My claim is that there’s a genuine distinction to be drawn in the conceptual
domain—and that this can be made independently of any particular computer
system. I might say that “1 day” is simply the same thing as “24 hours”, in
the same way that “1 foot” is the same as “12 inches”. But my discussion
partner might argue with this saying that the length of one day is sometimes 23
hours and sometimes 25 hours due to the much-rehearsed arguments about DST.
Here, I’d be thinking horologically and my discussion partner would be thinking
culturally. Those two terms of art (or other equivalent ones) are useful to
stop a fist fight breaking out by allowing each discussion partner to
understand, and label, the other’s mental model—both of which have merit.
Notice that the same argument could be had about the equivalence of “1 minute”
and “60 seconds” in the light of the “leap second” phenomenon. It just happens
that when we get to PostgreSQL, its Proleptic Gregorian Calendar implementation
knows nothing of leap seconds. At least, this is how I interpret “because leap
seconds are not handled” on the
https://www.postgresql.org/docs/11/functions-datetime.html page. »
Here’s an example where (as I believe) I can use these terms to advantage:
When you subtract two timeztamptz values which are greater apart than 24 hours,
the “days”, “hours”, “minutes”, and “seconds” fields of the resulting interval
are populated using horological semantics. But when you add an interval value
to a timeztamptz value, the value of the “days” field is added using cultural
semantics but the value of the “hours” field is added using horological
semantics.
This is possibly what lies behind the design choice that the “days” and “hours”
values are explicitly separately represented.
--
Adrian Klaver
adrian.kla...@aklaver.com
