stateDuration, wrong output

Fluxlang
1

I just tried stateDuration function however seems that function sort data according in my case “trigger” and then time before counting state duration change (sort function did not help either, see below).

Am I doing something wrong to calculate duration between state changes (according to time) ?

With sort function query but still sorted by trigger and then time

from(bucket: "test")
  |> range($range)
  |> filter(fn: (r) =>
       r._measurement == "fautest" and
       r._field == "countfld")
  |> drop(columns: ["msgid"])
  |> sort(columns:["_time", "_start"])
  |> stateDuration(fn: (r) => r.trigger == "interval",
       column: "stateDuration",
       unit: 1s
    )

Result

#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,long,string,string,string,long
#group,false,false,true,true,false,false,true,true,true,false
#default,_result,,,,,,,,,
,result,table,_start,_stop,_time,_value,_field,_measurement,trigger,stateDuration
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:27.076Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:31.295Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:45.17Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:49.576Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:56.17Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:19:08.426Z,1,countfld,fautest,cycle,-1
,,1,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:14.732Z,0,countfld,fautest,green,-1
,,1,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:09:13.344Z,0,countfld,fautest,green,-1
,,1,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:12:37.439Z,0,countfld,fautest,green,-1
,,1,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:14:23.002Z,0,countfld,fautest,green,-1
,,1,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:18:35.957Z,0,countfld,fautest,green,-1
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:15:29.745Z,0,countfld,fautest,interval,0
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:15:59.713Z,0,countfld,fautest,interval,29
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:16:29.903Z,0,countfld,fautest,interval,60
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:16:59.839Z,0,countfld,fautest,interval,90
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:17:29.995Z,0,countfld,fautest,interval,120
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:17:59.996Z,0,countfld,fautest,interval,150
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:18:30.277Z,0,countfld,fautest,interval,180
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:19:00.168Z,0,countfld,fautest,interval,210
,,3,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:09:15.359Z,0,countfld,fautest,red,-1
,,3,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:12:36.111Z,0,countfld,fautest,red,-1
,,4,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:25:32.936Z,1,countfld,fautest,reject,-1
,,4,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:25:35.264Z,1,countfld,fautest,reject,-1
,,4,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:25:37.67Z,1,countfld,fautest,reject,-1
,,4,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:28:51.109Z,1,countfld,fautest,reject,-1
,,4,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:36:36.47Z,1,countfld,fautest,reject,-1
,,5,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:16:30.784Z,0,countfld,fautest,yellow,-1

Without sort function same result

from(bucket: "test")
  |> range($range)
  |> filter(fn: (r) =>
       r._measurement == "fautest" and
       r._field == "countfld")
  |> drop(columns: ["msgid"])
  |> stateDuration(fn: (r) => r.trigger == "interval",
       column: "stateDuration",
       unit: 1s
    )

Result

#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,long,string,string,string,long
#group,false,false,true,true,false,false,true,true,true,false
#default,_result,,,,,,,,,
,result,table,_start,_stop,_time,_value,_field,_measurement,trigger,stateDuration
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:27.076Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:31.295Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:45.17Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:49.576Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:56.17Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:19:05.082Z,1,countfld,fautest,cycle,-1
,,0,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:19:08.426Z,1,countfld,fautest,cycle,-1
,,1,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:24:14.732Z,0,countfld,fautest,green,-1
,,1,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:09:13.344Z,0,countfld,fautest,green,-1
,,1,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:12:37.439Z,0,countfld,fautest,green,-1
,,1,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:14:23.002Z,0,countfld,fautest,green,-1
,,1,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:18:35.957Z,0,countfld,fautest,green,-1
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:15:29.745Z,0,countfld,fautest,interval,0
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:15:59.713Z,0,countfld,fautest,interval,29
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:16:29.903Z,0,countfld,fautest,interval,60
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:16:59.839Z,0,countfld,fautest,interval,90
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:17:29.995Z,0,countfld,fautest,interval,120
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:17:59.996Z,0,countfld,fautest,interval,150
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:18:30.277Z,0,countfld,fautest,interval,180
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:19:00.168Z,0,countfld,fautest,interval,210
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:19:30.246Z,0,countfld,fautest,interval,240
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:20:00.324Z,0,countfld,fautest,interval,270
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:20:30.403Z,0,countfld,fautest,interval,300
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:21:00.544Z,0,countfld,fautest,interval,330
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:21:30.482Z,0,countfld,fautest,interval,360
,,2,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:22:00.497Z,0,countfld,fautest,interval,390
,,3,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:09:15.359Z,0,countfld,fautest,red,-1
,,3,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:12:36.111Z,0,countfld,fautest,red,-1
,,4,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T08:25:32.936Z,1,countfld,fautest,reject,-1
,,5,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:14:25.94Z,0,countfld,fautest,yellow,-1
,,5,2020-01-21T08:15:00Z,2020-01-21T09:20:00Z,2020-01-21T09:16:30.784Z,0,countfld,fautest,yellow,-1
3

