Remove character from interval

Question

Consider the following statement:

select interval '-1 hours' 

I could not figure out how to get the abs() interval, i.e. toggle the sign if it is negative. The only thing that occurred to me was this:

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

But I am wondering if there is a more elegant way (way to preserve interval type)?

Answer 1

A CASE expression would have looked more self-evident. 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 

Answer 2

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

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

Answer 3

This method is not more elegant than yours, but it returns the type of interval

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

Answer 4

The pgsql-general mailing list is discussed here: The absolute value of intervals about why the built-in abs(interval) function is not provided by PostgreSQL.
In short, there is no consensus on what it should do in some cases when considering the component nature of an interval type.

But anyone can create their own function, realizing their own idea of ​​what it should calculate, for example, based on an 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 usually built-in during query execution, so performance should be comparable to 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