Search code examples
sqlpostgresqldatetimecasedate-arithmetic

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.


Solution

  • 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