Ken Tanzer <ken.tan...@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
