calculatetimes

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
calculatetimes [2021/02/16 12:45]
wikiadmin 		calculatetimes [2021/02/16 12:55] (current)
wikiadmin
Line 36: Line 36:
 5|2015-01-01 13:35:00|ON|00:05:00 5|2015-01-01 13:35:00|ON|00:05:00
 7|2015-01-01 13:50:00|ON|01:09:59 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> </code>
  
 A similar problem (with solution) is described here: 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 https://stackoverflow.com/questions/18065846/sqlite-subtract-time-difference-between-two-tables-if-there-is-a-match
  • calculatetimes.1613501122.txt.gz
  • Last modified: 2021/02/16 12:45
  • by wikiadmin