What Grafana version and what operating system are you using?
Grafana v9.4.7 running as a Home Assistant add-on
What are you trying to achieve?
I have a binary sensor helper in Home Assistant that resets to 0 (off) at midnight each night and at some point in the morning I will trigger an action that sets it to 1 (on). If Home Assistant restarts, the value gets resent to InfluxDB.
and I can group it by 1d, but what I really want is the time each day that it is first set to 1 (if it helps, it should only ever transition from 0 to 1 once in a day). I’m very new to Grafana and queries so I’m not sure how to filter away the 0 values or any additional 1 values, and I don’t know how to remap from those values to the time of day they occurred. I’m not really search what terms to search for this solution either.
Hi @ben184a,
I solved this by first grouping data per day with window() function, then filtering data where value is 1 and after that selecting first data point. First data point is per each day and where value is 1 (since we filtered data that way).
Query:
from(bucket: "test")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "custom")
|> filter(fn: (r) => r["_field"] == "state")
|> window(every: 1d) // group data per day
|> filter(fn: (r) => r["_value"] == 1) // filter only 1 values
|> first() // select first data point (per day where value is 1)
|> group() // ungroup all data so that you get 1 label in legend instead of 1 label per day
|> keep(columns: ["_value", "_time"]) // keep only important columns. I found that without this data is shifted.
|> yield(name: "first_1_value")
Result:
On left you can see table with times when value was set to 1 first time in day. On right there is a graph where dashed line represent Original data and yellow dots represent points where value is set to 1 first time at that day. Note that at first day (4.6.2023.) value went to 1 two times (at 6h and at 18h) but only first time is kept. Table uses same query as in graph.
Thank you for sharing. That looks like it is well on the way to what I’m wanting. Ultimately I’d like a line graph with the time of day, but I imagine making that final step might not be as complicated.
Looking at your query, it looks very different than what I see on my screen.
Okay, looks like I had my InfluxDB setup with InfluxQL. I added a new connection with Flux and now I’m able to reproduce your data filtering with my real data. Now I just need to graph the time of day instead of the value.
Not yet. Life got busy and this is now a lower priority for me. If you figure it out, please post your solution as I am definitely still interested in the answer!
