views:

161

answers:

1

I have a series of timestamped on/off data in a table, representing on/off states, or the point at which a state "starts"

00:00:00    0
04:00:00    1
08:00:00    0
09:00:00    1
15:00:00    0
20:00:00    1
23:59:59    0

I need to calculate the total duration of (say) the ON state over a 24h period.

In this simplified example total duration = 1 is (04:00:00->08:00:00, 09:00:00->15:00:00, 20:00:00->23:59:59 i.e. 13:59:59 approx 14h

I can't determine whether this can be done in SQL alone, or whether the underlying framework i am using (django) would need to do this based on returned data. I would obviously prefer to have the database do the heavy lifting if possible, because we may need to use the SQL in our separate stats package as well.

It's not clear to me if I can do operations on (say) previous or next element in the select, I am a confident SQL user but can't see where to start for this or the generalised approach, any ideas?

I'd really like this in a single query, or some other clever way of calculating this I am missing!

+1  A: 

There's no row_number() in MySQL, but you can do a double join to search for the previous row:

select 
    sum(case when cur.state = 0 then 0
        else subtime(cur.timeCol, prev.timeCol)
        end) as TotalOnTime
from YourTable cur
join YourTable prev
    on prev.timeCol < cur.timeCol
left join YourTable inbetween
    on prev.timeCol < inbetween.timeCol
    and inbetween.timeCol < cur.timeCol
where inbetween.timeCol is null;

In MySQL, you can also use a variable, which in this case is probably more efficient:

set @total := '00:00:00';
set @lasttime := '00:00:00';

select 
    @total := addtime(@total, case 
        when state = 0 then 0
        when @lasttime is null then 0
        else subtime(timeCol, @lasttime)
        end)
,   @lasttime := timeCol
from YourTable
order by timeCol;

select 'Result = ', @total;

Code to create and populate test table:

DROP TABLE IF EXISTS YourTable;
CREATE TABLE YourTable (
   timeCol time,
   state bit
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into YourTable values ('00:00:00',    0);
insert into YourTable values ('04:00:00',    1);
insert into YourTable values ('08:00:00',    0);
insert into YourTable values ('09:00:00',    1);
insert into YourTable values ('15:00:00',    0);
insert into YourTable values ('20:00:00',    1);
insert into YourTable values ('23:59:59',    0);
Andomar
The complexity of this query makes me wonder whether this could be a lot more efficient when done in code and not in SQL. But looks like it would work, though.
Tatu Ulmanen
I think I get where you are going with the variable example, and I think I am much closer thanks to your help.I'm not familiar with the period (".") prefix before the @lasttime var, what does this achieve? I'm getting an error with this syntax in place, and I can't find what it means in the manuals.
Aitch
@Aitch: That should've been a `,` comma, for next column
Andomar
@Andomar i thought that would be the case, but wondered whether i had missed some quirky mysql shorthand abbreviation :) Just tweaking the new code, i was getting some encouraging results after your advice, working in my extra criteria; will post findings for completeness when i finish it
Aitch