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
From b11a05e3304250803c7aa2ac811e0d49b0adfc00 Mon Sep 17 00:00:00 2001
From: Euler Taveira <[email protected]>
Date: Thu, 31 Oct 2019 23:07:00 -0300
Subject: [PATCH] Add abs function for interval.
Sometimes you want to answer if a difference between two timestamps is
lesser than x minutes. However, if you cannot obtain the absolute value
of subtraction, you have to add two conditions. Let's make it simple and
add abs function and @ operator for intervals.
---
doc/src/sgml/func.sgml | 19 +++++++++++++++++++
src/backend/utils/adt/timestamp.c | 17 +++++++++++++++++
src/include/catalog/pg_operator.dat | 3 +++
src/include/catalog/pg_proc.dat | 6 ++++++
src/test/regress/expected/interval.out | 8 ++++++++
src/test/regress/sql/interval.sql | 4 ++++
6 files changed, 57 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 28eb322f3f..9882742aba 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -7370,6 +7370,12 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
<entry><literal>interval '1 hour' / double precision '1.5'</literal></entry>
<entry><literal>interval '00:40:00'</literal></entry>
</row>
+
+ <row>
+ <entry> <literal>@</literal> </entry>
+ <entry><literal>@ interval '-2 hour'</literal></entry>
+ <entry><literal>interval '02:00:00'</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -7391,6 +7397,19 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
<row>
<entry>
<indexterm>
+ <primary>abs</primary>
+ </indexterm>
+ <literal><function>abs(<type>interval</type>)</function></literal>
+ </entry>
+ <entry><type>interval</type></entry>
+ <entry>Absolute value</entry>
+ <entry><literal>abs(interval '6 days -08:16:27')</literal></entry>
+ <entry><literal>6 days 08:16:27</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>age</primary>
</indexterm>
<literal><function>age(<type>timestamp</type>, <type>timestamp</type>)</function></literal>
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 1dc4c820de..a6b8b8c221 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -2435,6 +2435,23 @@ interval_cmp(PG_FUNCTION_ARGS)
PG_RETURN_INT32(interval_cmp_internal(interval1, interval2));
}
+Datum
+interval_abs(PG_FUNCTION_ARGS)
+{
+ Interval *interval = PG_GETARG_INTERVAL_P(0);
+ Interval *result;
+
+ result = palloc(sizeof(Interval));
+ *result = *interval;
+
+ /* convert all struct Interval members to absolute values */
+ result->month = (interval->month < 0) ? (-1 * interval->month) : interval->month;
+ result->day = (interval->day < 0) ? (-1 * interval->day) : interval->day;
+ result->time = (interval->time < 0) ? (-1 * interval->time) : interval->time;
+
+ PG_RETURN_INTERVAL_P(result);
+}
+
/*
* Hashing for intervals
*
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index fa7dc96ece..09ce9f2765 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -2164,6 +2164,9 @@
{ oid => '1803', descr => 'subtract',
oprname => '-', oprleft => 'timetz', oprright => 'interval',
oprresult => 'timetz', oprcode => 'timetz_mi_interval' },
+{ oid => '8302', descr => 'absolute value',
+ oprname => '@', oprkind => 'l', oprleft => '0', oprright => 'interval',
+ oprresult => 'interval', oprcode => 'interval_abs' },
{ oid => '1804', descr => 'equal',
oprname => '=', oprcanmerge => 't', oprleft => 'varbit', oprright => 'varbit',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 58ea5b982b..e7277e1aac 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2280,6 +2280,12 @@
{ oid => '1295', descr => 'promote groups of 30 days to numbers of months',
proname => 'justify_days', prorettype => 'interval',
proargtypes => 'interval', prosrc => 'interval_justify_days' },
+{ oid => '8300',
+ proname => 'interval_abs', prorettype => 'interval', proargtypes => 'interval',
+ prosrc => 'interval_abs' },
+{ oid => '8301', descr => 'absolute value',
+ proname => 'abs', prorettype => 'interval',
+ proargtypes => 'interval', prosrc => 'interval_abs' },
{ oid => '1176', descr => 'convert date and time to timestamp with time zone',
proname => 'timestamptz', prolang => 'sql', provolatile => 's',
prorettype => 'timestamptz', proargtypes => 'date time',
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index f88f34550a..e2e4ea606e 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -927,3 +927,11 @@ select make_interval(secs := 7e12);
@ 1944444444 hours 26 mins 40 secs
(1 row)
+-- test absolute operator
+set IntervalStyle to 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)
+
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index bc5537d1b9..8f9a2bda29 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -308,3 +308,7 @@ select make_interval(months := 'NaN'::float::int);
select make_interval(secs := 'inf');
select make_interval(secs := 'NaN');
select make_interval(secs := 7e12);
+
+-- test absolute operator
+set IntervalStyle to postgres;
+select @ interval '1 years -2 months 3 days 4 hours -5 minutes 6.789 seconds' as t;
--
2.11.0
