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!