Sum up of stateduration

Fluxlang
1

Hello,

I am trying to get sum of time when device where off. By using stateDuration I get duration but duration is incremented and I need to sum up only the last values before changing state.

How to sum up how long the state last ? Based on below output the SUM should look like = 14799 + 565 + 244 + 464

from(bucket: "test")
  |> range($range)
  |> filter(fn: (r) =>
    r._measurement == "fautest")
|> group(columns: ["_time", "_value", "trigger", "msgid"], mode: "except")
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn:(r) => ({
     r with
     testa: if r.green == 0 and r.red == 0 and r.yellow == 0 then "OFF"
        else "ON",
  }))
|> stateDuration(fn: (r) => r.testa == "OFF", column: "stateDuration", unit: 1s)

asasass

,result,table,_measurement,_start,_stop,_time,countfld,durationfld,green,red,testa,yellow,stateDuration
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T02:30:25.034Z,0,0,0,0,OFF,0,0
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T02:30:54.925Z,0,0,0,0,OFF,0,29
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T02:31:25.082Z,0,0,0,0,OFF,0,60
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T06:36:04.097Z,0,0,0,0,OFF,0,14739
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T06:36:34.16Z,0,0,0,0,OFF,0,14769
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T06:37:04.176Z,0,0,0,0,OFF,0,14799
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T06:38:34.27Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:15:41.822Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:16:12.87Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:16:41.979Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:16:49.151Z,0,2362253,0,0,OFF,0,0
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:17:13.136Z,0,0,0,0,OFF,0,23
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:17:42.12Z,0,0,0,0,OFF,0,52
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:18:13.152Z,0,0,0,0,OFF,0,84
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:18:42.199Z,0,0,0,0,OFF,0,113
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:19:13.464Z,0,0,0,0,OFF,0,144
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:19:42.386Z,0,0,0,0,OFF,0,173
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:20:13.465Z,0,0,0,0,OFF,0,204
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:20:42.575Z,0,0,0,0,OFF,0,233
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:21:13.591Z,0,0,0,0,OFF,0,264
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:21:42.747Z,0,0,0,0,OFF,0,293
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:22:13.764Z,0,0,0,0,OFF,0,324
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:22:42.873Z,0,0,0,0,OFF,0,353
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:23:14.092Z,0,0,0,0,OFF,0,384
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:23:43.014Z,0,0,0,0,OFF,0,413
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:24:14.076Z,0,0,0,0,OFF,0,444
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:24:43.186Z,0,0,0,0,OFF,0,474
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:25:14.295Z,0,0,0,0,OFF,0,505
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:25:43.452Z,0,0,0,0,OFF,0,534
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:26:14.577Z,0,0,0,0,OFF,0,565
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:30:15.156Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:38:18.146Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:38:51.601Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:39:16.663Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:39:42.819Z,0,544439,0,0,OFF,0,0
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:39:45.632Z,0,0,0,0,OFF,0,2
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:40:16.929Z,0,0,0,0,OFF,0,34
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:40:49.868Z,0,0,0,0,OFF,0,67
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:41:16.978Z,0,0,0,0,OFF,0,94
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:41:47.181Z,0,0,0,0,OFF,0,124
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:42:17.103Z,0,0,0,0,OFF,0,154
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:42:47.293Z,0,0,0,0,OFF,0,184
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:43:17.278Z,0,0,0,0,OFF,0,214
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:43:47.466Z,0,0,0,0,OFF,0,244
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:44:04.919Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:44:17.544Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:44:47.513Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:45:17.748Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:45:40.92Z,0,96313,0,0,OFF,0,0
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:45:47.529Z,0,0,0,0,OFF,0,6
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:46:05.373Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:46:17.748Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T07:46:25.983Z,0,20714,0,0,OFF,0,0
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:46:34.317Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:47:04.41Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:47:34.489Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:48:04.489Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:48:34.645Z,0,0,1,0,ON,0,-1
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:48:51.192Z,0,3029235,0,0,OFF,0,0
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:49:04.848Z,0,0,0,0,OFF,0,13
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:49:34.801Z,0,0,0,0,OFF,0,43
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:50:04.879Z,0,0,0,0,OFF,0,73
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:50:35.036Z,0,0,0,0,OFF,0,103
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:51:05.145Z,0,0,0,0,OFF,0,133
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:51:35.13Z,0,0,0,0,OFF,0,163
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:52:05.208Z,0,0,0,0,OFF,0,194
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:52:35.225Z,0,0,0,0,OFF,0,224
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:53:05.287Z,0,0,0,0,OFF,0,254
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:53:35.271Z,0,0,0,0,OFF,0,284
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:54:05.285Z,0,0,0,0,OFF,0,314
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:54:35.364Z,0,0,0,0,OFF,0,344
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:55:05.318Z,0,0,0,0,OFF,0,374
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:55:35.428Z,0,0,0,0,OFF,0,404
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:56:05.537Z,0,0,0,0,OFF,0,434
,,0,fautest,2020-01-23T02:30:00Z,2020-01-23T16:30:00Z,2020-01-23T14:56:35.522Z,0,0,0,0,OFF,0,464
2

@salvq For this specific use case where you’re just trying to find the total time of states, you can use elapsed to calculate the time between subsequent points, then group points by their “state” column, then run sum on the state column:

from(bucket: "test")
  |> range($range)
  |> filter(fn: (r) =>
      r._measurement == "fautest")
  |> group(columns: ["_time", "_value", "trigger", "msgid"], mode: "except")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn:(r) => ({
       r with
       testa: if r.green == 0 and r.red == 0 and r.yellow == 0 then "OFF"
        else "ON",
  }))
  |> elapsed(unit:1s)
  |> group(columns: ["testa"])
  |> sum(column: "elapsed")
3

Got it scott, working just fine with elapsed :+1:

4

Actually, I don’t believe this is that simple since the elapsed time for a row at OFF which follows a ON state will contain the duration of the PREVIOUS ON state.
So, as it is, the result would be incorrect.

You do get the right result though IF you reverse the time before doing elapsed.

from(bucket: "test")
  |> range($range)
  |> filter(fn: (r) =>
      r._measurement == "fautest")
  |> group(columns: ["_time", "_value", "trigger", "msgid"], mode: "except")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn:(r) => ({
       r with
       testa: if r.green == 0 and r.red == 0 and r.yellow == 0 then "OFF"
        else "ON",
  }))

  // ADD THIS TO MAKE IT WORK
  |> sort(columns: ["_time"], desc: true)

  |> elapsed(unit:1s)

  // AND THIS TO GET POSITIVE VALUES
  |> map(fn: (r) => ({ r with elapsed: -r.elapsed} ))


  |> group(columns: ["testa"])
  |> sum(column: "elapsed")