ClickHouse WITH FILL and INTERPOLATE: Filling Gaps in Time Series

When you aggregate events into time buckets, ClickHouse only returns buckets that actually contain data. Hours with no events simply don't appear in the result, which leaves gaps in charts and breaks downstream math like running totals. WITH FILL solves this at query time by generating the missing rows, and INTERPOLATE controls what non-ordered columns get in those generated rows.

This guide covers the full WITH FILL syntax (FROM, TO, STEP, STALENESS), how INTERPOLATE works, and practical patterns for counters vs. gauges and per-series gap filling.

The Problem WITH FILL Solves

Consider an hourly aggregation that only has data for three hours of the day:

SELECT
    toStartOfHour(time) AS h,
    count() AS hits
FROM events
WHERE date(time) = '2025-06-12'
GROUP BY h
ORDER BY h;

If hours 02:00, 03:00, and most of the day had no events, those rows are absent from the output. A line chart drawn from this result connects 01:00 directly to 04:00, hiding the fact that activity dropped to zero. Filtering, alerting, and running-total calculations all misbehave on a result with holes in it.

WITH FILL rewrites the result so every expected bucket exists.

Basic Syntax

WITH FILL is a modifier on an ORDER BY expression. The full grammar is:

ORDER BY expr [WITH FILL]
    [FROM const_expr]
    [TO const_expr]
    [STEP const_numeric_expr]
    [STALENESS const_numeric_expr]
    [, ... ]
[INTERPOLATE [ (col [AS expr], ...) ]]

WITH FILL can be applied to ORDER BY columns of numeric types (integer, float, decimal) and Date/DateTime/DateTime64 types.

A minimal hourly fill looks like this:

SELECT
    toStartOfHour(time) AS h,
    count() AS hits
FROM events
WHERE date(time) = '2025-06-12'
GROUP BY h
ORDER BY h ASC WITH FILL STEP toIntervalHour(1);

Every missing hour is now present, with hits = 0 (the default for the aggregate column, since count() of nothing is zero in the generated rows).

FROM, TO, and STEP

These three clauses define the sequence of generated values:

The important consequence of the FROM/TO defaults: if your data starts at 08:00, a bare WITH FILL will not back-fill 00:00–07:00, because the minimum observed value is 08:00. To guarantee a full day regardless of where data starts and ends, set explicit bounds:

SELECT
    toStartOfHour(time) AS h,
    count() AS hits
FROM events
WHERE date(time) = '2025-06-12'
GROUP BY h
ORDER BY h ASC
WITH FILL
    FROM toDateTime('2025-06-12 00:00:00')
    TO   toDateTime('2025-06-13 00:00:00')
    STEP toIntervalHour(1);

STEP with INTERVAL

For date/time columns, STEP accepts an INTERVAL expression, which is what you almost always want. Plain numeric steps are interpreted as days for Date and seconds for DateTime, so STEP 1 on a DateTime column fills every second — rarely the intent. Use the explicit interval form:

-- Fill every 100 milliseconds
ORDER BY bucket ASC WITH FILL STEP toIntervalMillisecond(100)

-- Fill calendar months (irregular interval, handled correctly)
ORDER BY month ASC WITH FILL STEP INTERVAL 1 MONTH

INTERVAL 1 MONTH correctly steps across months of different lengths — a plain numeric step cannot express this.

INTERPOLATE: Controlling Generated Rows

By default, columns that are not part of ORDER BY ... WITH FILL receive their type's default value in generated rows (0 for numbers, empty string for strings). That is correct for counter-style metrics (no events means zero), but wrong for gauge-style metrics and for cumulative columns.

INTERPOLATE applies to any column not participating in ORDER BY WITH FILL. In a generated row, the column is computed from the previous row's value by applying the supplied expression. If no expression is given, the previous value is simply repeated (forward-fill / last-observation-carried-forward).

Forward-fill a gauge metric

A temperature or "current price" reading should carry forward across empty buckets, not drop to zero:

SELECT
    toStartOfHour(time) AS h,
    any(value) AS v
FROM metrics
GROUP BY h
ORDER BY h ASC WITH FILL STEP toIntervalHour(1)
INTERPOLATE ( v AS v );

v AS v (or simply listing v) carries the last known value forward into every filled hour.

Carry forward a running total

INTERPOLATE is essential when a column is a cumulative sum computed with a window function. Without it, gap rows would reset the running total to zero:

SELECT
    toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
    count() AS count,
    sum(count) OVER (ORDER BY bucket) AS cumulative
FROM images
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
    FROM toDateTime64({start:String}, 3)
    TO   toDateTime64({end:String}, 3) + INTERVAL 100 millisecond
    STEP toIntervalMillisecond(100)
INTERPOLATE ( cumulative );

Arithmetic in the interpolation expression

The expression can reference the previous value and transform it. For example, to increment a counter by 1 in each generated row:

SELECT n, inter
FROM some_source
ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5
INTERPOLATE ( inter AS inter + 1 );

Note that INTERPOLATE repeats the previous value with the expression applied. It does not perform true linear interpolation between the surrounding known points — for that, see Linear Interpolation below.

STALENESS: Stop Filling After a Gap

Introduced in ClickHouse 24.11, the STALENESS clause limits how far past real data the fill extends. The query generates rows only until the difference from the previous original row exceeds the staleness threshold. This is useful when you want to bridge small gaps but not invent long stretches of data after a series goes silent:

SELECT
    toStartOfMinute(time) AS m,
    any(value) AS v
