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!


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

InfluxDB v3 enhancements and InfluxDB Clustered is now generally available

New capabilities, including faster query performance and management tooling advance the InfluxDB v3 product line. InfluxDB Clustered is now generally available.

InfluxDB v3 performance and features

The InfluxDB v3 product line has seen significant enhancements in query performance and has made new management tooling available. These enhancements include an operational dashboard to monitor the health of your InfluxDB cluster, single sign-on (SSO) support in InfluxDB Cloud Dedicated, and new management APIs for tokens and databases.

Learn about the new v3 enhancements


InfluxDB Clustered general availability

InfluxDB Clustered is now generally available and gives you the power of InfluxDB v3 in your self-managed stack.

Talk to us about InfluxDB Clustered