Hi,
Sometimes you want to answer if a difference between two timestamps is
lesser than x minutes but you are not sure which timestamp is greater
than the other one (to obtain a positive result -- it is not always
possible). However, if you cannot obtain the absolute value of
subtraction, you have to add two conditions.
The attached patch implements abs function and @ operator for
intervals. The following example illustrates the use case:
postgres=# create table xpto (a timestamp, b timestamp);
CREATE TABLE
postgres=# insert into xpto (a, b) values(now(), now() - interval '1
day'),(now() - interval '5 hour', now()),(now() + '3 hour', now());
INSERT 0 3
postgres=# select *, a - b as t from xpto;
a | b | t
----------------------------+----------------------------+-----------
2019-10-31 22:43:30.601861 | 2019-10-30 22:43:30.601861 | 1 day
2019-10-31 17:43:30.601861 | 2019-10-31 22:43:30.601861 | -05:00:00
2019-11-01 01:43:30.601861 | 2019-10-31 22:43:30.601861 | 03:00:00
(3 rows)
postgres=# select *, a - b as i from xpto where abs(a - b) < interval '12 hour';
a | b | i
----------------------------+----------------------------+-----------
2019-10-31 17:43:30.601861 | 2019-10-31 22:43:30.601861 | -05:00:00
2019-11-01 01:43:30.601861 | 2019-10-31 22:43:30.601861 | 03:00:00
(2 rows)
postgres=# select @ interval '1 years -2 months 3 days 4 hours -5
minutes 6.789 seconds' as t;
t
-----------------------------
10 mons 3 days 03:55:06.789
(1 row)
--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
