After fighting for longer than I’d like to admit with this function I finally managed to get it working.
I use a single stat visualation and the below queries to give me energy usage in Watt/Hour from my data stored in Watts.
1hr Usage: (Relative time over-ride = 1h)
SELECT integral("Energy_Usage",1h) FROM "esp" WHERE ("Device" = 'esp_03') AND $timeFilter GROUP BY time(3h)
24hr Usage: (Relative time over-ride = 24h)
SELECT integral("Energy_Usage",1h) FROM "esp" WHERE ("Device" = 'esp_03') AND $timeFilter GROUP BY time(3d)
7 Day Usage: (Relative time over-ride = 7d)
SELECT integral("Energy_Usage",1h) FROM "esp" WHERE ("Device" = 'esp_03') AND $timeFilter GROUP BY time(21d)
That’s it!
Thank you for the trick to add the “, 1h” to the integral() function to integrate over each hour to get Watt Hours that can then be grouped into time buckets of one’s choosing.
One quick amendment, though. If you want kWh, and not Wh, divide by 1000:
`SELECT integral(“Energy_Usage”,1h) / 1000 FROM “esp” WHERE (“Device” = ‘esp_03’) AND $timeFilter GROUP BY time(3h)`