calculatetimes [ErgoTech Systems, Inc. TechWiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

Adapted from the MySQL version here:
https://stackoverflow.com/questions/58046256/calculate-total-on-time-from-on-off-timestamps

<code sql>
create table my_table (id int, dt datetime, status text);
</code>

<code sql>
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');
</code>

<code sql>
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;
</code>

Result:

<code>
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
</code>

Last 24 hours:
<code sql>
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;
</code>

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