Flux: Calculate for how long was a field value not zero

InfluxDB 2
, ,
1

I want to calculate the working time of a component. The speed signal is available in an InfluxDB. So I need a query that calculates the sum of time span in which the speed is not zero.

With this I filter the data. But I don’t know how to get the timespan.

from(bucket: "${bucket}")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "${measurement}"
    and r._field == "Speed"
    and r._value > 0
  )
2

Hello @Nick135,
Have you tried using the stateduration function?

3

Hello @Anaisdg,
this function sound exactly what I need, but I get the wrong result.

from(bucket: "${bucket}")
  |> range(start:2022-12-29T09:00:00Z, stop:2023-01-23T12:30:00Z)
  |> filter(fn: (r) =>
    r._measurement == "${measurement}"
    and r._field == "Speed"
  )
  |> stateDuration(fn: (r) =>  r._value > 0)
  |> filter(fn: (r) => r["stateDuration"] > 0)
  |> sum(column: "stateDuration")

Total Time Range is 26 days and 3.5h
The result of the function is 2754949 => 31.89h
If I use ms as unit I get a few more hours extra

4

@Nick135

Have you seen this thread or this thread? Both are similar to what you are wanting to do.

5

@grant1
The result is better but not good.

If I sum “_value” == 0 and “_value” > 0 the result is 599.5h => 24,98 days
not 26,14 days.

from(bucket: "${bucket}")
  |> range(start:2022-12-29T09:00:00Z, stop:2023-01-23T12:30:00Z)
  |> filter(fn: (r) =>
    r._measurement == "${measurement}"
    and r._field == "Speed"
  )
  |> elapsed(unit: 1ms)
  |> map(fn: (r) => ({ r with elapsedFloat: float(v: r.elapsed)/3600000.0 }))
  |> filter(fn: (r) => r["_value"] > 0)
  |> sum(column: "elapsedFloat")
6

Does anyone else have an idea how to solve this problem?
Is the problem possibly the precision of the float data type?