How do I sum up the 2-interval difference in Postgresql?

Question

How do I sum up the 2-interval difference in Postgresql?

Asked 10 years, 2 months ago

Viewed 135 times

2

I need to sum up all the gap differences that meet the Where condition. There is only one however, if there is no end_date on this line, I need to take the current time 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;

And what mistake you’re facing ?

– gmsantos

2014/08/21 at 19:03
1

The version of postgresql that I use, does not accept the use of @variavel, so I was having a syntax error, I ended up solving the problem differently and published the answer below.

– ramoncs11

2014/08/22 at 12:04
3

You published a syntax error and a vague problem... and you yourself answered... it’s still hard to understand the usefulness of this.

– Peter Krauss

2014/08/28 at 00:16

2 answers

3

Using coalesce is simpler:

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

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.

by ramoncs11 • 43 points · Answer 1 · 2014-08-21T19:06:48+00:00

I managed to solve the problem. Basically, the conditions go within the 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;