change in value running total of time between

Question

I have a program that periodically records details of a users mobile data connection (signal strength, network technology type, etc) to a single table in PostgreSQL. The relevant fields in the table are;

networkmedium.id
networkmedium.date_time
networkmedium.medium_type

An example table may look like;

0  |  2013-07-26 08:00:01  | 3G
1  |  2013-07-26 08:00:02  | GPRS
2  |  2013-07-26 08:00:06  | 3G
3  |  2013-07-26 08:00:10  | 3G
4  |  2013-07-26 08:00:16  | GPRS

I'm trying to get from this data the total duration spent on a particular medium type (i.e. GPRS, 3G etc), and I'm having difficulty.

In the above example I'd need the time difference between r1 and r0, r2 and r1, r3 and r1, r4 and r3, and then sum'd together grouped by medium_type, but I'm stuck how best to go about this.

Thanks in advance, Trev.

Answer 1

try this:

with CTE1 as
(
    select
        *,
        lead(date_time) over (order by date_time asc) as next_date_time
    from networkmedium
    order by date_time asc
)
select C1.medium_type, sum(date_part('second', C1.next_date_time - C1.date_time))
from CTE1 as C1
group by C1.medium_type

MEDIUM_TYPE |    SUM
--------------------
GPRS        |      4
3G          |     11

SQL FIDDLE EXAMPLE

more complex approach

with CTE1 as
(
    select
        *,
        lag(medium_type) over (order by date_time asc) as prev_medium_type
    from networkmedium
    order by date_time asc
), CTE2 as
(
    select *, row_number() over(order by date_time) as row_num
    from CTE1
    where prev_medium_type <> medium_type or prev_medium_type is null
)
select C1.medium_type, sum(date_part('second', C2.date_time - C1.date_time)) as cnt
from CTE2 as C1
    left outer join CTE2 as C2 on C2.row_num = C1.row_num + 1
group by C1.medium_type

SQL FIDDLE EXAMPLE