Find timerange based on the change of a value

Fluxlang
1

Hi All,

I’m having several measurements wich are in a certain range and change only several time a day. I’m trying to construct a query to find the timerange - like start at ‘timestamp xx’ stop at ‘timestamp yy’ - if the value changes.

My source would look like this:
timestamp, sensor1, value=200
timestamp, sensor1, value=220
timestamp, sensor1, value=200
timestamp, sensor1, value=1000
timestamp, sensor1, value=1050
timestamp, sensor1, value=1000
timestamp, sensor1, value=200
timestamp, sensor1, value=225
timestamp, sensor1, value=200

And I would like to get the starting and stoping timestamp if the value is above or equal to 1000 and also start and stop when it’s below 1000. This state changes several times a day.

I’ve looked through documentation and several tutorials and wasn’t actualy able to find a starting point. Is that even possible or would it be better to do further work the data in something like python. I’m thankfull for any help on this.

Thanks, Sven

2

Hello @Sven_Achtelik,
Does this help?

Alternatively you can use the python client as well

Let me know if you want more assistance writing the query.

3

Hi Anaisdg,

it didn’t really help me. I invested a lot of time to get flux running and I did get results like telling me the level change from on to off or from off to on, but as soon as I set the state change from any to any it fails .The expected result would be the start and end timestamp after the state change happens. So I’m looking at a machine that is turnedeither on or off during the day and I need that start and stop timestamp of the “off” durations to use that in another query.

In a seperate query I want to calculate information based on the timerange that I’m trying to get from the above query.

Is something like that possible ?

Thanks, Sven

4

@Sven_Achtelik I think you could accomplish this with the events.duration() function. It’s a little convoluted, but you’d first have to logically assign a _level of either crit, warn, info, or ok, then use the monitor.stateChangesOnly() function (which depends on those four levels). Then ungroup and sort by time again (since monitor.stateChangesOnly() changes the grouping and sorting). Then with the output of events.duration(), you can calculate the stop time by adding the returned duration to the start time of the state:

import "influxdata/influxdb/monitor"
import "contrib/tomhollingworth/events"

from(bucket: "example-bucket")
  |> range(start: -15m)
  |> filter(fn: (r) => r.field == "sensor1")
  |> map(fn: (r) => ({ r with _level: if r._value >= 1000 then "crit" else "ok" }))
  |> monitor.stateChangesOnly()
  |> group()
  |> sort(columns: ["_time"])  
  |> events.duration(unit: 1ns, stop: now())
  |> map(fn: (r) => ({ 
    state: if r._level == "crit" then "on" else "off",
    start_time: r._time,
    stop_time: time(v: int(v: r._time) + r.duration),
  }))

The resulting output will look something like:

state start_time stop_time
off 02020-12-22T20:48:08Z 2020-12-22T20:48:18Z
on 2020-12-22T20:48:18Z 2020-12-22T20:48:28Z
off 2020-12-22T20:48:28Z 2020-12-22T20:48:58Z
on 2020-12-22T20:48:58Z 2020-12-22T20:49:08Z
off 2020-12-22T20:49:08Z 2020-12-22T20:49:58Z
on 2020-12-22T20:49:58Z 2020-12-22T20:58:27Z
2 Likes