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 >= '2025-07-10T08:00:00Z' AND time <= '2025-07-10T10:00:00Z'
  • Copy
  • Fill window

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 >= '2025-07-10T08:00:00Z'
    AND time <= '2025-07-10T10:00:00Z'
GROUP BY _time, room
  • Copy
  • Fill window
_timeroomAVG(home.temp)
2025-07-10T08:00:00ZKitchen21
2025-07-10T08:30:00ZKitchen22
2025-07-10T09:00:00ZKitchen23
2025-07-10T09:30:00ZKitchen22.85
2025-07-10T10:00:00ZKitchen22.7
2025-07-10T08:00:00ZLiving Room21.1
2025-07-10T08:30:00ZLiving Room21.25
2025-07-10T09:00:00ZLiving Room21.4
2025-07-10T09:30:00ZLiving Room21.6
2025-07-10T10:00:00ZLiving Room21.8
SELECT
  date_bin_gapfill(INTERVAL '30 minutes', time) as _time,
  room,
  locf(avg(temp))
FROM home
WHERE
    time >= '2025-07-10T08:00:00Z'
    AND time <= '2025-07-10T10:00:00Z'
GROUP BY _time, room
  • Copy
  • Fill window
_timeroomAVG(home.temp)
2025-07-10T08:00:00ZKitchen21
2025-07-10T08:30:00ZKitchen21
2025-07-10T09:00:00ZKitchen23
2025-07-10T09:30:00ZKitchen23
2025-07-10T10:00:00ZKitchen22.7
2025-07-10T08:00:00ZLiving Room21.1
2025-07-10T08:30:00ZLiving Room21.1
2025-07-10T09:00:00ZLiving Room21.4
2025-07-10T09:30:00ZLiving Room21.4
2025-07-10T10:00:00ZLiving Room21.8

Was this page helpful?

Thank you for your feedback!


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.

Read more

New in InfluxDB 3.2

Key enhancements in InfluxDB 3.2 and the InfluxDB 3 Explorer UI is now generally available.

See the Blog Post

InfluxDB 3.2 is now available for both Core and Enterprise, bringing the general availability of InfluxDB 3 Explorer, a new UI that simplifies how you query, explore, and visualize data. On top of that, InfluxDB 3.2 includes a wide range of performance improvements, feature updates, and bug fixes including automated data retention and more.

For more information, check out: