21
votes

Consider the following statement:

select interval '-1 hours'

I couldn't figure out how to get the absolute value of an interval, i.e. to toggle or remove the sign if negative. The only thing that came to my mind is the following:

select abs(extract(epoch from interval '-1 hours'))

But I wonder if there is a more elegant way (a way that preserves the interval type)?

4

4 Answers

17
votes

You can find the greatest value between i and -i. For example:

SELECT greatest(-'1 hour'::interval, '1 hour'::interval);
11
votes

There's a discussion on the pgsql-general mailing-list: Absolute value of intervals on why a built-in abs(interval) function is not provided with PostgreSQL.
In short, there's no consensus about what it should do in some cases, when considering the componentized nature of the interval type.

But anyone can create their function implementing their own idea about what it should compute, for instance, building on the expression from LisMorski's answer:

CREATE FUNCTION abs(interval) RETURNS interval AS
  $$ select case when ($1<interval '0') then -$1 else $1 end; $$
LANGUAGE sql immutable;

Simple SQL functions are generally inlined during query execution, so the performance should be comparable to having the expression inside the query.

Example:

#= select abs(interval '-2 days +3 minutes');
       abs        
------------------
 2 days -00:03:00


# select abs(now()-clock_timestamp());
       abs       
-----------------
 00:00:00.000146
9
votes

A CASE expression would look more self-explanatory. Example:

SELECT
    i,
    (CASE WHEN (i < INTERVAL '0') THEN (-i) ELSE i END) AS abs_i
FROM
    (VALUES
        (INTERVAL '-2 h'),
        (INTERVAL '2 m')
    ) AS foo (i)

which produces:

     i     |  abs_i
-----------+----------
 -02:00:00 | 02:00:00
 00:02:00  | 00:02:00
3
votes

It way isn't more elegant than yours, but it returns an interval type

select interval '-1 hours'*sign(extract(epoch from interval '-1 hours'))