Fill data with last value before time range

InfluxDB 3
,
1

I am coming from InfluxDB 2 with Flux and try to switch to InfluxDB 3 with SQL and struggle a little bit with the possibilities SQL offers or doesn´t offer. In a Grafana visualisation you often want to fill a gap at the beginning with the last value outside and before the given time range and maybe the gap at the end by continuing the last value inside the time range to the end.

I do not want the data in the timerange to be “time binned” and aggregated to time slots, so i guess the date_bin or similar functions don´t do the job.

Any hint on how to achieve this is appreciated. Thanks.

2

I think I got it with two UNIONS and the usage of selector_last. Not sure if it is efficient, but it seems to work. First select the data from within the time range then union it with the last value from before the time range and then add a row at $__timeTo with the last value before $_timeTo.

SELECT time, value FROM "measurement" WHERE "time" >= $__timeFrom AND "time" <= $__timeTo GROUP BY 1, value 

UNION SELECT selector_last(value, time)['time'] as time, selector_last(value,time)['value'] as value FROM "measurement" WHERE "time" < $__timeFrom 

UNION SELECT $__timeTo as time, selector_last(value,time)['value'] as value FROM "measurement" WHERE "time" < $__timeTo`

Great. Maybe using SQL instead of Flux and I will become friends.

1 Like