Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 39 ยท Querying: Flux and SQL

Windows, Gap Filling and Pivot

Handle missing data in InfluxDB queries: createEmpty and fill in Flux, fill and linear interpolation in InfluxQL, gap filling in SQL, and pivoting fields into columns.

Time-series data is rarely complete. A sensor battery dies, a network link drops, a service is restarted, and your tidy five-minute chart has holes. How a query treats those holes decides whether a dashboard tells the truth: a missing value drawn as a zero looks like a crash, a missing value silently skipped hides an outage.

This lesson ties together the window, gap and reshaping tools of all three query languages and ends with pivot, the operation that turns InfluxDB's one-row-per-field results into the wide tables most tools expect.

The Flux, InfluxQL and InfluxDB SQL snippets are read-only. The runnable blocks are SQLite and JavaScript stand-ins that perform the same operations on sample data.

Why gaps happen and what they mean

Cause Looks like Right treatment
Collector stopped No points for a while Show a gap; alert on it
Value unchanged and only sent on change No points, value constant Carry the last value forward
Irregular events No points when nothing happens Count as zero
Late-arriving data Gap now, filled later Query a little behind "now"

Before you choose a fill, decide which row of this table you are in. Carry forward is right for state ("door is open"), zero for counts of events, null (a visible break) for sensor metrics where "no data" is itself information, and interpolation only for smooth physical quantities such as temperature.

Windows and empty windows in Flux

aggregateWindow groups rows into windows. By default (createEmpty: true) it emits a row for every window, with a null _value for the empty ones. Set it to false to skip empty windows:

TEXT
from(bucket: "weather")
  |> range(start: -3h)
  |> filter(fn: (r) => r._measurement == "weather" and r._field == "temp")
  |> aggregateWindow(every: 10m, fn: mean, createEmpty: true)
  |> fill(usePrevious: true)       // carry the last value forward
  // |> fill(value: 0.0)            // or use a literal

For smooth interpolation, import the package:

TEXT
import "interpolate"

from(bucket: "weather")
  |> range(start: -3h)
  |> filter(fn: (r) => r._measurement == "weather" and r._field == "temp")
  |> aggregateWindow(every: 10m, fn: mean, createEmpty: true)
  |> interpolate.linear(every: 10m)

InfluxQL

TEXT
SELECT mean("temp") FROM "weather"
WHERE time >= now() - 3h
GROUP BY time(10m) fill(linear)        -- or fill(previous), fill(0), fill(none), fill(null)

SQL in InfluxDB 3

TEXT
SELECT date_bin_gapfill(INTERVAL '10 minutes', time) AS window_start,
       locf(avg(temp)) AS temp               -- or interpolate(avg(temp))
FROM weather
WHERE time >= now() - INTERVAL '3 hours' AND time < now()
GROUP BY window_start

Stand-in: gap filling in portable SQL

Standard SQL has no gap-fill function, so you build a calendar of expected windows with a recursive CTE and LEFT JOIN your data onto it. The carried-forward value uses a correlated subquery. Run it to see all three treatments side by side.

SQL Calendar table, LEFT JOIN and carry-forward (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Windows 20 and 30 have no data: fill_null shows nulls (a gap in a chart), fill_zero shows zeros (misleading for a temperature) and fill_previous repeats 19.1.

Stand-in: linear interpolation

Linear interpolation draws a straight line between the neighbouring known values. Here is the arithmetic in JavaScript, which also shows why interpolation can mislead when the gap is long.

JS Linear interpolation across a gap (stand-in)

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Pivot: from rows per field to columns per field

Flux returns one row per field. A chart or a CSV export usually wants one column per field. pivot rotates the table:

TEXT
from(bucket: "weather")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "weather")
  |> filter(fn: (r) => r._field == "temp" or r._field == "humidity")
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

Before the pivot there are two rows per timestamp (temp and humidity); afterwards one row per timestamp with temp and humidity columns. Pivot after you have reduced the data (range, filters, aggregateWindow): it is expensive on large inputs. If different series have different timestamps, the pivoted result has nulls wherever a field is missing for a time.

In SQL (InfluxDB 3) you get wide rows for free, because a point's fields are already columns. If you need to pivot a tag instead (one column per host), use conditional aggregation, which works in every SQL dialect, including SQLite below.

SQL Pivot a tag into columns with conditional aggregation (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

The row at time 120 has no web-02 value, so that cell is null: the same effect a Flux pivot has on misaligned series.

And the narrow-to-wide transformation written in JavaScript, as Flux does it:

JS pivot(rowKey, columnKey, valueColumn) (stand-in)

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Querying "slightly behind now"

Late data and the last, partial window both distort the newest point of a chart. Two defensive tricks:

  • End your range a little before now (stop: -1m), and
  • Use aggregateWindow(..., timeSrc: "_start") or offset so labels and alignment match your expectation.

For alerting on missing data, use a "deadman" check (Dashboards and Alerts covers it): alert when no point arrived in the last N minutes, rather than relying on a fill value.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A door sensor sends a point only when the door changes state. Which fill fits best for a chart of its state?

    State persists until it changes, so the last known value is the right assumption.
  2. 2. What does aggregateWindow(createEmpty: false) do?

    With createEmpty false, empty windows are skipped instead of returned with null.
  3. 3. What does pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value") produce?

    Pivot widens narrow Flux output so each field becomes a column.
  4. 4. In portable SQL, how do you produce a row for every expected window?

    A calendar table supplies the missing windows; the join adds data where it exists.

Summary

  • Decide what a gap means (collector failure, state, event, late data) before choosing a fill.
  • Flux uses aggregateWindow(createEmpty), fill and interpolate; InfluxQL has fill(...); InfluxDB 3 SQL has date_bin_gapfill with locf and interpolate.
  • Portable SQL fills gaps with a calendar CTE and a LEFT JOIN.
  • pivot turns one-row-per-field results into columns; do it after reducing data, and expect nulls where series do not align.

Next: back to the InfluxDB and Time-series Databases track