Computing average cycle duration using influxql

InfluxDB 1 InfluxQL
1

Hi all,

Is there a trick I could use to compute an average cycle time from a series that simply captures the state (on/off) of a boiler?

I know this is possible using flux and monitor.stateChanges, but I have a pure influxql Grafana setup for now and I would rather avoid migrating while I can…

For this series:

0 0 1 1 0 1 0
where points are logged every mn the result should be 90s (one 2mn cycle and one 1mn cycle).

Thanks!

2

Maybe I will have more success with this :slight_smile: I have been trying my first flux query, and I am getting pretty close but I can’t figure out how to wrap things up:

The data has a single “pe_isburning” column with a value of 0 or 1 indicating whether a boiler is running. I am trying to compute the average cycle time in a given time window.

This query :

import "influxdata/influxdb/monitor"
from(bucket: "pellematic/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._field == "pe_isburning")
  |> toString()
  |> rename(columns: {_value: "_level"})
  |> stateDuration(fn: (r) => r._level == "1")
  |> yield()

yields

2022-10-12 08:56:10.  1  1260

2022-10-12 08:57:10.  1  1320

2022-10-12 08:58:10   1  1380

2022-10-12 08:59:10.  0  -1

… and my intention was to follow up with monitor.stateChanges(…) to pick up the “1380” record… however stateChanges picks up the first records of each state; the total I am looking for is in the last record for each state… any suggestion?

[Note: the toString is there because stateChanges seems to require a string value]