Timescaledb: gap filling: maximum length

[zinji]

Does the gap filling functions have option to limit the size of gaps being filled by setting maximum length of gap in number of time steps?

[svenklemm]

@zinji it currently does not but that's an interesting idea, whats the use case you had in mind for this functionality?

[zinji]

@svenklemm this functionality is highly useful when computing stats on the timeseries data such as mean daily or monthly temperature; dality rainfall totals etc.
usecase: From hourly rainfall totals, i want to compute daily rainfall totals to 9:00 am using gap filling to fill any gaps less that 4hrs and any gaps greater than 4hrs are left null.

[moodgorning]

I concur, we have a very similar use case at hand that would benefit very much from this. We have sensor readings that usually come in every 15 minutes. So we are ok with interpolating 1-2 missing values, but don't want to go further than that. Anything more would be treated as a gap in the data, i.e. NULL

[moodgorning]

I think I managed to achieve this through the following query:
`CREATE TABLE timestamps (
ts timestamp,
value numeric
);

INSERT INTO timestamps VALUES
('2015-05-01 12:00:00.0',1),
('2015-05-01 12:15:00.0',2),
('2015-05-01 12:30:00.0',3),
('2015-05-01 12:45:00.0',4),
('2015-05-01 13:00:00.0',5),
('2015-05-01 13:15:00.0',6),
('2015-05-01 13:30:00.0',7),
('2015-05-01 13:45:00.0',8),
('2015-05-01 14:00:00.0',9),
('2015-05-01 14:15:00.0',10),
('2015-05-01 15:00:00.0',7),
('2015-05-01 15:15:00.0',8),
('2015-05-01 15:30:00.0',9),
('2015-05-01 15:45:00.0',10);

WITH interpolated AS (
WITH deltas AS (
SELECT ts,
value,
extract(epoch from ts::timestamp) - lag(extract(epoch from ts), -1) OVER (ORDER BY ts) delta
FROM raw_measurements
ORDER BY ts
)
SELECT time_bucket_gapfill(
'5 minutes', ts,
start => '2015-05-01 11:53:00',
finish => '2015-05-01 17:00:00') AS minutes_5,
interpolate(avg(value)) AS avg_val,
locf(last(delta,ts)) AS locf_delta,
last(delta,ts) AS deltaActual
FROM deltas
GROUP BY minutes_5
)
SELECT * from interpolated WHERE interpolated.locf_delta >= -%s OR NOT deltaActual IS NULL;`
Not the most elegant way and I'm sure theres lots of room for improvement, but the general idea is to use interpolate over the whole dataset and generate a new column with the time to the next measurement which can then be used to drop all interpolations that were over a gap more than x seconds (900 in this example). This will not fill the gap with NULL values, but you could probably achieve that by running another time_bucket_gapfill over the current result.

[svenklemm]

@zinji ok so you would want gapfill to still produce the row with a timestamp but the locf/interpolate column would have NULL values

[zinji]

@svenklemm Yes, the query output should output the applicable timesteps with null values depending on the time bucket used.

[moodgorning]

Any update on this? This would be a really useful feature to have.

[iam4x]

I'm looking for this as well 🥺