Aggregate data of a measurement using time range defined in another measurement

[olib]

Hello,

I have 2 types of measurements in a bucket:

• A: contains events with values “Start” and ‘Stop” (it could also be “On/Off”) that define time span of activity
• B: contains data from a sensor, produced continuously (at a higher pace than A)

I would like to extract the data from B which are within the time interval defined by “Start/Stop” in A (and calculate the mean of each time span)

This could be similar to using the aggregateWindow function with _time values of “Star/Stop” events instead of “period”.
Someone already raised the question but for InfluxQL (here)

I have tried many ways to solve this problem …

Thank you

[scott]

@olib Are you trying to query a single event from the A measurement? How are unique events identified? Are stop and stop fields in the A measurement or tags on every point?

[olib]

Thanks for you reply Scott.
Here is a brief description of my data structure.

A measurement (Events):
…
_value=0 _field= event cycle= 11 index=2
_value=1 _field= event cycle= 11 index=3 ( → Event Start)
_value=0 _field= event cycle= 11 index=3 ( → Event End)
_value=1 _field= event cycle= 12 index=1
…

Events are uniquely identified by their cycle / index tags, and _value (1 = Start, 0 = End).

B measurement (Sensor):
…
_value= 1.2 _field=temperature + other tags which are not present in A measurement
…

My goal is to retrieve the B records that are in the timespan defined by Start/End events, of a same cycle/item in A measurement.
(Open to any suggestion if there is a better way to structure A measurement).

I hope it is clearer than my first post.

[scott]

@olib There are a couple of different ways you could do this. I’d suggest creating a custom function that returns the start and stop time of a specific event that you can then use to define the range used in the measurement B query:

eventTime = (cycle, index) => {
  eventsData = from(bucket: "example-bucket")
    |> range(start: -30d)
    |> filter(fn: (r) => r._measurement == "A" and r.cycle == cycle and r.index == index)
    |> group()
    |> sort(columns: ["_time"])
  eventStart = (eventsData |> findRecord(fn: (key) => true, idx: 0))._time
  eventStop = (eventsData |> findRecord(fn: (key) => true, idx: 1))._time
  return {start: eventStart, stop: eventStop}
}

event = eventTime(cycle: "11", index: "3")

from(bucket: "example-bucket")
  |> range(start: event.start, stop: event.stop )
  |> filter(fn: (r) => r._measurement == "B")

[olib]

Thanks a lot @Scott
This is exactly what I was looking for.

[scott]

No problem @olib. Happy to help!