From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | albrecht(dot)dress(at)posteo(dot)de |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Inconsistent results in timestamp/interval comparison |
Date: | 2024-03-04 12:45:48 |
Message-ID: | CA+bJJbwvj1_e+2fVPf-kD4Q1pfsMqYUP47++p2OzVGmVx_zy0Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 4 Mar 2024 at 13:10, <albrecht(dot)dress(at)posteo(dot)de> wrote:
> According to the documentation, Table 9.31, IMHO both comparisons should
> produce the same results, as
> timestamp - timestamp → interval
> timestamp + interval → timestamp
Your problem may be due to interval comparison.
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.
=> select '2 years'::interval > '1 year 362 days'::interval;
?column?
----------
f
(1 row)
=> select '2 years'::interval > '1 year 359 days'::interval;
?column?
----------
t
(1 row)
=> select '2 years'::interval > '1 year 360 days'::interval;
?column?
----------
f
(1 row)
=> select '2 years'::interval = '1 year 360 days'::interval;
?column?
----------
t
(1 row)
If you want to do point in time arithmetic, you will be better of by
extracting epoch from your timestamps and substracting that. Intervals
are more for calendar arithmetic on the type "set me a date two
months, three days and four hours from the last".
Francisco Olarte.
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2024-03-04 13:04:22 | Re: Inconsistent results in timestamp/interval comparison |
Previous Message | Michał Kłeczek | 2024-03-04 12:35:33 | Re: postgres_fdw aggregate pushdown for group by with expressions |