FROM sensor_readings
GROUP BY m
ORDER BY m ASC
WITH FILL
    STEP INTERVAL 1 MINUTE
    STALENESS INTERVAL 15 MINUTE
INTERPOLATE ( v AS v );

After a 15-minute silence the fill stops rather than carrying the last reading forward indefinitely. Note: STALENESS cannot be combined with the FROM clause in the same column's WITH FILL.

Filling Per Series (Multiple IDs)

A common pitfall: when a result contains many series (per device, per host, per symbol), a single WITH FILL on the timestamp treats the whole result as one sequence and produces incorrect rows that mix series together. To fill gaps independently per series, put the grouping column first in ORDER BY and apply WITH FILL to the time column. Filling follows the order of fields in ORDER BY, so the time fill restarts whenever the leading column changes:

SELECT
    device_id,
    toStartOfHour(time) AS h,
    count() AS hits
FROM events
GROUP BY device_id, h
ORDER BY
    device_id,
    h ASC WITH FILL
        FROM toDateTime('2025-06-12 00:00:00')
        TO   toDateTime('2025-06-13 00:00:00')
        STEP toIntervalHour(1);

Each device_id gets its own complete 24-bucket timeline. The device_id column is carried into the generated rows automatically because the fill sequence is scoped within each distinct leading-column value.

Linear Interpolation Between Known Points

WITH FILL ... INTERPOLATE does forward-fill, not true linear interpolation. When you need a value that ramps linearly between two surrounding observations, use window functions to locate the bounding points and compute the line yourself:

interpolated = prev_value
    + (current_ts - prev_ts) / (next_ts - prev_ts) * (next_value - prev_value)

The Altinity Knowledge Base documents this pattern using anyLastIf(...) OVER (...) window frames to find the previous and next known values, then applying the formula above. Reach for this only when forward-fill is genuinely insufficient — for most dashboards, WITH FILL plus INTERPOLATE is enough and far cheaper.

Common Issues

• Gaps still appear at the edges. You relied on the default FROM/TO, which are the min/max of observed data. Set explicit FROM and TO to cover the full intended range.
• Filled rows show 0 instead of the last value. That is the default for non-INTERPOLATE columns. Add INTERPOLATE (col) to forward-fill gauge metrics and cumulative columns.
• Invalid WITH FILL expression error. Usually a zero/negative STEP, or a STEP direction that doesn't match FROM→TO. See ClickHouse Invalid WITH FILL expression for the full troubleshooting checklist.
• Per-series fill produces cross-contaminated rows. Put the series key before the time column in ORDER BY so the fill restarts per series.
• STEP 1 on a DateTime fills every second. Numeric steps mean seconds for DateTime and days for Date. Use toIntervalHour(1), INTERVAL 1 MONTH, etc.

Best Practices

1. Always set explicit FROM and TO for dashboards. Defaults follow the data and silently drop edge gaps. Explicit bounds make the output deterministic.
2. Match the fill semantics to the metric type. Counters and rates fill with 0 (default); gauges and cumulative values need INTERPOLATE.
3. Use INTERVAL/toInterval*() for time steps. It is unambiguous and handles irregular intervals like months and years correctly.
4. Fill at query time, not by pre-materializing empty rows. WITH FILL keeps storage clean; you don't insert synthetic zero rows into the table.
5. Apply WITH FILL after aggregation. It operates on the final ordered result, so it fills aggregated buckets, not raw rows.
6. Consider STALENESS (24.11+) for sparse sensor data so you bridge short gaps without fabricating long runs of stale values.

How Pulse Helps

Gap-filling logic is easy to get subtly wrong: a forgotten FROM/TO produces charts that lie about edge periods, and a WITH FILL applied across mixed series quietly corrupts per-entity timelines. Pulse (pulse.support) monitors ClickHouse query patterns and schema in production, surfacing queries that are heavy, error-prone, or returning surprising row counts — exactly the symptoms of misconfigured gap-filling. When a WITH FILL query starts scanning far more buckets than expected or trips INVALID_WITH_FILL_EXPRESSION, Pulse flags it with context so you can correct the bounds before it reaches a dashboard. It is run by ClickHouse experts who can review your time-series query design directly.

Frequently Asked Questions

Q: Does WITH FILL change the data stored in my table?

No. WITH FILL only affects the result of the query that uses it. No rows are written to disk; the generated rows exist only in that result set.

Q: Why are my filled rows zero instead of carrying the previous value forward?

Columns outside ORDER BY ... WITH FILL default to their type's zero/empty value in generated rows. To carry the previous value forward, add INTERPOLATE (your_column) (or INTERPOLATE (your_column AS your_column)).

Q: Can I fill gaps separately for each device or symbol?

Yes. List the grouping column before the time column in ORDER BY and apply WITH FILL to the time column. The fill restarts whenever the leading column changes, giving each series its own complete timeline.

Q: Does INTERPOLATE do real linear interpolation?

No. INTERPOLATE forward-fills: it repeats the previous row's value, optionally transformed by an expression. For true linear interpolation between two surrounding known points, use window functions (anyLastIf over previous/next frames) to compute the line manually.

Q: How do I make sure the timeline starts and ends at fixed times?

Set explicit FROM and TO. Without them, ClickHouse uses the minimum and maximum observed values, so leading and trailing gaps are not filled.

Q: What's the difference between WITH FILL and ASOF JOIN for time series?

WITH FILL generates a regular grid of timestamps within a single result. ASOF JOIN matches each row to the nearest row in another table by an inequality — the "last known value" join. Use WITH FILL to densify one series' time axis; use ASOF JOIN to align two series with mismatched timestamps.