On Fri, Jan 13, 2023 at 5:03 PM Bryn Llewellyn <b...@yugabyte.com> wrote:
> ken.tan...@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-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
