Thread: Duda sobre como imprimir un campo INTERVAL

> 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.

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Thanks.  I could understand that they're not identical.   But then what's
> going on where Postgres evaluates them as equal?  (i1=i2 above.)  Are the
> two intervals getting cast or converted to something else before they are
> compared, with whatever makes them non-identical getting lost in the
> conversion?

Hmm ... the code is pretty clear about what it thinks it's doing,
but I'm not sure if this info exists anywhere in the user-facing docs:

 * Interval comparison is based on converting interval values to a linear
 * representation expressed in the units of the time field (microseconds,
 * in the case of integer timestamps) with days assumed to be always 24 hours
 * and months assumed to be always 30 days.

So once '1 day 2 hours' and '26 hours' are flattened into this linear
representation, they are indeed "equal".  There's precedent elsewhere
for values that are "equal" but not identical, so it's not a bug,
even if it's not the semantics you'd want for some particular use-case.

I think the main factor behind having done it this way is that we need
a linear sort order if we want to support btree indexes or ORDER BY
on intervals.

You can use justify_hours() to get from '26 hours' to '1 day 2 hours'.
I'm not sure if there's a compact way to go the other direction,
though you could always use extract() to get the components and
sum them up.

            regards, tom lane