Calculating Time Durations Between Rows from InfluxDB

Grafana Configuration
,
1

I have a _measurement in InfluxDB that is keeping track of the downtime reason code of a machine. It’s in the format of an integer and it logs every second. I have a flux query currently that returns a table with the timestamp and downtime reason code only when the downtime reason code changes.

The flux query looks like this:
from(bucket: “${Bucket}”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “${Measurement}” and r._field == “CurrentDowntimeReasonCode”)
|> aggregateWindow(every: 1s, fn: last, createEmpty: false)
|> map(fn: (r) => ({ r with lastMachineState: r._value })) // Keep original state
|> difference(columns: [“_value”], nonNegative: false)
|> filter(fn: (r) => exists r._value and r._value != 0.0)
|> drop(columns: [“_value”]) // remove difference value
|> keep(columns: [“_time”, “lastMachineState”])
|> rename(columns: {lastMachineState: “CurrentDowntimeReasonCode”})

and the output looks like this

_time CurrentDowntimeReasonCode
7/1/2025 8:49 9
7/1/2025 8:53 0
7/1/2025 8:56 9
7/1/2025 9:07 3
7/1/2025 9:09 2
7/1/2025 9:14 9
7/1/2025 9:17 3
7/1/2025 9:19 9

What I want is to have a table that has the current timestamp as a column labelled “started” and then the timestamp of the next row in the same row as a column labelled as “ended” and then as well a duration column that is the difference between those 2. The last row will use the current time as the “ended” value. Essentially looking for a table that is “Started/Ended/Duration/Downtime Reason Code”.

I am open to doing this as a combination of a Flux query and Grafana but not sure how to accomplish this!

2

You can try this Solution to resolve your issue
Step:1 Start With your Base Query

data = from(bucket: "${Bucket}")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "${Measurement}" and r._field == "CurrentDowntimeReasonCode")
  |> aggregateWindow(every: 1s, fn: last, createEmpty: false)
  |> map(fn: (r) => ({ r with lastMachineState: r._value }))
  |> difference(columns: ["_value"], nonNegative: false)
  |> filter(fn: (r) => exists r._value and r._value != 0.0)
  |> drop(columns: ["_value"])
  |> keep(columns: ["_time", "lastMachineState"])
  |> rename(columns: {lastMachineState: "CurrentDowntimeReasonCode"})

Step : 2 Create Next Version for Same Table

next = data
  |> rename(columns: {_time: "ended"})
  |> drop(columns: ["CurrentDowntimeReasonCode"])

Step : 3 Join data with next to simulate start and end

import "experimental"

joined = experimental.join(
  tables: {start: data, end: next},
  on: ["_field", "_measurement", "_start", "_stop"],
  method: "inner"
)

Step : 4 Compute duration and clean up columns

|> map(fn: (r) => ({
    started: r._time,
    ended: r.ended,
    reason: r.CurrentDowntimeReasonCode,
    duration: duration(v: r.ended - r._time)
  }))
|> keep(columns: ["started", "ended", "duration", "reason"])

Step : 5 Add a final row with now() as ended
you can append a final row manually if you want to track ongoing states.

Final Output Look Like

image
image699×152 2.1 KB

1 Like
3

hello @jaredcomer35 ,
I am following up to confirm whether the solution provided has successfully resolved your issue. Should you continue to encounter any difficulties or if the issue persists, please feel free to reach out. I am available to assist further to ensure everything functions as intended.
Thanks