Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux