Differences

This shows you the differences between two versions of the page.

--- calculatetimes [2021/02/16 13:45] – wikiadmin
+++ calculatetimes [2021/02/16 13:55] (current) – wikiadmin
@@ Line 36: / Line 36: @@
 |2015-01-01 13:35:00|ON|00:05:00
 |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