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

On 3/29/21 3:32 PM, Bryn Llewellyn wrote:
>> 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“interval minute” to second” has the same effect as “interval day to second”. But you can’t even write
“intervalmonth to second” or “interval “year to second”. This suggests that there’s a hard boundary (using those words
ratherloosely) 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”.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
waysto 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
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.
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com