Query with time range calculated on basis of time returned from previous query

:wave: Folks,

We are using version 1.7 and migrating to 2.0. (Ideally looking for a solution that also works on 1.7, but I am sure that Flux has many options).

I want to define just using influxdb and Grafana a time range for a query where the time range is based on the time field of an influxdb (sub) query.

So in the simplest implementation, I want to find say the time of the last(value) FROM
Then use that time to calculate n seconds before and after it for from and to respectively.

In the final implementation, I want to define a Grafana variable with all the times retuned by the query and then generate repeating panels for each time (range).

I hope that that makes sense.

Looking forward to any enlightenment :blush:

Best wishes
Eric

Certainly not possible with InfluxQL. You likely can do this with Flux. I think a simple example of a couple of data points might make this clearer…

Given data like:

I want to do:
XYZ

And the expected result should be:
ABC

@Eric_Smith1 This is totally doable in Flux, and I think 1.7 has a new enough version of Flux to make it work.

The following query uses some timestamp manipulation and string interpolation to generate from and to timestamps for each row base on n seconds added/subtracted to the points _time value. I don’t the exact format you’d need to populate a grafana variable, but the keep() function at the end keeps only the from and to columns in the output.

n = 2

from(bucket: "example-bucket")
  |> range(start: -1w)
  |> filter(fn: (r) => r._measurement == "example-m" and r._field == "value" )
  |> last()
  |> map(fn: (r) => ({ r with
    from: time(v: int(v: r._time) - int(v: duration(v: "${string(v: n)}s"))),
    to: time(v: int(v: r._time) + int(v: duration(v: "${string(v: n)}s")))  
  }))
|> keep(columns: ["from", "to"])