How to aggregate a boolean

Grafana Configuration
1

Some background:
I have a home sprinkler system I created, and I publish the state when it changes.

I have these boolean values from the system. It looks like the “value” in influxdb is 1 or 0. I have a “Running” that is true when any of the zones are on. And I have a Zone 0/Zone 1/Zone 2 that is true if that particular zone is on.

I only publish the change in state to mqtt. Home assistant is pulling the data into influxdb for me.

Here is the actual question:

How do I aggregate that into the number of minutes it was running, or the number of minutes it was running in each zone? I just started logging it, and I can see the on or off state with this query:
SELECT last("value") FROM "state" WHERE ("entity_id" = 'sprinklers') AND $timeFilter GROUP BY time(1m) fill(previous)

But how do I make a graph that would show the last 7 days, and tell me how many minutes I ran each zone, or the total number of minutes it was running?

screenshot-2021-06-13-1623641544
screenshot-2021-06-13-16236415442481×1738 221 KB

Thanks for the help.

2

Is this something I should be asking about in influxdb? Or Home assistant forums instead?

3

hi @jeffeb3

not an ideal answer but yes, it might be best to start in the influx forum. If the idea here is that you’ll need to introspect those values by performing calculations on the timestamps, I think it’s possible that you could do something like that using transformations, but it will be cleaner and more performant to do that on the db side. (if I’m getting the gist here…)