Fill gaps in data

Use date_bin_gapfill with interpolate or locf to fill gaps of time where no data is returned. Gap-filling SQL queries handle missing data in time series data by filling in gaps with interpolated values or by carrying forward the last available observation.

To fill gaps in data:

1. Use the date_bin_gapfill function to window your data into time-based groups and apply an aggregate function to each window. If no data exists in a window, date_bin_gapfill inserts a new row with the starting timestamp of the window, all columns in the GROUP BY clause populated, and null values for the queried fields.

2. Use either interpolate or locf to fill the inserted null values in the specified column.

   • interpolate: fills null values by interpolating values between non-null values.
   • locf: fills null values by carrying the last observed value forward.

   The expression passed to interpolate or locf must use an aggregate function.

3. Include a WHERE clause that sets upper and lower time bounds. For example:

WHERE time >= '2022-01-01T08:00:00Z' AND time <= '2022-01-01T10:00:00Z'

Example of filling gaps in data

The following examples use the Home sensor sample data to show how to use date_bin_gapfill and the different results of interplate and locf.

SELECT
  date_bin_gapfill(INTERVAL '30 minutes', time) as time,
  room,
  interpolate(avg(temp))
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY 1, room

SELECT
  date_bin_gapfill(INTERVAL '30 minutes', time) as time,
  room,
  locf(avg(temp))
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY 1, room