Derived values of an energy counter

Grafana InfluxDB
1

Hi,

I am using Grafana Version 9.1.6 with a similarly old, unknown version of InfluxDB¹. In this Grafana/Influx installation, I collect the numbers of various energy meters. I usually read in the meter value of consumed energy (in Wh or kWh) and use the derivative function to get the “current” energy consumption (in W or kW). This has proven to work better than reading out the consumption values that some meters offer².

One of the meters is read via Modbus from the solar power inverter, and it is crippled by the fact that it only reports full kWh (but in Wh³), while the other meters usually report by Wh or even a fraction of an Watt-Hour. So, this counter stays at the same level for quite some time (with the reading staying the same, creating new database rows with the same value over and over) and then jumps by 1000. The derive function plots this as a series of zeroes followed by a huge peak.

Screenshot_20241204_154205
Screenshot_20241204_1542051005×1221 84.2 KB

(please disregard the units, they’re at least misleading if not plain wrong in the example)

Green: The raw meter value
Yellow: What derive makes from it.

I have deliberately screenshotted the queries with the graph.

The InfluxDB query for the green graph is

SELECT last("Val") 
FROM "TS00339" 
WHERE $timeFilter 
GROUP BY time($__interval) fill(previous)

The InfluxDB query for the yellow graph is

SELECT derivative(last("Val"),1h) 
FROM "TS00339" 
WHERE $timeFilter 
GROUP BY time($__interval) fill(previous)

TS00339 is the time series that the appliance writes to InfluxDB, and “Val” is the column name also pre-set and unchangeable from the appliance.

I would like the yellow graph to average the power consumption.

Example: Let’s assume that the load takes 100 W constantly. That means that the meter value will advance by 1000, then stay at X for 10 hours and then advance by one thousand again. I’d like my green graph to show 100 W for the entire 10 hours.

Another example: Let’s assume that the meter has advanced by 1000 exactly at midnight for the last time, the load takes 0 W, except between 03:00 and 04:00 where it took 990 W (= 990 Wh) and then between 09:00 and 10:00 990 W again. At some time before 09:01, the meter will thus advance by one thousand, and I’d like Grafana to draw a horizontal line at just-a-bit-above-100-W (below? brain too foggy to get the direction right) for the entire time between midnight and 09:01.

Can I get my ancient InfluxDB/grafana combination to do this without changing the data preparation and without losing the existing data? Or do I need to plug some math between the Modbus and Influx intake?

Thanks for helping! I really appreciate that.

¹ and Updates won’t happen because it’s kind of an appliance in house automation
² because most meters just export a snapshot of the current wattage and not the averaged value between the last two reads, which is rather different if your load wattage changes quickly and radically
³ sick, I know, but unchangeable

1 Like
2

I guess you need to derivate on longer time period, e.g. GROUP BY time(1h) without filling - just connect null values.
Maybe you can use also subquery, where inner query will derivate and outer query will calculate avg on top of that - again on longer time periods.

3

Hi @zugschlus

It sounds like there are several problems that you have to deal with. Have you considered migrating (and cleaning up, as required) your data from InfluxDB v1 to v2 or v3? I did this for my v1 data years ago and it went very smoothly.

v2 or v3 will allow you to more easily do the queries you want to do.

How are you ingesting the Modbus data into InfluxDB? Can you transform or manipulate it before it’s ingested into Influx (e.g. Extract → Transform → Load)?

4

If I use GROUP BY time(1h), I always get the average even if the meter jumps every ten minutes, right? That is not what I want.

And when I remove the “last” selector, the graph just changes to a “No data” message.

Can you give more exact suggestions that I can try? I fear that I didnt get your tips right.

Greetings
Marc

5

yeah, you don’t have regular period, but InfluxQL can group only on static periods (e.g. 1m, 1h, 1d, …). So if you need avg for current reported period, which is dynamic (e.g. sometimes it’s 10m, sometimes 1h), then that’s not possible.

6

As written in my original request, I am stuck on those older versions because InfluxDB and Grafana are running on a really nice appliance with a small footprint and little power consumption. Running later versions would mean to run both the database and Grafana myself, which is a work that I probably need to do sooner or later but I’d like to avoid that at the current time. And, if I run my own installation, I’d probably use a Prometheus Time Series Database instead of a later Influx.

7

I’d like to have the average between the last two “movements” in the input data, which might be variable, yes.

8

So show me how can you group: GROUP BY time(<between two movements>)? That’s not possible, because you can GROUP BY time(<static period: e.g. 1m, 1h, 1d>) only.

9

I have only used Grafana for about three years with very basic usage, so you’re way beyond my knowledge. I apologize for not being able to talk with you on your level on knowledge. I can only put in words what I’d like to have at the moment.

I don’t even know how the mechanics of GROUP BY and derivative() work.

10

This is not about Grafana knowledge. You are using InfluxQL query language, so this InfluxQL knowledge is important:

@grant2 suggested newer versions of InfluxDB, where you may have another query languages, which may be more useful for your use case.
I recommend his blog post A comparison of InfluxQL, SQL, and Flux query languages for Grafana dashboards | Grafana Labs so you will have idea about InfluxQL, SQL, and Flux query languages.

11

This is why I am posting on the Grafana/InfluxDB forum.