Inconsistent results in timestamp/interval comparison
От | albrecht.dress@posteo.de |
---|---|
Тема | Inconsistent results in timestamp/interval comparison |
Дата | |
Msg-id | 839f7314609c96c5123549a28d493534@posteo.de обсуждение исходный текст |
Ответы |
Re: Inconsistent results in timestamp/interval comparison
|
Список | pgsql-general |
Hi all, I run the “official” deb package postgresql-16 v. 16.2-1.pgdg120+2 on a Debian Bookworm system, and observed a confusing behavior in a calculation with time stamps and intervals. To reproduce, consider the following trivial example: <snip> create table testtab (t1 timestamp without time zone); insert into testtab values ('2022-02-27 11:46:33'), ('2022-03-11 23:39:17'), ('2022-03-21 17:49:02'); test=# select now(), t1, (now() - t1) >= '2 years'::interval, now() >= (t1 + '2 years'::interval) from testtab; now | t1 | ?column? | ?column? -------------------------------+---------------------+----------+---------- 2024-03-04 12:59:39.796969+01 | 2022-02-27 11:46:33 | t | t 2024-03-04 12:59:39.796969+01 | 2022-03-11 23:39:17 | t | f 2024-03-04 12:59:39.796969+01 | 2022-03-21 17:49:02 | f | f (3 Zeilen) </snip> According to the documentation, Table 9.31, IMHO both comparisons should produce the same results, as timestamp - timestamp → interval timestamp + interval → timestamp i.e. (now() - t1) >= '2 years'::interval # add t1 on both sides of the comparison now() >= (t1 + '2 years'::interval) As only the second example is wrong for the 1st comparison method, this might indicate some rounding and/or insufficient precision issue. Or did I miss something here? Thanks in advance, Albrecht.
В списке pgsql-general по дате отправления: