SHARE
TWEET

Untitled

a guest Oct 13th, 2017 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DEVICE    |       READING       |       VALUE       |       TIMESTAMP
  2. -----------------------------------------------------------------------------
  3.  Thermometer |     temperature     |        20.0       |  2107.10.12 00:12:59
  4.  Thermometer |     temperature     |        20.2       |  2107.10.12 00:24:12
  5.                                   ...
  6.    
  7. SELECT
  8.     filtered.hour as time,
  9.     AVG((SELECT VALUE
  10.         FROM history
  11.         WHERE READING="temperature" AND DEVICE="Thermometer" AND TIMESTAMP <= filtered.hour
  12.         ORDER BY TIMESTAMP DESC
  13.         LIMIT 1
  14.     )) as value
  15. FROM (
  16.     SELECT calculated.hour as hour FROM (
  17.             SELECT DATE_ADD(DATE_SUB(DATE($__timeTo()), INTERVAL 10 YEAR), INTERVAL t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i HOUR) as hour
  18.             FROM (SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
  19.                      (SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
  20.                      (SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
  21.                      (SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
  22.                      (SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
  23.     ) calculated
  24.     WHERE calculated.hour >=  $__timeFrom() AND calculated.hour <= $__timeTo()
  25. ) filtered  
  26. GROUP BY DATE(filtered.hour)
RAW Paste Data
Top