ken.tanzer@xxxxxxxxx wrote: 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: 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 |
