daily pastebin goal
39%
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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top