I’m trying to get a graph that would display my heat pump power consumption per hour or day or week or month
I get cumulative kWh readings every minute from a Gavazzi sensor through modbus and nodered that are being fed into my influxdb database.
I’m getting the cumulative values displayed correctly in Grafana, but how do I split it to only show difference since last value? Be that value say last day, last hour or last month depending on my time period set.
This is my query that shows the cumulative graph:
SELECT mean(“totalkWh”) FROM “modbus” WHERE $timeFilter GROUP BY time($__interval) fill(previous)
I have found some info here on the forum to use difference, but I don’t understand how to write the code for it.
Hello. Just to get things rolling, maybe try this query to see if it gives you the results grouped by day?
SELECT mean("totalkWh")*24 FROM "modbus" WHERE $timeFilter GROUP BY time(1d) fill(none)
Also, in this thread, the poster said he also wanted to convert cumulative values to daily/hourly/monthly values, but he actually wanted Stat panels showing these values. Just to clarify, do you want the same, or do you want several regular time series graphs, e.g. one showing bars for hourly power consumption, another for daily power consumption, etc.
Thank you, this gives me something like this (just have values from yesterday as i started logging then) So it seems to group ok but the values seem wrong.
The Gavazzi manual says this value is reported as kWh
My main goal is to get a graph with bars of monthly heat pump power consumption so I can calculate heating costs. But it would also be nice to be able to change the grouping to day or week.
I’ll check the other thread too! Thank you for finding it for me
With that query I get no data, maybe because I don’t have enough data yet to fill a whole day?
I tried going down to group per hour instead because my data series is still so short.
This gives me:
I have set nodered to update the kWh value from the Gavazzi every minute. The influx database is being updated every 10 seconds but I don’t know how accurate that timing is.
So this seems to work, if then we could find a way to show the difference between those bars that would be great!
Ok, so using a slightly modded version of that code
SELECT power-power_fill as Energy FROM
(SELECT first(totalkWh) as power_fill from modbus WHERE $timeFilter group by time(1d) ),
(SELECT first(totalkWh) as power from modbus WHERE $timeFilter GROUP BY time($__interval))
fill(previous)
Okay, some further tinkering gives me these two graphs.
So the green seems to be accumulative kWh per hour grouped and reset per day
The yellow is logged power draw.
This looks slightly more okay, but I don’t really understand what the code does and I need to check the values because it doesn’t add up.
The absolute easiest way was to group by whatever time I was interested in, say hour and then just use spread(value) to give the delta between highest and lowest in the time range. I’m going to mark this as a solution because it was the solution for me in the end.
Good question it looks like you have the same settings as I have.
It works really well for me. What version of Grafana/Influxdb do you have?
I have Grafana 8.3.3 and influxdb 1.7
Oh! Maybe it’s the short time interval in your query options for spread. I can see it says 10 minutes, it’s possible it calculates the spread over only 10 minutes instead of a day. What happens if you change the Query options interval to 1d when using spread?
Thanks for the suggestion, the values are however still 0 but now it displays lines between the points.
I only report the data once a day to influx but I guess I could change it to report more often if I could get this to work.
What happens if you enter a month as min interval? I was just thinking if you report data once a day then for that day there will be no spread between current and last reported value (which is what spread reports) You would have to have at least a 2 day period to log 2 different values. But since you get a rising value for mean, I don’t really understand why this would be the case. Try upping the reporting frequency and see what happens. Good Luck!
btw I’m reporting every 10 seconds Which is way too high for kWh but that’s how I have it set up. Good thing you mentioned it, Think I’ll adjust it to once a minute and save some data points.
No difference for me when changing the “min interval”. But yes, I guess that could be the case, that I need more data points. Need to test that out. I also feel I need to read up better how the spread function works in more detail, thanks for your input!
Which is way too high for kWh but that’s how I have it set up. Good thing you mentioned it, Think I’ll adjust it to once a minute and save some data points.