How to do calculation in InfluxDB 2.0

InfluxDB 2
,
1

Hello, I am new to this topic but have a question.
I use InfluxDB2.6.1 and feed the database with electricity data from my house and my solar system.
My smart meter measures data and so does my solar system.
The whole thing should be visualised in grafana.
I am familiar with simple queries, but when it comes to calculation, I am out. I have tried a lot using chat GPT, but it didn’t work the way I wanted or at all.

I want to calculate the amount of electricity that is directly consumed by my house when the solar system is providing electricity and the amount that is fed into the grid from surplus.
The daily yield of the solar system is measured at the inverter and recalculated daily. So in the evening I can see how much the solar system has generated today.
My smart electricity meter counts the amount of electricity consumed from the grid and the amount I feed into the grid.

Both are absolute values that are counted up.

To calculate the direct consumption of the inverter, I have to calculate as follows:

Direct consumption = Today’s production solar - (current feed-in number - feed-in number the day before at 23:59).

A query of today’s production is simple, it is straightforward.
A query of the current count is also simple.
A query for the value that was at 23:59 the previous day is not so simple. Every time I tried using Chat GPT, I got errors because you can’t subtract or add time intervals. Or a value came out that was not correct.

So my question is, what is the best way to carry out such a calculation?
Or should I rather do the whole thing in Grafana and if so, how?
Thanks a lot

2

Hi @agentsmith1612

To do a query for the value that was at 23:59 the previous day, would something like this work? I am not sure how often you are collecting the data, so if the last data point collected yesterday was at 23:45, then this query would close, but not perfect.

import "experimental/date/boundaries"
day = boundaries.yesterday()

from(bucket: "your-bucket")
|> range(start: day.start, stop: day.stop)
...
|> filter(fn: (r) => r["_field"] == "your-electricity-field")
|> last()
|> yield(name: "last")
3

I will try this tomorrow.

The last datapoint on the counter for electricity in the grid is at this time when the solar plant is not producing so when the sunset occurrs.
Round about 22 or in summer at least 23 I think. So every value between 23:00 the day befor or 2 o´clock in the morning will be the same count.

4

Seems to work. Here it is in Grafana. The time is off by 4 hours due to my timezone (there is a fix for this). Remember to use the last() function.

image
image924×452 27.5 KB

5

Thank you very much @grant1 for this code. It works for me. I will get one value that is yesterday at 00:00.
Only for interest what I have to put in to go like 4 hours back?

import "experimental/date/boundaries"
day = boundaries.yesterday()
from(bucket: "Stromzähler")
|> range(start: day.start, stop: day.stop)
|> filter(fn: (r) => r["_measurement"] == "Einspeisung")
|> filter(fn: (r) => r["_field"] == "value")
|> last()
|> yield(name: "last")

Now it comes to calculations: Should I calculate in influxDB or should I do that with expressions in Grafana?
I mean the possibility of grafana to use “total” or “difference” in the value options are only reagarding one graf, like to calculate the difference of start and stop. That is working when I want to know how much consumption from the grid I have or how much I put in the grid.

But it is not works when it comes to calculation with miltiple grafs (in worst case out of different buckets).

6

Hi @agentsmith1612

Re: the timezone / 4 hour offset, see the latter part of this thread.

For the calculation (current feed-in number - feed-in number the day before at 23:59), let’s rewrite as:

t1 - t2

Does this code return the correct value?

import "experimental/date/boundaries"
day = boundaries.yesterday()

t1 = from(bucket: "Stromzähler")
|> range(start: -1m)
|> filter(fn: (r) => r["_measurement"] == "Einspeisung")
|> filter(fn: (r) => r["_field"] == "value")
|> last()

t2 = from(bucket: "Stromzähler")
|> range(start: day.start, stop: day.stop)
|> filter(fn: (r) => r["_measurement"] == "Einspeisung")
|> filter(fn: (r) => r["_field"] == "value")
|> last()

result = join(tables: {t1: t1, t2: t2}, on: ["_time"])
|> map(fn: (r) => ({_value: (r._value_t1 - r._value_t2)}))
|> yield(name: "t1_minus_t2")
7

Both queries work on its own when I miss the t1= and t2= in Influx and in Grafana and return two seperate values.
When I use the t1=… this error occurs

“invalid: error in query specification while starting program: this Flux script returns no streaming data. Consider adding a “yield” or invoking streaming functions directly, without performing an assignment”

The result function does not work, because of not using t1 and t2.

In Grafana it is possible to use expressions to “tag” every query and do some math with these expressions, but I don´t get hit how to use it.

Here are screenshots of the expression what I have done so far:

Expressions
Expressions1896×812 108 KB

8

Using Influx Data Explorer (not Grafana), try to get the join to work. There is helper text that will identify the error (or post here if you cannot figure it out). I am sure you are close.

9

When I put in

import "experimental/date/boundaries"
day = boundaries.yesterday()

t1 = from(bucket: "Stromzähler")
|> range(start: -1m)
|> filter(fn: (r) => r["_measurement"] == "Einspeisung")
|> filter(fn: (r) => r["_field"] == "value")

Influx Script will be report this error:

error in query specification while starting program: this Flux script returns no streaming data. Consider adding a "yield" or invoking streaming functions directly, without performing an assignment

When I delete the “t1=” it works and give back a value.

If I put in the whole code from you nothing is displayed and not error occurs only “No result”.

10

Does this work? (I added a yield statement to t1 and t2)

import "experimental/date/boundaries"
day = boundaries.yesterday()

t1 = from(bucket: "Stromzähler")
|> range(start: -1m)
|> filter(fn: (r) => r["_measurement"] == "Einspeisung")
|> filter(fn: (r) => r["_field"] == "value")
|> last()
|> yield(name: "value_t1")

t2 = from(bucket: "Stromzähler")
|> range(start: day.start, stop: day.stop)
|> filter(fn: (r) => r["_measurement"] == "Einspeisung")
|> filter(fn: (r) => r["_field"] == "value")
|> last()
|> yield(name: "value_t2")

result = join(tables: {t1: t1, t2: t2}, on: ["_time"])
|> map(fn: (r) => ({_value: (r._value_t1 - r._value_t2)}))
|> yield(name: "t1_minus_t2")
11

Thanks for the querry:
No error occurs. Result see in the screenshot.
129.4 was the value from yesteday.
The value from today is 129.5 (cloudy and rainy day today), the value is not shown.
And the result is also not shown should be 0.1 for yesterday and today.

Test
Test1051×299 39.6 KB

12

Hi @agentsmith1612
Can you please toggle this switch and then share the screenshot?
image

13

t1 & t2
t1 & t21750×336 48.1 KB

It gives back two values like expected but no calculation takes places.

14

I continiue the question:
I try so solve my problem in grafana and got help in the grafana forum, but not solve it to the end.

So I think about two different ways to solve my difference calculation:
1st: Is it possible to create a task in influxDB that calculate every 15sec this query:

import "timezone"
option location = timezone.location(name: "Europe/Amsterdam")
from(bucket: "Stromzähler")
 |> range(start: today())
 |> filter(fn: (r) => r["_measurement"] == "Einspeisung")
 |> filter(fn: (r) => r["_field"] == "value")
 |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
 |> last()
 |> yield(name: "MoneyReceivedForFeedingToTheGrid")`Preformatted text`

And store this value to seperate measurement?

2nd:
I could performce this calculation in my prior node-red?

What do you think?