> On 19 Nov 2022, at 4:58, Ken Tanzer <ken.tan...@gmail.com> wrote: > > On Thu, Nov 17, 2022 at 2:30 PM Alejandro Baeza Rangel > <jlabaezaran...@gmail.com> wrote:
(…) > don't fully understand it. But what really confuses me is the example below. > How can these two intervals be equal and still yield different output in the > to_char function? And as a practical matter, and for the OPs question, how > can you convert from one to the other of these "equal" values? > > 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 > > Cheers, > Ken Those intervals are not identical. I think the reasoning is that due to DST changes, ‘1 day 2 hours’ is more specific than its conversion to ’26 hours’ (or 25 or 27 at DST change). And since you’re not converting the number of days in to_char, that information gets lost. That problem doesn’t seem to arise in the OP’s question (as far as I understand his question), he does have dates to base the intervals on. However, converting the differences in dates to intervals decouples the difference from the dates (the intervals could, for example, subsequently be added to an entirely different date) and he ends up in the same boat. It would seem that the way to do this is to convert the difference to (seconds since) epoch and do the math to convert that to a character string yourself. See for example: https://stackoverflow.com/questions/341384/how-to-convert-an-interval-like-1-day-013000-into-253000 That seems unnecessarily complicated, perhaps there is/could be a more convenient method? I’m sort of thinking of a "relative timestamp offset" type, that tracks an exact difference relative to a given timestamp? Alban Hertroys -- There is always an exception to always.