@salvq trigger is part of the group key, so you actually have multiple tables in your output and the tables happen to be sorted by trigger alphabetically (this isn’t always guaranteed). The actual rows in each table are sorted by _time (the default sorting), and, according to your query, the only table you actually track the state duration of is the table with trigger=interval.

If you look at the annotated CSV output, you have ,result,table,.... table acts as an ID for a table to which the row belongs. So if you have multiple table values in annotated CSV, you have multiple tables.

Are you hoping to get the duration of each state as they happen? Right now, the stateDuration() function uses a predicate function (fn) to determine the state and increment the duration. The problem with this is you have to know the state(s) ahead of time. I think this is something that could be improved upon.

If you are in fact trying to get the state duration of each state as they change over time, you need to create a stream of tables for each state, calculate the state durations for each, then union the streams together and remove trigger from the group key to group all the rows into one table.

Note: I didn’t test this :point_down:

baseData = from(bucket: "test")
    |> range($range)
    |> filter(fn: (r) =>
         r._measurement == "fautest" and
         r._field == "countfld")
    |> drop(columns: ["msgid"])

returnStateStream = (state, tables=<-) => tables
    |> stateDuration(fn: (r) => r.trigger == state)
    |> filter(fn: (r) => r.trigger == state)

cycle    = baseData |> returnStateStream(state: "cycle")
green    = baseData |> returnStateStream(state: "green")
interval = baseData |> returnStateStream(state: "interval")
red      = baseData |> returnStateStream(state: "red")
yellow   = baseData |> returnStateStream(state: "yellow")

union(tables: [cycle, green, interval, red, yellow])
    |> group(mode: "except", columns: ["_time", "_value", "trigger"])
4

@scott you are right, I overlook the table ID the approach works just fine. As usual missing experience and learning while typing & trying.

It is not really state duration question rather record duration between same state…is there a way to reset the counter from record to record (same state) ? See example below…

Adding up approach

from(bucket: "test")
    |> range($range)
    |> filter(fn: (r) =>
         r._measurement == "fautest" and
         r._field == "countfld" and
         r.trigger == "cycle")
|> drop(columns: ["msgid"])
|> stateDuration(fn: (r) => r.trigger == "cycle")

,result,table,_start,_stop,_time,_value,_field,_measurement,trigger,stateDuration
,,0,2020-01-22T12:42:24.097959655Z,2020-01-24T12:42:24.097959655Z,2020-01-23T07:58:31.268Z,1,countfld,fautest,cycle,0
,,0,2020-01-22T12:42:24.097959655Z,2020-01-24T12:42:24.097959655Z,2020-01-23T07:58:40.612Z,1,countfld,fautest,cycle,9
,,0,2020-01-22T12:42:24.097959655Z,2020-01-24T12:42:24.097959655Z,2020-01-23T07:58:44.862Z,1,countfld,fautest,cycle,13
,,0,2020-01-22T12:42:24.097959655Z,2020-01-24T12:42:24.097959655Z,2020-01-23T08:01:14.489Z,1,countfld,fautest,cycle,163

Expected result i.e. reset counter approach for same states

,result,table,_start,_stop,_time,_value,_field,_measurement,trigger,stateDuration
,,0,2020-01-22T12:42:24.097959655Z,2020-01-24T12:42:24.097959655Z,2020-01-23T07:58:31.268Z,1,countfld,fautest,cycle,0
,,0,2020-01-22T12:42:24.097959655Z,2020-01-24T12:42:24.097959655Z,2020-01-23T07:58:40.612Z,1,countfld,fautest,cycle,9
,,0,2020-01-22T12:42:24.097959655Z,2020-01-24T12:42:24.097959655Z,2020-01-23T07:58:44.862Z,1,countfld,fautest,cycle,4
,,0,2020-01-22T12:42:24.097959655Z,2020-01-24T12:42:24.097959655Z,2020-01-23T08:01:14.489Z,1,countfld,fautest,cycle,150
5

Ah, ok. I think what you’re looking for is elapsed(). This calculates the amount of time (in a specified unit) between subsequent points in a table.

6

Great, thank you for guidance.

I was not able to find such a function so I used workaround by applying stateDuration() and difference() functions. Now I just made comparison of approaches (stateDuration / difference vs. elapsed) and results are same but script / query looks easier to understand by applying elapsed() :+1: