- 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.
I am able to graph the values
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.
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).
|> 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")
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.
Pictures with queries and set overrides
Query A is used to show original data (dashed green line).
Query B is used to showed yellow points which represent times at which value was set to 1 first time per each day.
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.
On in text edit mode:
I’m not totally sure how to translate your test bucket query to my entity_id query.
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.
Isn’t that what @ldrascic created above?
If not, can you create a mockup of what you want the graph to look like?
I’ll try to create a mockup later, but what I want is the clock time on the Y axis and on the X axis I want the date without time.
did anyone solve this?
I’m having exactly this issue and don’t find an answer…
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!