Newbie: How do I split cumulative kWh measurement to difference per time period?

Grafana 7.3.4

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.

Any help would be greatly appreciated.

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.

1 Like

Hi Grant!

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

Just for comparison, what graph appears when you change the query to this?

SELECT mean(“totalkWh”) FROM “modbus" WHERE $timeFilter GROUP BY time(1d) fill(none)

Also, in Influx, are your values there every minute? every second? every 7 minutes?

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!

Try this and see what the graph looks like (really no idea if this will work, but gotta try)…

SELECT DIFFERENCE(“totalkWh”) FROM “modbus" WHERE $timeFilter GROUP BY time(1h) fill(none)

InfluxDB Error: error parsing query: found “, expected identifier, string, number, bool at line 1, char 19"

totalkWh should be used (not totalkwh)

(Maybe you already had it correct?)

Yeah, that still gives me an error.

I found something quite similar in this thread and I’m trying to make that work on an hourly basis at first Make InfluxDB/Grafana cumulative function that resets daily (sawtooth graph) - Stack Overflow

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

i get:

Which seems to start counting from 0. But it seems something is wrong with the kWh value

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.


Hi! Does this work for you? I tried the spread option but that just floors all measurements?

Compared to just showing the mean values

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?

However, my graph still works at lower grouping values… strange:

But for the values in the above graph are not per 10 minutes.

1 Like

Thanks for the suggestion, the values are however still 0 but now it displays lines between the points. :wink:
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. :slight_smile:

I’m running Influxdb 1.8.10 and Grafana 8.3.3.

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 :stuck_out_tongue: 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.

1 Like

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!

1 Like

I use influx v2 and the derivative function with very good succes on these kind of calculation. I see this function exists in 1.8 too.