How to get the absolute value of an interval

Question

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)?

Answer 1

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

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

Answer 2

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