Adapted from the MySQL version here: https://stackoverflow.com/questions/58046256/calculate-total-on-time-from-on-off-timestamps
CREATE TABLE my_table (id INT, dt datetime, STATUS text);
INSERT INTO my_table VALUES (1,'2015-01-01 13:00:00' , 'ON'), (2,'2015-01-01 13:10:00' , 'OFF'), (3,'2015-01-01 13:20:00' , 'ON'), (4,'2015-01-01 13:30:00' , 'OFF'), (5,'2015-01-01 13:35:00' , 'ON'), (6,'2015-01-01 13:40:00' , 'OFF'), (7,'2015-01-01 13:50:00' , 'ON'), (8,'2015-01-01 15:00:00' , 'OFF');
SELECT x.*, TIME(CAST (( JulianDay(y.dt) - JulianDay(x.dt)) * 24 * 60 * 60 AS INTEGER), 'unixepoch') AS TimeDiff FROM my_table AS x INNER JOIN my_table AS y ON y.dt >= x.dt WHERE x.status = 'ON' AND y.status = 'OFF' GROUP BY x.id;
Result:
1|2015-01-01 13:00:00|ON|00:10:00 3|2015-01-01 13:20:00|ON|00:10:00 5|2015-01-01 13:35:00|ON|00:05:00 7|2015-01-01 13:50:00|ON|01:09:59
Last 24 hours:
SELECT x.*, TIME(CAST (( JulianDay(y.dt) - JulianDay(x.dt)) * 24 * 60 * 60 AS INTEGER), 'unixepoch') AS TimeDiff FROM my_table AS x INNER JOIN my_table AS y ON y.dt >= x.dt WHERE x.status = 'ON' AND y.status = 'OFF' AND JulianDay(x.dt) > (julianday('now') - 1) GROUP BY x.id;
A similar problem (with solution) is described here: https://stackoverflow.com/questions/18065846/sqlite-subtract-time-difference-between-two-tables-if-there-is-a-match