Adding negative Intervals in PostgreSQL

I’m using the following code to add intervals. But it doesn’t seem to work if the times aren’t on the same day.

For example start = 23:00 and end = 01:00

Code

SELECT extract(epoch from SUM(end - start)::interval) FROM table GROUP BY column

Problem

If I add: end = 01:00 and start = 23:00 then it will add a negative and false number to the sum. Because 01:00 – 23:00 is -22:00. However, the correct result should be 2:00 because between 23:01 and 01:00 there are two hours.

Answer

You could use a case expression. Instead of:

end - start

You would add 24 hours to negative intervals like so:

case when end >= start 
    then end - start 
    else (end - start) + interval '24 hour'
end

Note that there is no need to convert the result of the time substraction to an interval – it is already.

In your query:

select col,
    extract(epoch from sum(
        case when end >= start 
            then end - start 
            else (end - start) + interval '24 hour'
        end
    )) as res
from mytable 
group by col