Hi all,
what is the best way how to select all data for some variables from the continuous aggregate with time_bucket_gapfill?
What I am trying to do: I have 5min, 30min, 2h, and 1d continuous aggregates from the underlying data, something like
create materialized view gauges_1d
with (timescaledb.continuous) as
select time_bucket(interval '1d', time) as time, variable,
min(value) as min,
avg(value) as avg,
max(value) as max
from gauges group by 1, 2
Now the monitoring system or the monitored device could be down (think systems turned off during night or weekend), so there are gaps in the aggregate data. I would like to retrieve the data with gapfilled time, something like
select time_bucket_gapfill('1d', time),
variable, avg, min, max
from gauges_1d where variable in (...)
group by 1,2
But time_bucket_gapfill needs an explicit start and end time. To select the data from the whole history I would like to use something like
start => (select min(time) from gauges_1d
where variable in (...)),
finish => now()
but this fails with
ERROR: invalid time_bucket_gapfill argument: start must be a simple expression
So, is it possible to select all the available data for some subset of variables with time_bucket_gapfill in a single SQL statement, instead of selecting min(time) first, and use it as a constant afterwards?
Thanks!
-Yenya