How to get an average around an event?

Grafana InfluxDB
1

Using Grafana Cloud w. Influxdb3 (cloud), so I can use either SQL or InfluxQL, but not Flux.

I want to find the average value of a field (“distance”) during a one-minute period before the field “extruder_target” gets a non-zero value. How can I do that?

I’ve tried setting a template variable to the time value of the event, but it seems I cannot. I tried setting a template variable with:

SELECT time as timestamp
FROM gantry
WHERE extruder_target > 0
AND time >= $__timeFrom AND time <= $__timeTo
ORDER BY time ASC
LIMIT 1

and then using that variable, but it seems not to get populated:

SELECT distance, time FROM "gantry" WHERE  time > $__timeFrom and time < $heatingStart

flightsql: rpc error: code = InvalidArgument desc = Error while planning query: SQL error: ParserError("Expected: an expression:, found: EOF")

I do not need to use a template variable, any solution would be ok.

2

I would create a task in InfluxDB for that. I.e. check with requred period if extruder_target is greater than zero and save required data into another bucket.

3

Thanks, that might work for some use cases. In my case I would like to show the subsequent values as a difference to this value, and since the influxdb task might not be executed right away, this might not be possible. Also, if I want to change the averaging period, it seems like I would need to go through the whole influxdb data. So something that would be immediately usable and dynamic would be nice.