Histogram of duration per state?

I’m adding one data point to an InfluxDB2 database whenever the power state of a device changes:

power state=low 1712104703000000000
power state=high 1712104920000000000
power state=off 1712104980000000000
power state=high 1712105200000000000

I would like to get a histogram of how long the device stayed in each power state, so in this case three numbers, for example:

off: 220 s
low: 217 s
high: 60 s

There can be other states in the database, not only these three.

The boundary entries can be lost, that doesn’t bother me.

How would I go about this in Flux?

I thought the answer is elapsed():

  |> sort(columns: ["_time"]) // not sure if needed
  |> elapsed(unit: 1s)
  |> keep(columns: ["_value", "elapsed"])
  |> group(columns: ["_value"])
  |> sum(column: "elapsed")
  |> group()

But this counts the elapsed time towards the next state, not the current state:

value | elapsed
----- | -------
high  | 217 s
off   | 60 s
high  | 220 s

Because elapsed() returns the time since the last state together with the new state, the time will be counted for the new state.

So this is not what I’m looking for.

@AndreKR Instead of elapsed(), try events.duration(). I believe it behaves the way you’re looking for.

import "contrib/tomhollingworth/events"

    |> events.duration(unit: 1s, stop: now())
    |> keep(columns: ["_value", "duration"])
    |> group(columns: ["_value"])
    |> sum(column: "duration")
    |> group()
1 Like