From: | albrecht(dot)dress(at)posteo(dot)de |
---|---|
To: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Inconsistent results in timestamp/interval comparison |
Date: | 2024-03-04 13:06:22 |
Message-ID: | 4faaea681e2e210bbfb7de92fc8cbb35@posteo.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Am 04.03.2024 13:45 schrieb Francisco Olarte:
> Intervals are composed of months, days and seconds, as not every month
> has 30 days and not every day has 86400 seconds, so to compare them
> you have to normalize them somehow, which can lead to bizarre results.
Ah, I see, thanks for the explanation. I had the (apparently wrong)
impression that Postgres _internally_ always uses numerical values (i.e.
the equivalent of EXTRACT(EPOCH …)) for such calculations. My bad…
However, a clarification in the docs might be helpful!
> If you want to do point in time arithmetic, you will be better of by
> extracting epoch from your timestamps and substracting that.
I can confirm that using the query
select now(), t1, extract(epoch from now() - t1) >= extract (epoch from
'2 years'::interval), now() >= (t1 + '2 years'::interval) from testtab;
produces consistent results.
Thanks a lot for your help,
Albrecht.
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2024-03-04 15:04:50 | Re: Inconsistent results in timestamp/interval comparison |
Previous Message | Alban Hertroys | 2024-03-04 13:04:22 | Re: Inconsistent results in timestamp/interval comparison |