Open gjeusel opened 3 years ago
Hi! Have you looked at https://docs.timescale.com/latest/api#time_bucket_gapfill?
I think time_bucket_gapfill
in a subquery followed by a count(*) filter (where power is null)
+ count(*)
should give you the ratios you want?
Though I suppose that wouldn't work with continuous aggregates, which may be why you're asking more specifically about it.
I was looking for something like this as well. Can be solved by this query:
select date(b), count(*) filter (where a.count is null) from
(select
time_bucket_gapfill('1m', bucket) as b, count(*) as count
from series_aggregegated_by_1m
where bucket between '2024-06-01 00:00:00' and now() and sensor_type = ... group by b)
a group by date(b);
This one groups by day, but you can adjust it as you want.
Is your feature request related to a problem? Please describe.
In the renewable energy area, we want to be conservative on missing values. Meaning, if a turbine didn't send any production for a certain period of time, we want to keep track of it.
This can lead to either missing indexes in the db, or indexes for which a column is set to Null or NaN.
It would be of interest to have a query to retrieve those gaps. The completeness of the timeseries might then be deduced, with the definition being the following ratio:
(nrecords with value) / (nrecords with value + nrecords missing)
A wind turbine production with a 0.95 completness means 5% of the time it was in maintenance / stopped for any other reason.
Describe the solution you'd like
Additional context
Often, the wind turbine captor is sending a value only when it measures a different value compared to previously, with a minimum interval of one minute.
This lead to unevenly-spaced timeseries:
Or viewing it with NaNs:
Hence the sample data used previously.