How do I sum the difference of 2 intervals in Postgresql?

Question

How do I sum the difference of 2 intervals in Postgresql?

Navigation

#1 by (3 votes)
#2 by (2 votes)

2

I need to make the sum of all the differences of the intervals that fulfill the condition of the where. There is only one, however, if there is no end_date on this line, I need to pick up the current schedule and do the calculation with the current time instead of end_date.

CREATE OR REPLACE FUNCTION total_paused_time (v_id Integer)
    RETURNS Interval
as $$
    DECLARE @aux integer
    select sum(@aux),
        case when end_date = null then @aux := now() - start_date 
        else then @aux := end_date - start_date 
        end
    from called_pause where called_id = v_id and internal = false;
$$ LANGUAGE SQL;

sql postgresql

asked by anonymous 21.08.2014 / 20:19

2 answers

2

I was able to solve the problem. Basically, conditions go into sum ().

CREATE OR REPLACE FUNCTION total_paused_time (v_id Integer)
    RETURNS Interval
as $$
    select sum(
        case when end_date = null then now() - start_date
        else end_date - start_date
        end)
    from called_pause where called_id = v_id and internal = false;
$$ LANGUAGE SQL;

21.08.2014 / 21:06

CSS Alignment Webmail Import data from excel to sql server

score 3 · Accepted Answer

3

Using coalesce is simpler:

select sum(coalesce(end_date, now()) - start_date)

30.09.2014 / 13:40