Hello everyone,
I’m using InfluxDB 2.0.7 and I would like to query data between two tags, but I don’t know how to write a flux query. Attached is an image of the case. I have a tag "event" = "start", and a tag "event" = "end", I would like to query the data between the two tags. Assume the bucket is "myBucket", measurement is "myMeasurement", field to query is "myField", could anyone help me?
Thanks a lot!
Hello @mfrice,
Welcome! You bet:
start = from(bucket: "myBucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "myMeasurement")
|> filter(fn: (r) => r["_field"] == "myField")
|> filter(fn: (r) => r["event"] == "start")
|> findRecord(fn: (key) => true, idx: 0)
stop = from(bucket: "myBucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "myMeasurement")
|> filter(fn: (r) => r["_field"] == "myField")
|> filter(fn: (r) => r["event"] == "stop")
|> findRecord(fn: (key) => true, idx: 0)
from(bucket: "myBucket")
|> range(start: start._time, stop: stop._time)
|> filter(fn: (r) => r["_measurement"] == "myMeasurement")
|> filter(fn: (r) => r["_field"] == "myField")
Hello @Anaisdg ,
Thank you very much for the prompt reply. This is really helpful! I have one related question. If I have multiple start tags, how can I query 10 seconds data after each start tag? See the picture below. There are several (not a fixed number) events happened in a period of time with a start tag. Each start tag indicates the start of a 10 seconds event. The interval between two tags are longer than 10 seconds. I would like to query all of them (the green part).
Hello @mfrice,
I don’t think this is possible without outer joins. I’m assuming you want to query for all green sections simultaneously. This is what I would do if they were:
import "experimental"
start_stop = from(bucket: "noaa")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "average_temperature")
|> filter(fn: (r) => r["_field"] == "degrees")
|> filter(fn: (r) => r["location"] == "coyote_creek")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> limit(n: 5)
|> map(fn: (r) => ({ r with my_start: r._time }))
|> map(fn: (r) => ({ r with my_stop: experimental.addDuration(d: 10s, to: r._time) }))
|> keep(columns: ["my_stop","my_start", "_time"])
// |> yield(name: "start_stop")
all = from(bucket: "noaa")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "average_temperature")
|> filter(fn: (r) => r["_field"] == "degrees")
|> filter(fn: (r) => r["location"] == "coyote_creek")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
// |> yield(name: "all")
// if we had outer joins:
// join(tables: {all: all, start_stop: start_stop}, on: ["_time"], method: "outer")
|> filter(fn: (r) => r["_time"] >= "my_start" and r["_time"] <= "my_stop")
Let me forward your question to the Flux team to see if anyone can think of anything.
Additionally, @scott do you have any suggestions here?
Hello @Anaisdg , are there any updates from the Flux team? On the other hand, can we solve the problem by creating a noncontinuous range (a range with several parts) for the green sections, then filter data based on the range? The performance should be better by filtering by range than comparing each timestamp with “my_start” and “my_stop” (suppose we have outer join).
Hello @mfrice,
I submitted an issue with your question. The Flux team will get back to you asap.
Yes absolutely you can create a noncontinuous range, but you’ll have to:
Hi @Anaisdg , thanks for your explanation! I’ll wait for a response of the Flux team to see if they have a better idea.
