# Fill gaps in a time series

Model / timeseries

A variant of [Resample a time series](https://meridian.online/operations/resample-a-time-series).

Insert rows for missing periods, or interpolate onto evenly spaced time stamps, so the series runs at a regular frequency.

Page: <https://meridian.online/operations/resample-a-time-series/fill-gaps-in-a-time-series>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

insert the missing days of a series and interpolate readings onto evenly spaced times

```sql
-- insert rows: a daily series with days 3, 4, 7 and 8 missing
CREATE TEMP TABLE daily AS SELECT day::DATE AS day, sales FROM (VALUES ('2026-01-01', 5), ('2026-01-02', 7), ('2026-01-05', 6), ('2026-01-06', 9), ('2026-01-09', 4)) t(day, sales);
CREATE TEMP TABLE regular AS
SELECT s.day, d.sales, d.day IS NULL AS inserted
FROM (SELECT unnest(generate_series((SELECT min(day) FROM daily), (SELECT max(day) FROM daily), INTERVAL 1 DAY))::DATE AS day) s LEFT JOIN daily d USING (day);
SELECT * FROM regular ORDER BY day;
-- interpolate: four readings at uneven times, read off linearly on an hourly grid from the reading before and the reading after each hour
CREATE TEMP TABLE readings AS SELECT ts::TIMESTAMP AS ts, v FROM (VALUES ('2026-01-01 00:00', 10.0), ('2026-01-01 00:25', 20.0), ('2026-01-01 01:40', 30.0), ('2026-01-01 03:05', 15.0)) t(ts, v);
CREATE TEMP TABLE grid AS SELECT unnest(generate_series(TIMESTAMP '2026-01-01 00:00', TIMESTAMP '2026-01-01 03:00', INTERVAL 1 HOUR)) AS ts;
CREATE TEMP TABLE hourly AS
SELECT g.ts, CASE WHEN p.ts = n.ts THEN p.v ELSE p.v + (n.v - p.v) * epoch(g.ts - p.ts) / epoch(n.ts - p.ts) END AS v
FROM grid g ASOF JOIN readings p ON g.ts >= p.ts ASOF JOIN readings n ON g.ts <= n.ts;
SELECT * FROM hourly ORDER BY ts;
SELECT CASE WHEN ((SELECT count(*) = 9 AND count(*) FILTER (WHERE inserted) = 4 AND list(day ORDER BY day) FILTER (WHERE inserted) = [DATE '2026-01-03', DATE '2026-01-04', DATE '2026-01-07', DATE '2026-01-08']
    AND count(sales) = 5 AND sum(sales) = 31 FROM regular)
  AND (SELECT count(*) = 4 AND abs(max(v) FILTER (WHERE hour(ts) = 0) - 10) < 1e-12 AND abs(max(v) FILTER (WHERE hour(ts) = 1) - 24.666666667) < 1e-9
      AND abs(max(v) FILTER (WHERE hour(ts) = 2) - 26.470588235) < 1e-9 AND abs(max(v) FILTER (WHERE hour(ts) = 3) - 15.882352941) < 1e-9 FROM hourly)) THEN 'as expected' ELSE error('the daily series should run for 9 days with 4 inserted and the hourly grid should read 10, 24.67, 26.47 and 15.88') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Alteryx Designer: [`TS Filler`](https://help.alteryx.com/current/en/designer/tools/time-series/ts-filler-tool.html) (read 2026-09-27)
- Polars: [`DataFrame.upsample`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27)
- RapidMiner Studio: [`Equalize Numerical Indices`](https://docs.rapidminer.com/latest/studio/operators/modeling/time_series/transformation/equalize_numerical_indices.html) (read 2026-09-27)
- RapidMiner Studio: [`Equalize Time Stamps`](https://docs.rapidminer.com/latest/studio/operators/modeling/time_series/transformation/equalize_time_stamps.html) (read 2026-09-27)
- Tableau Prep: [`New Rows`](https://help.tableau.com/current/prep/en-us/prep_new_rows.htm) (read 2026-09-27)