How to graph time of day of the first time a value changes to 1?

  • 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.

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).



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")


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.


Best regards,

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!