Filter by preceding row value

InfluxDB 2
1

So I have a table:

time| state
1    | "on"
2    | "off"
5    | "on"
19  | "off"

And I want to figure out how long the “on” state has been.

The stateDuration() function only works for consecutive points with the same state so that doesn’t work out here.

The elapsed() function does give me the time elapsed between the two states. but then the next step is filtering so I only get to see how long it was in “on” state. The problem is: elapsed() gives the time passed compared to the previous datapoint. So if I filter for “on” I actually get the results for “off” and vice versa. Which would be fine but my real world case has more than 1 state…

So my question is:

Does anyone know how to achieve this? Intuitively I’d like to use a filter that filters by the preceding value. not the current one. But as far as i’m aware this is not possible.

Any ideas? I’m also open to completely different setups, as long as it works!

2

I think the reply to this question will help you:
Query for gap start and end time - General - InfluxData Community
What you should do is sort it in reverse time order, then apply elapsed, filter, and sort in time order. Applying elapsed will give you the time from off to the on that preceded it, so that each on has the time from itself to the next off.