MySQL query to evaluate aggregate duration of time something is "on"

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
开发者_JAVA技巧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!

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);