Flux's stdlib provides the function StateDuration() which adds a column with an incrementing counter for as long as the data matches the provided predicate (i.e. indicates a specific state).
However, this counter resets when the state changes, so the duration column only tells you how long each instance of the state lasted and because the counter is added to each row, you cannot simply sum() it to calculate the total time spent in that state (say a switch being on)
However, it's possible to calculate the total amount of time spent in a state using the elapsed()function with a map().
This snippet details how to define a set of predicate based states for a stream of data and then calculate the total time spent in each of those states
// Note that elapsed() calculates backwards
// the value in the column elapsed is the number of seconds
// since the last point, not the number of seconds until the
// next
//
// so when manually checking, work bottom-up, not top-down
from(bucket: "mybucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "foo")
// Use map() to add a column indicating the current state
|> map(fn: (r) => ({ r with
state: if r._value > 1000 then
"ON"
else
"OFF"
})
// Use elapsed() to calculate the time between each point
|> elapsed()
// Group by state - add other columns (e.g. host) if you
// need them
|> group(columns: ["state"])
// Calculate the time spent in that duration
|> sum(column: "elapsed")
import "array"
// define some data
rows = [
// Slow
{_time: 2022-09-06T10:00:18Z, _field: "speed", _value: 30},
{_time: 2022-09-06T10:00:19Z, _field: "speed", _value: 35},
// Moderate
{_time: 2022-09-06T10:00:20Z, _field: "speed", _value: 50},
{_time: 2022-09-06T10:00:21Z, _field: "speed", _value: 52},
{_time: 2022-09-06T10:00:22Z, _field: "speed", _value: 55},
{_time: 2022-09-06T10:00:27Z, _field: "speed", _value: 55},
{_time: 2022-09-06T10:00:28Z, _field: "speed", _value: 70},
// Fast
{_time: 2022-09-06T10:00:30Z, _field: "speed", _value: 80},
{_time: 2022-09-06T10:00:35Z, _field: "speed", _value: 85},
{_time: 2022-09-06T10:00:40Z, _field: "speed", _value: 95},
{_time: 2022-09-06T10:00:42Z, _field: "speed", _value: 100},
{_time: 2022-09-06T10:00:43Z, _field: "speed", _value: 105},
{_time: 2022-09-06T10:00:45Z, _field: "speed", _value: 110},
// V fast
{_time: 2022-09-06T10:00:46Z, _field: "speed", _value: 115},
{_time: 2022-09-06T10:00:50Z, _field: "speed", _value: 120},
// Fast
{_time: 2022-09-06T10:00:52Z, _field: "speed", _value: 109},
{_time: 2022-09-06T10:00:53Z, _field: "speed", _value: 102},
{_time: 2022-09-06T10:00:55Z, _field: "speed", _value: 95},
{_time: 2022-09-06T10:00:57Z, _field: "speed", _value: 90},
{_time: 2022-09-06T10:00:59Z, _field: "speed", _value: 85},
{_time: 2022-09-06T10:01:05Z, _field: "speed", _value: 75}
]
// Calculate time spent in each speed grouping
array.from(rows: rows)
|> map(fn: (r) => ({ r with
pace: if r._value <= 40 then
"Slow"
else if r._value > 40 and r._value <= 70 then
"Moderate"
else if r._value > 70 and r._value <= 110 then
"Fast"
else
"Clarkson"
}))
|> elapsed()
|> group(columns: ["pace"])
|> sum(column: "elapsed")
// Result:
//
// 0 5 Clarkson
// 1 32 Fast
// 2 9 Moderate
// 3 1 Slow
// Or, calculate time spent accelerating or decelerating
array.from(rows: rows)
// Calculate difference between each row
|> difference()
// Set a state based on this
|> map(fn: (r) => ({ r with
state: if r._value < 0 then
"decelerating"
else if r._value > 0 then
"accelerating"
else
"stable"
}))
|> elapsed()
|> group(columns: ["state"])
|> sum(column: "elapsed")
// Result:
//
// 0 26 accelerating
// 1 15 decelerating
// 2 5 stable