Calculate time between 2 events in a series

I am graphing this time series. The upward and downward spikes represent the start and stop of a machine. Is there any way to compute the run time from this series? I’ve looked at the stateduration function but that seems to assume a constant threshold. In this case the time starts when the value goes above +1 and stops when the value goes below -1.

image

  • Running grafana v10.0.3 in a docker container

Welcome @rw950431 to the Grafana forum.

You did not state your datasource, but mentioned StateDuration, so perhaps you are using InfluxDB with Flux? If yes, perhaps this will help.

And from the InfluxDB forum:

Count how long a value is 1 or 0 (on or off)
Calculating total ON or OFF time for a period (day, month, etc.).
Computing duration of a state using events.duration function

Apologies. This is the query that I’m using, using data from a temperature sensor attached to my furnace to try an measure burn time.

from(bucket: “house”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“device_id”] == “esp_3D:B0:14”)
|> filter(fn: (r) => r[“_field”] == “Temperature_3c72”)
|> derivative(unit: 1m)

The complete data looks like this (green line in lower graph is derived from yellow line in top panel)

Using a fixed threshold over-estimates burn time because it counts a portion of the time when the burner has switched off but the unit is still cooling back down.

So what I’m after is a way to measure the time between when the value ticks up sharply and when it ticks down again rather than time above a fixed threshold. This probably doesnt exist but I thought I would ask.

I think you would have to define a custom rate-of-change (i.e. derivative) threshold and use a map function. Something like this (this has not been tested) to get the point in time where you have spiked up. Then use that value in another function to calculate the time between the two events.

import "math"

// Define your threshold value
threshold = 5.0

// Query your time series data
from(bucket: "your-bucket")
  |> range(start: -1h) // Adjust the time range as needed
  |> filter(fn: (r) => r["_measurement"] == "your-measurement" and r["_field"] == "your-field")
  |> derivative(unit: 1s, nonNegative: true, columns: ["_value"]) // Calculate the derivative
  |> map(fn: (r) => ({
    r with
    exceedsThreshold: math.abs(r._value) > threshold,
  }))
  |> filter(fn: (r) => r.exceedsThreshold == true)
  |> yield(name: "exceeds_threshold")

Thanks for all the suggestions but since the data was already being processed by Node-Red before it arrived at grafana I’ve made a simple state machine node that gives me a nice clean signal without too much effort

1 Like