> On 19 Nov 2022, at 4:58, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>
> On Thu, Nov 17, 2022 at 2:30 PM Alejandro Baeza Rangel <jlabaezarangel@gmail.com> wrote:
(…)
> don't fully understand it. But what really confuses me is the example below. How can these two intervals be equal
andstill yield different output in the to_char function? And as a practical matter, and for the OPs question, how can
youconvert 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
thanits 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
basethe intervals on. However, converting the differences in dates to intervals decouples the difference from the dates
(theintervals 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
thatto 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
"relativetimestamp offset" type, that tracks an exact difference relative to a given timestamp?
Alban Hertroys
--
There is always an exception to always.