Documentation

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 sample data set provided in Get started with InfluxDB tutorial 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 _time, room
_timeroomAVG(home.temp)
2022-01-01T08:00:00ZKitchen21
2022-01-01T08:30:00ZKitchen22
2022-01-01T09:00:00ZKitchen23
2022-01-01T09:30:00ZKitchen22.85
2022-01-01T10:00:00ZKitchen22.7
2022-01-01T08:00:00ZLiving Room21.1
2022-01-01T08:30:00ZLiving Room21.25
2022-01-01T09:00:00ZLiving Room21.4
2022-01-01T09:30:00ZLiving Room21.6
2022-01-01T10:00:00ZLiving Room21.8
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 _time, room
_timeroomAVG(home.temp)
2022-01-01T08:00:00ZKitchen21
2022-01-01T08:30:00ZKitchen21
2022-01-01T09:00:00ZKitchen23
2022-01-01T09:30:00ZKitchen23
2022-01-01T10:00:00ZKitchen22.7
2022-01-01T08:00:00ZLiving Room21.1
2022-01-01T08:30:00ZLiving Room21.1
2022-01-01T09:00:00ZLiving Room21.4
2022-01-01T09:30:00ZLiving Room21.4
2022-01-01T10:00:00ZLiving Room21.8

Was this page helpful?

Thank you for your feedback!


Introducing InfluxDB 3.0

The new core of InfluxDB built with Rust and Apache Arrow. Available today in InfluxDB Cloud Dedicated.

Learn more

State of the InfluxDB Cloud Serverless documentation

The new documentation for InfluxDB Cloud Serverless is a work in progress. We are adding new information and content almost daily. Thank you for your patience!

If there is specific information you’re looking for, please submit a documentation issue.