From: Bryn Llewellyn <bryn@yugabyte.com>
To: Ken Tanzer <ken.tanzer@gmail.com>
Cc: Tom Lane PostgreSQL <tgl@sss.pgh.pa.us>
Cc: david.g.johnston@gmail.com, Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: Intervals and ISO 8601 duration
Date: Fri, 13 Jan 2023 16:03:14 -0800
Message-ID: <ECC4DF67-6FB3-43D2-B9B6-CB41290F68A8@yugabyte.com> (raw)
In-Reply-To: <CAD3a31UP79wrZZTRFtP4sPvgOy7TZg2v6FmPzXYdAAnaOdnuiQ@mail.gmail.com>
References: <AM9P191MB1286AD77B5FBAF4640E7274AB0FD9@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM>
<953416.1673536094@sss.pgh.pa.us>
<CAD3a31XW8iqrZ+uB7if+YUgRjk_=9YQOJRJVBc5__yGRsgypQA@mail.gmail.com>
<53c0b4d4-b80a-a35b-ce18-61058794c9bb@aklaver.com>
<CAD3a31UP79wrZZTRFtP4sPvgOy7TZg2v6FmPzXYdAAnaOdnuiQ@mail.gmail.com>
> ken.tanzer@gmail.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-o...
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/ty...
The user-defined "strict equals" interval-interval "==“ operator
https://docs.yugabyte.com/preview/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/ty...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: bryn@yugabyte.com, ken.tanzer@gmail.com, tgl@sss.pgh.pa.us, adrian.klaver@aklaver.com, sebastien.flaesch@4js.com
Subject: Re: Intervals and ISO 8601 duration
In-Reply-To: <ECC4DF67-6FB3-43D2-B9B6-CB41290F68A8@yugabyte.com>
References: <AM9P191MB1286AD77B5FBAF4640E7274AB0FD9@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM> <953416.1673536094@sss.pgh.pa.us> <CAD3a31XW8iqrZ+uB7if+YUgRjk_=9YQOJRJVBc5__yGRsgypQA@mail.gmail.com> <53c0b4d4-b80a-a35b-ce18-61058794c9bb@aklaver.com> <CAD3a31UP79wrZZTRFtP4sPvgOy7TZg2v6FmPzXYdAAnaOdnuiQ@mail.gmail.com> <ECC4DF67-6FB3-43D2-B9B6-CB41290F68A8@yugabyte.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox