On Fri, Mar 26, 2021 at 1:21 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> 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.
