I have ran into a problem with InfluxDB. I’m using Grafana for visualization of my data. First, here is some brief context:
My database contains power consumption figures and (solar) power production figures for my entire house.
Here is what I want, in English:
Show me how much power I consumed for the last week, per day, from 00:00 to 00:00 (local time) each day. In other words, I want to query a 24 hour time span beginning at midnight of one day and ending at midnight the next, for each day of the previous 7 days.
Here is what I have:
Multiple queries, one for each day:
7 days ago:
SELECT integral("power") /3600000 FROM "solar" WHERE ("time" > now() - 7d AND "time" < now()-6d)
6 days ago:
SELECT integral("power") /3600000 FROM "solar" WHERE ("time" > now()-6d AND "time" < now()-5d)
… And so on, for each day. The individual points in my DB are a measure of Watts, so the integral over 3600000 gives me the kilowatt hours.
Here is the issue:
now() function, as I’m sure you’re aware, returns the current time. So, if I execute the queries now, at 4:15pm, the results are actually showing me from 4:15pm yesterday until now… from 4:15pm 2 days ago until yesterday at 4:15pm… etc.
I don’t know how - or if it’s even possible in InfluxDB - to build a query to get the time, relative to
now(), that begins at 00:00 and ends at 00:00 the next day.
I have seen the stale issue here which describes the same functionality I need, but no solutions have been posted.
If it’s not possible to do in InfluxDB, I will explore the possibility of having Grafana build the dynamic query. If anyone knows how to do it in Grafana, it would be extremely helpful for you to share it here, though I realize this is not a Grafana forum…
Thanks for the help!