How to "split" a row representing a machine's runtime (duration) into n rows for representing an hourly runtime?

Fluxlang
, ,
1

Hi,

I’m collecting a machines runtime by recording “start” and “stop” events, each resulting in a single record. I looking for way to transform that type of data so that I can see the daily and hourly runtimes.

I’m currently using events.duration for converting the start / stop events into rows representing the runtime (a duration) and aggregateWindow to aggregate on an hourly basis like so:

import "contrib/tomhollingworth/events"
from(bucket: "Heating")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "burner")
  |> filter(fn: (r) => r["_field"] == "status")
  |> events.duration (unit: 1s, columnName: "duration")
  |> filter(fn: (r) => r["_value"] == "running")
  |> aggregateWindow(column: "duration", every: 1h, fn: sum)

This basically works but has one drawback: when the machine is running for longer than 1 hour, aggregateWindow sees a single record for the long duration and is not able to distribute the runtime according to the time frame of 1 hour.

So my question is: is there a way to “split” a row representing a runtime into multiple rows according to some time frame? E.g. a row representing a duration of 3 and a half hour would end up in 4 individual rows, 3 of them carrying a duration of 1 hour while the 4th row has a remaining duration of 30 minutes.

2

really no one has a clue?

3

Hello @Henning,
What do you mean by “distribute the runtime according to the time frame of 1 hr”? Can you please provide an example with some dummy data please? This will help me help you.
Does using

experimental.fill(usePrevious: true)

Help here at all?

4

Hi @Anaisdg,

i just found another thread that seems to address the same problem: https://community.influxdata.com/t/as-of-operations-and-irregular-spacing-of-data-using-flux/14612

I have an irregular time series which does not produce dense rows, so on windowing many rows have no value. I’ll give it a try tomorrow.

1 Like
5

This is the solution that works for me:

import "experimental/table"
from(bucket: "Heating")
  |> range(start: 2022-02-19T12:00:00.000Z, stop: 2022-02-19T17:00:00.000Z)
  |> filter(fn: (r) => r["_measurement"] == "burner")
  |> filter(fn: (r) => r["_field"] == "status")
  |> window(every: 1s, createEmpty: true)
  |> table.fill()
  |> duplicate(column: "_start", as: "_time")
  |> window(every: inf)
  |> fill(usePrevious: true)
  |> fill(value: "unknown")
  |> map(fn: (r) => ({ _time: r._time, runtime: if r._value == "running" then 1 else 0 }))
  |> aggregateWindow(every: 24h, column: "runtime", fn: sum)