CODEWITHSUNDEEP
sqlpostgresqldatetimecasedate-arithmetic
Naik
Naik

Reputation: 25

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.

Upvotes: 2

Views: 667

Answers (1)

GMB
GMB

Reputation: 222432

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

Upvotes: 3

Related Questions