Gap Filling

Fill missing timestamps in your time series data.

Function	Description
fill_gaps_by	Fill missing timestamps with NULL values
fill_forward_by	Extend series to target date

Showing 2 of 2

---

anofox_fcst_ts_fill_gaps_by

Populates missing timestamps in time series using a specified frequency interval.

Parameters

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table
group_col	ANY	No	Group column (NULL for single series)
date_col	DATE/TIMESTAMP/INTEGER	Yes	Date column
value_col	DOUBLE	Yes	Value column
frequency	VARCHAR/INTEGER	Yes	Time frequency

Frequency Values:

Type	Examples	Description
Minutes	"30m"	30-minute intervals
Hours	"1h"	Hourly intervals
Days	"1d"	Daily intervals
Weeks	"1w"	Weekly intervals
Months	"1mo"	Monthly intervals
Quarters	"1q"	Quarterly intervals
Years	"1y"	Yearly intervals
Integer	1, 2, 3	For integer-indexed series

Output

Returns table with filled timestamps. Missing values are set to NULL.

Example

-- Fill daily gaps in sales data
SELECT * FROM ts_fill_gaps_by(
    'sales_data',
    NULL,      -- no grouping
    date,
    sales,
    '1d'       -- daily frequency
);

-- Fill gaps for multiple products
SELECT * FROM ts_fill_gaps_by(
    'sales_by_product',
    product_id,
    date,
    sales,
    '1d'
);

---

anofox_fcst_ts_fill_forward_by

Extends series forward to a target date, populating gaps with NULL values.

Parameters

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table
group_col	ANY	No	Group column
date_col	DATE/TIMESTAMP/INTEGER	Yes	Date column
value_col	DOUBLE	Yes	Value column
target_date	DATE/TIMESTAMP/INTEGER	Yes	Target date to extend to
frequency	VARCHAR/INTEGER	Yes	Time frequency

Output

Returns extended table with NULL-filled gaps up to target date.

Example

-- Extend series to end of year
SELECT * FROM ts_fill_forward_by(
    'sales_data',
    NULL,
    date,
    sales,
    '2024-12-31'::DATE,
    '1d'
);

-- Extend multiple products to same target
SELECT * FROM ts_fill_forward_by(
    'sales_by_product',
    product_id,
    date,
    sales,
    '2024-12-31'::DATE,
    '1d'
);

---

Gap Filling Workflow

-- 1. Check for gaps in your data
SELECT
    date,
    LEAD(date) OVER (ORDER BY date) as next_date,
    LEAD(date) OVER (ORDER BY date) - date as gap_days
FROM sales_data
WHERE LEAD(date) OVER (ORDER BY date) - date > 1;

-- 2. Fill gaps
CREATE TABLE sales_filled AS
SELECT * FROM ts_fill_gaps_by(
    'sales_data',
    NULL,
    date,
    sales,
    '1d'
);

-- 3. Impute NULL values (see Imputation section)
CREATE TABLE sales_imputed AS
SELECT * FROM ts_fill_nulls_forward_by(
    'sales_filled',
    NULL,
    date,
    sales
);

---