Plot electrical energy generated this year so far

Hello,

We are a group of photovoltaic enthusiast and are using Grafana to plot statistics about the “generation” and consumption of electrical energy.

We are using Grafana with InfluxDb and Telegraf.

We have a database which is constantly updated with the current meter values in Watt-hours. So the value will always be increased.

This works for years without a problem. Thanks to the Grafana-Team!!!

Now we want to create an new panel which displays the generated energy for each year. So the plot should start at zero every 1st of January and should reach the maximum value for this year on 31st of December.

For illustration:


The first graph shows the meter value, like it is stored in the database. This works without any problems with the following statement.

SELECT mean(“MeterValue”) FROM “mqtt_consumer” WHERE (“topic” = ‘PV’) AND $timeFilter GROUP BY time($__interval) fill(null)

The second graph is what we want to plot. This should be very easy. It is only the meter value subtracted by the first value of the year. I can do this with the difference() function with a time() of one year. But this outputs only on data point per year.

SELECT difference(mean(“MeterValue”)) FROM “mqtt_consumer” WHERE (“topic” = ‘PV’) AND $timeFilter GROUP BY time(1Y) fill(null)

How can I generate the second graph?

Regards,
Otti

welcome to the :grafana: forum, @otti1

this feels like a @svetb specialty topic :grinning_face_with_smiling_eyes: :grafana: :heart: .

I’d also try searching through this forum. This type of time-related question pops up regularly

1 Like

Hi @mattabrams thanks for the prod!

@otti1 - interesting problem. Although your raw data is pretty standard, I don’t think I’ve previously seen requirements for output that are quite like these. And while I don’t disagree that “This should be very easy.” I can’t off the top of my head think of a way to do it with InfluxQL.

Doing GROUP BY time(1Y) makes sense superficially, since that lets you “access” the value at the start of each year, but it also means that you only get a single value per year (i.e. the difference for that year) - as you found. You could try to do something smart with subqueries, but my sense is that it wouldn’t work - since you fundamentally need to work simultaneously on two different timescales: on one hand you need to access the “now” values for every point in time, and you also need to access the “start of the year” value. But in an Influx query you can only do one or the other - and you also unfortunately can’t couple subqueries side-by-side in the way that you’d need to (c.f. Explore data using InfluxQL | InfluxDB OSS 1.8 Documentation).

It’s almost certainly possible to do it in Flux instead of InfluxQL, but I’m afraid I’m not well-equipped to provide you the answer there.

Another thing you could try is to have two separate Influx queries and do the arithmetic within Grafana:

  • Query A would be your raw MeterValue data interpolated at $__interval, as in your first query
  • Query B would be a dataset with the same timestamps, but which contains the start value of that year at each point

Then in Grafana you can plot A - B, which is I think what you want. I haven’t used this functionality myself, but I feel like this is possible. Query B would look like

SELECT mean("MeterValueJan1") FROM (
  SELECT first("MeterValue") as "MeterValueJan1" FROM "mqtt_consumer" WHERE ("topic" = 'PV') GROUP BY time(1Y)
) WHERE $timeFilter GROUP BY time($__interval) fill(previous)

This uses a subquery to get the first value of each year, and then the outer query uses fill(previous) to replicate that value for every subsequent point in the year. By the way, I’ve sometimes observed funkiness with the time boundaries that GROUP BY applies, so you may potentially end up with values from the year before or after the right one, but that shouldn’t be hard to fix.

Hope that helps!

2 Likes