Getting daily stats from influxdb (00:00 - 24:00)

Grafana InfluxDB
1

Hi All,

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') )

which gives:

image
image898×293 31.7 KB

2

In case someone reads this and has the same question.

I found the better way to be using this plugin in home assistant providing the correct data to influx by means of automation.

3

Indeed, but do you display them in Grafana using FLUX ???