I needed a couple of gauges that shows me the total m3 gas consumption per day (00:00 - 24:00). After some google searches i couldn’t find a good answer. Most solutions only provide a 24h total. This was not good enough for me since the totals would be changing every hour.
I ended up with the following solution that works, however i wonder if there is a better way to do this?
- Select values starting the day before: time>NOW()-2d
- group them by time(1d)
- LIMIT the outcome to two results (the day before, and the day we are looking for)
- create an outer select that selects the LAST value
SELECT MAX("sum") FROM ( SELECT SUM("value") FROM "m3/h" WHERE time>NOW()-2d GROUP BY time(1d) LIMIT 2 TZ('Europe/Amsterdam') )