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
_time room AVG(home.temp)
2022-01-01T08:00:00Z Kitchen 21
2022-01-01T08:30:00Z Kitchen 22
2022-01-01T09:00:00Z Kitchen 23
2022-01-01T09:30:00Z Kitchen 22.85
2022-01-01T10:00:00Z Kitchen 22.7
2022-01-01T08:00:00Z Living Room 21.1
2022-01-01T08:30:00Z Living Room 21.25
2022-01-01T09:00:00Z Living Room 21.4
2022-01-01T09:30:00Z Living Room 21.6
2022-01-01T10:00:00Z Living Room 21.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
_time room AVG(home.temp)
2022-01-01T08:00:00Z Kitchen 21
2022-01-01T08:30:00Z Kitchen 21
2022-01-01T09:00:00Z Kitchen 23
2022-01-01T09:30:00Z Kitchen 23
2022-01-01T10:00:00Z Kitchen 22.7
2022-01-01T08:00:00Z Living Room 21.1
2022-01-01T08:30:00Z Living Room 21.1
2022-01-01T09:00:00Z Living Room 21.4
2022-01-01T09:30:00Z Living Room 21.4
2022-01-01T10:00:00Z Living Room 21.8

Was this page helpful?

Thank you for your feedback!


Introducing InfluxDB Clustered

A highly available InfluxDB 3.0 cluster on your own infrastructure.

InfluxDB Clustered is a highly available InfluxDB 3.0 cluster built for high write and query workloads on your own infrastructure.

InfluxDB Clustered is currently in limited availability and is only available to a limited group of InfluxData customers. If interested in being part of the limited access group, please contact the InfluxData Sales team.

Learn more
Contact InfluxData Sales

The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Flux is going into maintenance mode and will not be supported in InfluxDB 3.0. This was a decision based on the broad demand for SQL and the continued growth and adoption of InfluxQL. We are continuing to support Flux for users in 1.x and 2.x so you can continue using it with no changes to your code. If you are interested in transitioning to InfluxDB 3.0 and want to future-proof your code, we suggest using InfluxQL.

For information about the future of Flux, see the following: