Summarize Boolean variable per month

Grafana Dashboards
1

  • What Grafana version and what operating system are you using?
    10.0.3, running as Home Assistant add-on

  • What are you trying to achieve?
    For a boolean variable (HA: binary_sensor), summarize for each month how many hours the variable was true.

Note: I’m new to grafana, have been able to build some basic dashboards showing graphs that I find useful. However, figuring out how to do this … I don’t even know where to begin as it seems not a simple query but rather a sequence of steps that needs doing.

Thank you so much for any indications on where to look / how to approach.

2

Welcome @robdejonge

If your datasource is InfluxDB and you are using Flux, see here: Duration of switch ON time in hour_minutes_seconds - #14 by grant2

3

Hi Grant - thank you for the response. I am indeed using InfluxDB as a datasource. Pardon my ignorance, but what do you mean by “and you are using Flux” ?

4

The above should explain the difference.

5

TLDR; InfluxDB has 2 “languages”. Flux a scripting engine, and influxQL an SQL like syntax.

@grant2 and other engineers like me on the industrial world have found Flux “unleashes” the full power of time series databases for complex and specific use cases. influxQL works great if you need something more like a relational database.

1 Like
6

Thanks to @grant2 and @fercasjr for the comments. For my use case, diving into learning another query language is perhaps a bit overkill. Especially given my rudimentary-only knowledge of InfluxDB and Grafana.

I did read through the thread linked to above and tried out that ‘Discrete’ plugin. That displays a “it was in this state for int% of the time selected” under the graph, which I can turn into a (very) rough estimate of time spent in a certain state. It’s a rather manual process, but sometimes those save the most time :slight_smile:

Thanks for your comments!

7

You do not need to use a plugin to accomplish what you want. The question was merely about what datasource and language you are presently using. You stated it was InfluxDB, so therefore you must be using either InfluxQL or Flux. It will be obvious if you share a screenshot of the panel query that you currently have working in Grafana.

8

Thanks, @grant2.

The query was simple:

SELECT “value” FROM “autogen”.“state” WHERE (“entity_id”::tag = ‘living_room_air_conditioner_state’) AND $timeFilter

Using the Discrete plugin, and with 3 queries like above but for different air conditioning units, I got a graph like this:

Screenshot 2023-08-14 at 17.25.13

(This is for a 7-day period)

The ideal step 1 solution would show me the total time in each state 0 or 1 for the selected time period.

9

What are the field names of the data table

10

Apologies for the slow response, @yosiasz. I was away from home, unable to look up the answer to your question.

Although: I am back now and still unable to answer your question. I am trying to query a Home Assistant-created database, but am not sure how to get a list of field names. I’ve tried all sorts of things from this link but could not get anything out of it that made sense to me. As mentioned, I’m only a beginning user of InfluxDB and Grafana.

Thanks for your attempt to try and help me out!

1 Like
11

It’s definitely an Influx question that could be summarized as “How do I get availability % of a given period based on a boolean sensor ?”. You should ask on their forum.

12

From the InfluxDB forum:

Count how long a value is 1 or 0 (on or off)
Calculating total ON or OFF time for a period (day, month, etc.).
Computing duration of a state using events.duration function