Hello,
I’m using grafana (v8.3.4) and InfluxDB to visualize some data concerning our heat pump.
This is the data (energy used for heating → cumulative) in the InfluxDB so far for the current month:
Every day, after midnight, the usage of the previous day is produced, and added to the Influx database. Important: the time that it is added to the database can slightly differ one day to another, so it is not always e.g. 02:00:00.
So we start on 2022-02-01 with the total counter reset to 0.
On 2022-02-02, the heat pump tells us that it used 4,2 kWh during the previous day, so the total counter is set to 4,2.
On 2022-02-03 we get 7,220 kWh, so the total counter is set to 11,420.
On 2022-02-04 we get 11,140 kWh, so the total counter is set to 22,560.
On 2022-02-05 we get 6,370 kWh, so the total counter is set to 28,930.
On 2022-02-06 we get 6,890 kWh, so the total counter is set to 35,820.
So, we have a database with cumulative energy values. We have 1 value / day, but time between the entries in the database can differ from 24 hours.
What I want to do seems not so complex, but yet I don’t succeed in getting the graphs that I would like …
I would like a graph that plots the DAILY usage, so:
2022-02-01 → 0
2022-02-02 → 4,2 kWh
2022-02-03 → 7,220 kWh
2022-02-04 → 11,140 kWh
2022-02-05 → 6,370 kWh
2022-02-06 → 6,890 kWh
Daily usage is the ‘value in the database’ minus the ‘value of the previous day’. And with ‘day’ I mean ‘calendar day (so a date)’, and not a timespan of 24 hours.
I thought I found a solution, based on some reactions I read on the website.
I used following query:
SELECT derivative(last(“value”), 1d) FROM “Counter” WHERE (“idx” = ‘52’) AND $timeFilter GROUP BY time(24h) fill(none)
This gives me the following curves:
The data above gives the orange curve (other curves are other energy values, but should be processed in the same way).
As you can see, the first points in the graph are correct: 4.2 kWh, 7,220 kWh, 11.140 kWh but then it goes wrong. Instead of 2 different points (6.370 kWh and 6,890 kWh), we only get 1 value of 13.3 kWh. It takes those 2 values together, probabily because there is less than 24 hours between the timestamps of those entries in the database.
Does anyone know how I can solve this problem?
So, with a query I want to produce 1 value / day, doesn’t matter at what time the data is added to the database. The data belongs to a specific day, but it doesn’t matter when the data was added during that day. And the values I want to display in the graph, is the usage for a day, so the difference between a value from the database, and the value that was registred the previous day.
Thanks in advance!