Gap Filling

Fill missing timestamps in your time series data.

Function	Description
fill_gaps	Fill missing timestamps with NULL values
fill_gaps_operator	High-performance C++ gap filling
fill_forward	Extend series to target date

Showing 3 of 3

---

anofox_fcst_ts_fill_gaps

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 anofox_fcst_ts_fill_gaps(
    'sales_data',
    NULL,      -- no grouping
    date,
    sales,
    '1d'       -- daily frequency
);

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

---

anofox_fcst_ts_fill_gaps_operator

Native C++ high-performance implementation for gap filling. 6-258x faster than SQL version depending on dataset size.

Parameters

Parameter	Type	Required	Description
input_table	TABLE	Yes	Input table (direct table reference)
group_col	VARCHAR	No	Group column name
date_col	VARCHAR	Yes	Date column name
value_col	VARCHAR	Yes	Value column name
frequency	VARCHAR/INTEGER	Yes	Time frequency

Output

Returns table with missing timestamps filled as NULL.

Example

-- High-performance gap filling
SELECT * FROM anofox_fcst_ts_fill_gaps_operator(
    (SELECT * FROM sales_data),
    NULL,
    'date',
    'sales',
    '1d'
);

-- With grouping
SELECT * FROM anofox_fcst_ts_fill_gaps_operator(
    (SELECT * FROM sales_by_product),
    'product_id',
    'date',
    'sales',
    '1d'
);

Performance: Uses efficient O(1) hash set lookups for gap detection.

---

anofox_fcst_ts_fill_forward

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 anofox_fcst_ts_fill_forward(
    'sales_data',
    NULL,
    date,
    sales,
    '2024-12-31'::DATE,
    '1d'
);

-- Extend multiple products to same target
SELECT * FROM anofox_fcst_ts_fill_forward(
    '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 anofox_fcst_ts_fill_gaps(
    'sales_data',
    NULL,
    date,
    sales,
    '1d'
);

-- 3. Impute NULL values (see Imputation section)
SELECT * FROM anofox_fcst_ts_fill_nulls_forward(
    'sales_filled',
    NULL,
    date,
    sales
);

---