Re: Intervals and ISO 8601 duration

On Fri, Jan 13, 2023 at 5:03 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> ken(dot)tanzer(at)gmail(dot)com wrote:
>
> Here's an example. Note that they come out formatted differently with
> to_char, but evaluate as equal. The explanation(1) was that they were
> Equal but not Identical. I was thinking getting the raw data about how they
> are stored would get at the identicality issue:
>
> WITH inters AS (
> SELECT
> '1 day 2 hours'::interval AS i1,
> '26 hours'::interval AS i2
> )
> SELECT
> *,
> to_char(i1,'HH24:MM:SS') AS i1_char,
> to_char(i2,'HH24:MM:SS') AS i2_char,
> i1=i2 AS "Equal?"
> FROM inters;
>
> i1 | i2 | i1_char | i2_char | Equal?
> ----------------+----------+----------+----------+--------
> 1 day 02:00:00 | 26:00:00 | 02:00:00 | 26:00:00 | t
>
>
> I struggled to understand this whole murky area when I was writing the
> “Date and time data types and functionality” section for the YugabyteDB
> doc. (YugabyteDB uses the Postgres SQL processing code “as is” on top of
> its own distributed storage layer. All the examples in my doc work
> identically in vanilla PG.)
>
> The implied question here is this: is the interval “1 day 2 hours” the
> same as the interval “26 hours”? It might seem that the answer is “yes”—as
> it surely must be. But, sorry to say, that the answer is actually “no”.
> Confused? You will be. Most people are until they’ve wrapped their head in
> a towel and puzzled it through for a few days. This shows you what I mean:
>
> set timezone = 'America/Los_Angeles';
> with c as (
> select '2023-03-11 20:00 America/Los_Angeles'::timestamptz as
> original_appointment)
> select
> original_appointment::text as "original appointment",
> (original_appointment + '1 day 2 hours'::interval)::text as "postponed
> by '1_day 2 hours'",
> (original_appointment + '26 hours'::interval)::text as "postponed by
> '24_hours'"
> from c;
>
> This is the result:
>
> original appointment | postponed by '1_day 2 hours' | postponed by
> '24_hours'
>
> ------------------------+------------------------------+-------------------------
> 2023-03-11 20:00:00-08 | 2023-03-12 22:00:00-07 | 2023-03-12
> 23:00:00-07
>
> Two different answers! The “trick” here is that the time of the original
> appointment and the postponed times straddle the 2023 “spring forward”
> moment (at least as it happens in the America/Los_Angeles timezone). And
> the resolution of what at first might seem to be a bug come when you
> realized that you must make a distinction between clock time and calendar
> time.
>
> This query sheds a bit more light on the matter:
>
>
>
>
>
>
>
>
> *with c(i1, i2) as ( select '1 day 2 hours'::interval, '26
> hours'::interval)select interval_mm_dd_ss(i1)::text as i1,
> interval_mm_dd_ss(i2)::text as i2, (i1 = i2)::text as "i1 = i2",
> (i1==i2)::text as "i1 == i2"from c;*
>
> I defined the “interval_mm_dd_ss()” function and the “==” operator. (I
> called it the “strict equality operator for interval values”.)
>
> I believe that your question implies that you want
> my “interval_mm_dd_ss()” function. I can’t be sure what you want. But I
> dare to speculate that you might find it helpful to read (at least) the
> references that I’ve listed below. Start with the informal treatment in my
> blog post.
>
> Tom, Adrian, and David might remember my endless questions in this general
> space in March 2021. This, from Tom, answers the present question:
>
>
> https://www.postgresql.org/message-id/DAABCBE6-6DFF-4C28-9CED-0AA8053FE12C%40yugabyte.com
>
>
> p.s. Some other posts came in while I was writing this. My advice on
> “justify_interval()” is to avoid it.
> ____________________________________________________________
>
> PostgreSQL Timestamps and Timezones: How to Navigate the Interval Minefield
>
> https://www.yugabyte.com/blog/postgresql-timestamps-timezones-interval-minefield/
>
> Two ways of conceiving of time: calendar-time and clock-time
>
> https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/conceptual-background/#two-ways-of-conceiving-of-time-calendar-time-and-clock-time
>
> type interval_mm_dd_ss_t as (mm, dd, ss)
>
> https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#type-interval-mm-dd-ss-t-as-mm-dd-ss
>
> The user-defined "strict equals" interval-interval "==“ operator
>
> https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#the-user-defined-strict-equals-interval-interval-operator
>

*****

Just tried casting interval to bytea to see the binary layout, but that
direct cast is not allowed.

Sincerely,

Martin L Buchanan
postgreSQL database developer (for about 2.5 years now)
(and not knowledgeable about administering PG or the internals of PG)
Laramie, WY, USA