abs function for interval
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
Attachments:
0001-Add-abs-function-for-interval.patchtext/x-patch; charset=US-ASCII; name=0001-Add-abs-function-for-interval.patchDownload
From b11a05e3304250803c7aa2ac811e0d49b0adfc00 Mon Sep 17 00:00:00 2001
From: Euler Taveira <euler@timbira.com.br>
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
Hi,
On 2019-10-31 23:20:07 -0300, Euler Taveira wrote:
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); +} +
Several points:
1) I don't think you can do the < 0 check on an elementwise basis. Your
code would e.g. make a hash out of abs('1 day -1 second'), by
inverting the second, but not the day (whereas nothing should be
done).
It'd probably be easiest to implement this by comparing with a 0
interval using inteval_lt() or interval_cmp_internal().
2) This will not correctly handle overflows, I believe. What happens if you
do SELECT abs('-2147483648 days'::interval)? You probably should
reuse interval_um() for this.
--- 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
This is not even remotely close to enough tests. In your only test abs()
does not change the value, as there's no negative component (the 1 year
-2 month result in a positive 10 months, and the hours, minutes and
seconds get folded together too).
At the very least a few boundary conditions need to be tested (see b)
above), a few more complicated cases with different components being
of different signs, and you need to show the values with and without
applying abs().
Greetings,
Andres Freund
Em qui, 31 de out de 2019 às 23:45, Andres Freund <andres@anarazel.de> escreveu:
1) I don't think you can do the < 0 check on an elementwise basis. Your
code would e.g. make a hash out of abs('1 day -1 second'), by
inverting the second, but not the day (whereas nothing should be
done).It'd probably be easiest to implement this by comparing with a 0
interval using inteval_lt() or interval_cmp_internal().
Hmm. Good idea. Let me try it.
--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
