On 3/29/21 3:32 PM, Bryn Llewellyn wrote:
On 27-Mar-2021, at 09:16, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx
