Re: i := t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

On Fri, Mar 26, 2021 at 1:21 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:
> Given these, where the timestamptz values span the start of US/Pacific DST:
> t0 timestamptz := ...;
> t1 timestamptz := ...;
> i_by_subtraction interval second := t1 - t0;
> then this expression tests FALSE:
> t0 + i_by_subtraction i = t1
> There's more. We see that while the two "interval second” values '1 day 01:00:00' and '25 hours' test as equal, the results of adding each to the same timestamptz value are different.

This is many times by dessign. Intervals have, IIRC, three parts,
seconds, days and months to make things like "3 month 2 days 1hour
from now" work as expected in "normal" context ( i.e, in which you
would expect to convert 2021-03-26 16:00:00 cia 2021-06-26 ( 3 month )
and 06-28 ( two days) to 2021-06-28 17:00, disregarding the fact that
months and days are different duration ( in Spain ) while doing it ).

It seems you want to use them as simple seconds count, like "I'm
starting a batch job which will take 25 hours to be done, when will it
end?". Like unix timestamps. If you want that, just avoid interval,
use extract epoch to convert timestamptz to a simple number of
seconds, do arithmetic there (where intervals are just plain numbers )
and convert back using to_timestamp().

It seems you expect a different behaviour from intervals to what they
are dessigned for.

Francisco Olarte.