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 <eu...@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