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!