Aggregate time of an entry with a certain value

Fluxlang
1

I am looking for a way to sum up the time the inverter of my solar plant has a certain state. I have these status values in an influxdb. I noticed that each entry has a _start and _stop time stamp as well as just a _time date.

image
image1224×276 25.9 KB

So I would have to do a _stop - _start of all entries with a _value == 5 (5 is the one I am looking for meaning “throttled”), aggregated over the selected time frame. While I know WHAT to do, I am clueless about the HOW.

So far, I only managed to show me all entries with the value I want with:

from(bucket: "homeassistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["entity_id"] == "solaredge_i1_status")
  |> filter(fn: (r) => r["_measurement"] == "units")
  |> filter(fn: (r) => r["_field"] == "status_value")
  |> filter(fn: (r) => r._value == 5)

In the end, I just want the minutes that sensor was in state 5.

That’s why I came here in the hopes someone could help me out with a little flux magic. :slight_smile:
So, any help appreciated!

edit:
ok, poking around a bit more I finally was able to ask my search engine the right questions and found.

  |> elapsed(unit:1s)
  |> sum(column: "elapsed")

now all I need is to convert these seconds to hours and I am done.

2

victory bells rung to soon…
just noticed that the _start and _time entries are garbage, which the screenshot already shows. I should have looked at that sooner…
Although I do not understand why elapsed even shows different durations when the _start and _stop entries are all the same?

image
image2991×969 259 KB

3

Hi!

I understand _start and _stop is based on your selection “past 6h” so it means the interval for the query.
What if you count the amount of times the desired value was present and multiply it by your data collection interval? Lets say you collect the data each minute, so if the value was 5 sixty times you can say it was in this state for one hour. The precision will be determined by your collection interval.

Maybe something like:
result = from(bucket: “homeassistant”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“entity_id”] == “solaredge_i1_status”)
|> filter(fn: (r) => r[“_measurement”] == “units”)
|> filter(fn: (r) => r[“_field”] == “status_value”)
|> filter(fn: (r) => r._value == 5)
|> count(column: “_value”)

result
|> map(fn: (r) => ({ _value: r._value * your_collect_interval }))
|> yield(name: “count_multiplied_by_collect_interval”)

Hope it helps

4

thanks for the quick reply. Sadly I can not use that, as I have no control over the intervall. The just saw, that my home assistant does what I want in the preview of the sensor, but as I wanna put it up on my grafana, that will not help me. It seems, that the software calculates the duration in between status changes. So when that 5 finaly goes into a 4, the duration how long it was in 5 is calculated and displayed. Thats what I need. So I would have to find a way to calculate the duration from enty a to the timestamp from entry a+1.

as you can see in the next screenshot, I want a sum of all the purple durations.

image
image1854×768 62 KB

5

Ok.
What if you use elapsed for all data points, so this will create the column elapsed for each value. Then you filter the values equals to 5 and use sum on elapsed?

from(bucket: “homeassistant”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“entity_id”] == “solaredge_i1_status”)
|> filter(fn: (r) => r[“_measurement”] == “units”)
|> filter(fn: (r) => r[“_field”] == “status_value”)

|> elapsed(unit: 1s)
|> filter(fn: (r) => r[“_value”] == 5)
|> sum(column: “elapsed”)
|> yield(name: “result”)