Query to capture batch intervals (start or stop) for batch state

InfluxDB 2
, , ,
1

I’m looking for a way to create a continuous task query that captures batch start and stop times. I’ve tried doing this with state duration but don’t get quite what I need. Here is the rough outline of the use case and problems:

Use case: IoT sensor data that is logged in sub-second intervals when equipment is running/on usually during long intervals during daylight (ie. 4-8 hours and 1-2 times a day). But the equipment can be shut down for long periods of times. Am trying to build a query that captures these “batches” and logs them to a bucket.

Example batch iot data:

(batch 1, day 1)

  • measurement1, temp: 58, xx/dd/yyy: 08:00:200

  • measurement1, temp, 58.5 xx/dd/yyyy: 08:00:405
    …

  • measurement1, temp, 58.7 xx/dd/yyyy:12:00:000

    <gap greater than 15 minutes>

    (batch 2 - day 1)

  • measurement1, temp: 58, xx/dd/yyyy: 13:00:200

  • measurement1, temp, 58.5 xx/dd/yyyy: 13:00:405
    …

  • measurement1, temp, 58.7 xx/dd/yyyy:16:00:000

batch 3 - day 2

  • measurement1, temp: 58, xx/dd+1/yyyy: 08:00:200
  • measurement1, temp, 58.5 xx/dd+1/yyyy: 08:00:405
    …
  • measurement1, temp, 58.7 xx/dd+1/yyyy:16:00:000

query result:

batch-<xx/dd/yyyy>-1 start: : 08:00 (closest 1 minute window)
batch-<xx/dd/yyyy-1 stop: : 12:00 (closest 1 minute window)
batch-<xx/dd/yyyy>-2 start: : 13:00 (closest 1 minute window)
batch-<xx/dd/yyyy>-2 stop: : 16:00 (closest 1 minute window)
batch-<xx/dd+1/yyyy>-1 start: <date1+1>: 08:00 (closest 1 minute window)
batch-<xx/dd+1/yyyy>-1 stop: <date1+1>: 16:00 (closest 1 minute window)

I’ve tried stateDuration but I’m finding this really just creates a continuous window of state and duration. Possibly getting first and last record of that query? But, also not sure how to break a table into a write record in a flux query. Please excuse my learning curve as I’m new to flux/influx.

This gets me the continuous log of state on or off with duration key but not to a final batch record.

from(bucket: "bucket")
  |> range(start: today())
  
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
  |> stateDuration(fn: (r) => exists r._value, column: dur_key, unit: 1m)  //proves only that system is on, not moving.
  |> drop(columns: [ "_value"])
  |> rename(columns: {"stateDuration_m": "_value"})
  |> set(key: "_field", value: dur_key)
  |> yield(name: "state")