Duration and order of each state

Fluxlang
1

Hello,
I have a measurement of integer data like this, there can be several lines with the same value, but sometimes also only one. Like this:

_measurement  _time                  _field  _value  
test          2021-01-01T00:00:00Z   state	 1	     
test          2021-01-01T00:00:10Z   state	 1 	     
test          2021-01-01T00:00:20Z   state	 2 	     
test          2021-01-01T00:00:30Z   state 	 3 	     	    
test          2021-01-01T00:00:50Z   state	 1 	     
test          2021-01-01T00:00:55Z   state	 2

Now I want to get a table which shows me which state is how long active an in which order they occure:

_measurement  _time                  _field  _value _duration 
test          2021-01-01T00:00:00Z   state	 1	     20
test          2021-01-01T00:00:20Z   state	 2 	     10
test          2021-01-01T00:00:30Z   state 	 3 	     10   
test          2021-01-01T00:00:50Z   state	 1 	     20
test          2021-01-01T00:00:55Z   state	 2	     5

Is this possible with a flux query?
Thank You

2

Welcome @Tesla2k

Have you tried this function?

3

Thank You, this brought me quite close to the solution, but one thing is still wrong. My output example in my initial post has also one error. The duration is off by one row, this is how it should look like:

_measurement  _time                  _field  _value _duration 
test          2021-01-01T00:00:00Z   state	 1	     20
test          2021-01-01T00:00:20Z   state	 2 	     10
test          2021-01-01T00:00:30Z   state 	 3 	     20
test          2021-01-01T00:00:50Z   state	 1 	     5
test          2021-01-01T00:00:55Z   state	 2

I now use this query, which has two problems:

  1. The first two rows are missing
  2. I am one off with the elapsed row state 3 lasts for 20s not 10s
from(bucket: "test")
  |> range(start: -10y, stop: now())
  |> filter(fn: (r) => r["_field"] == "state")  
  |> duplicate(column: "_value", as: "diff")  
  |> difference(keepFirst:false, initialZero:false, nonNegative: false, columns: ["diff"]) 
  |> filter(fn: (r) => r["diff"] !=0)  
  |> elapsed(unit: 1ms, timeColumn: "_time")  
  |> drop(columns: ["_start", "_stop","_measurement","_field","diff"])

The output is:

_value	_time                       elapsed
3	    2020-12-31T23:00:30.000Z    10000
1	    2020-12-31T23:00:50.000Z	20000
2	    2020-12-31T23:00:55.000Z	5000
4

I fixed the seconds problem by using events.duration instead of elapsed

import "contrib/tomhollingworth/events"

from(bucket: "test")
  |> range(start: -10y, stop: now())
  |> filter(fn: (r) => r["_field"] == "state")  
  |> duplicate(column: "_value", as: "diff")  
  |> difference(keepFirst:false, initialZero:false, nonNegative: false, columns: ["diff"]) 
  |> filter(fn: (r) => r["diff"] !=0)    
  |> events.duration(unit: 1ms,columnName: "duration", timeColumn: "_time")
  |> drop(columns: ["_start", "_stop","_measurement","_field","diff"])
2 Likes
5

Awesome. Please mark the above post as “Solution” so others can find it in the future.