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

> On 27-Mar-2021, at 09:16, Adrian Klaver <adrian.klaver@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
“intervalminute” to second” has the same effect as “interval day to second”. But you can’t even write “interval month
tosecond” 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
precisionthat includes no fields other than YEAR and MONTH, though not both are required. The other class, called
day-timeintervals, 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”.
Butthe 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
declarean interval—but these condense into only seven semantically distinct declarations. 

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.
AndI want to believe that at least some logical thinking informed the design. I rehearsed my argument that a genuine,
anduseful, distinction can be made between the two terms of art in my reply to Francisco Olarte. I wrote: 

« My claim is that there’s a genuine distinction to be drawn in the conceptual domain—and that this can be made
independentlyof any particular computer system. I might say that “1 day” is simply the same thing as  “24 hours”, in
thesame way that “1 foot” is the same as “12 inches”. But my discussion partner might argue with this saying that the
lengthof one day is sometimes 23 hours and sometimes 25 hours due to the much-rehearsed arguments about DST. Here, I’d
bethinking horologically and my discussion partner would be thinking culturally. Those two terms of art (or other
equivalentones) 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
“leapsecond” phenomenon. It just happens that when we get to PostgreSQL, its Proleptic Gregorian Calendar
implementationknows nothing of leap seconds. At least, this is how I interpret “because leap seconds are not handled”
onthe 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
toa timeztamptz value, the value of the “days” field is added using cultural semantics but the value of the “hours”
fieldis added using horological semantics. 

This is possibly what lies behind the design choice that the “days” and “hours” values are explicitly separately
represented.