Re: Intervals and ISO 8601 duration - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Re: Intervals and ISO 8601 duration |
| Date | |
| Msg-id | ECC4DF67-6FB3-43D2-B9B6-CB41290F68A8@yugabyte.com Whole thread Raw |
| In response to | Re: Intervals and ISO 8601 duration (Ken Tanzer <ken.tanzer@gmail.com>) |
| Responses |
Re: Intervals and ISO 8601 duration
("Martin L. Buchanan" <martinlbuchanan@gmail.com>)
Re: Intervals and ISO 8601 duration (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-general |
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;
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
------------------------+------------------------------+-------------------------
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;
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:
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
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
pgsql-general by date: