Differences

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

--- calculatetimes [2021/02/16 13:32] – created wikiadmin
+++ calculatetimes [2021/02/16 13:55] (current) – wikiadmin
@@ Line 38: / Line 38: @@
 </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