Model / timeseries · All operations

Fill gaps in a time series

A variant of Resample a time series

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

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “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”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Alteryx Designer (1)
  • TS Fillerread 2026-09-27

    Fill gaps in a time series by inserting rows for the missing periods

Polars (1)
  • DataFrame.upsampleread 2026-09-27

    Insert rows so that a time column runs at a regular frequency

RapidMiner Studio (2)
  • Equalize Numerical Indicesread 2026-09-27

    Resample a series with numeric index onto equidistant index values, interpolating

  • Equalize Time Stampsread 2026-09-27

    Resample a series with date-time index onto equidistant time stamps, interpolating

Tableau Prep (1)
  • New Rowsread 2026-09-27

    Generate the missing rows of a numeric or date sequence, filled with null, zero or previous value

Background

In Tableau Prep, the New Rows step generates the missing rows of a sequence of whole numbers or dates, so that data with gaps can be analysed for trends.[1] Interpolation is a method of constructing new data points based on the range of a discrete set of known data points.[2]

When to use it

In Tableau Prep's sales examples, days with no recorded sales get new rows with a quantity of zero, while rows for an unknown daily count of orders get Null.[1] Choose the fill by what an absent row means: a day with no sales is a zero, a missing reading is unknown, and a status that did not change may carry forward. (Meridian's view)

Alteryx's TS Filler macro marks each row it generates with a FlagGeneratedRow value of True, and leaves every other column of a generated row null apart from its date-time column.[3] Keep a flag like that on any filled series, so that later steps can tell observed rows from made ones. (Meridian's view) The TS Filler macro does not account for Daylight Saving Time, so across a spring-forward change it can generate a row for an hour that never occurred.[3]

In Tableau Prep, the start and end values of the New Rows step cannot generate rows outside the current data set.[1] In Tableau Prep, an increment greater than the gap between values generates no new rows.[1] Gaps at the start or end of a series are easy to miss, so build the full calendar from the dates the series should cover, not from the first and last rows it happens to have. (Meridian's view)

In RapidMiner's Equalize Time Stamps operator, the time domain is an important choice, because months have different lengths, yet sales series often need monthly time stamps while sensor data may need microsecond ones.[4]

The method

In Tableau Prep, new rows can be filled with Null, with zero for numbers and Null for text, or with a copy of the previous row's values.[1]

Alteryx's TS Filler macro first truncates each date-time to the chosen interval, so with an hourly interval 1:58 pm becomes 1:00 pm.[3] The TS Filler macro builds its periods from the earliest date-time by adding the chosen increment, so an increment of 5 hours from 1:00 pm gives 1:00 pm, 6:00 pm and 11:00 pm.[3] After the TS Filler macro's rounding, several measurements within one period carry the same period label, though the macro does no summarising itself.[3] The TS Filler macro keeps each row's unaltered date-time in an OriginalDateTime column.[3]

RapidMiner's Equalize Time Stamps operator gives a series new equidistant time stamps and computes the values at them with the same functionality as its Replace Missing Values (Series) operator.[4]

Sources

  1. [1]Fill Gaps in Sequential Data - Tableaudocumentation
  2. [2]Interpolation - Wikipediasecondary source
  3. [3]TS Filler Tooldocumentation
  4. [4]Equalize Time Stamps - Rapidminer Documentationdocumentation