Seasonality Detection

Detect and analyze seasonal patterns in your time series data with 11 specialized algorithms.

Function	Description	Category
`ts_detect_seasonality`	Quick boolean seasonality check	Basic
`ts_analyze_seasonality`	Detailed seasonality analysis	Basic
`ts_detect_periods`	Multi-method period detection	Period Detection
`ts_estimate_period_fft`	FFT-based detection	Period Detection
`ts_estimate_period_acf`	Autocorrelation-based detection	Period Detection
`ts_autoperiod`	FFT + ACF hybrid	Period Detection
`ts_cfd_autoperiod`	Detrended variant for trending data	Period Detection
`ts_estimate_period_lomb_scargle`	For irregular sampling	Period Detection
`ts_estimate_period_aic`	Model selection approach	Period Detection
`ts_estimate_period_ssa`	Singular spectrum analysis	Period Detection
`ts_estimate_period_stl`	STL-based detection	Period Detection
`ts_estimate_period_matrix_profile`	Pattern repetition detection	Period Detection
`ts_estimate_period_sazed`	Zero-padded enhanced DFT	Period Detection
`ts_detect_multiple_periods`	Multiple seasonality detection	Period Detection
`ts_seasonal_strength`	Calculate seasonal strength	Analysis
`ts_seasonal_strength_windowed`	Time-varying strength	Analysis
`ts_classify_seasonality`	Classify seasonal patterns	Analysis
`ts_detect_seasonality_changes`	Detect onset/cessation	Analysis
`ts_instantaneous_period`	Time-varying period estimation	Analysis
`ts_detect_amplitude_modulation`	Detect modulation patterns	Analysis

Showing 20 of 20

Basic Detection

ts_detect_seasonality

Quick check for seasonal patterns at a specific period.

ts_detect_seasonality(values DOUBLE[], period INTEGER) → BOOLEAN

Example:

SELECT ts_detect_seasonality(
    LIST(sales ORDER BY date),
    7  -- test for weekly seasonality
) AS is_seasonal
FROM sales_data;

ts_analyze_seasonality

Detailed seasonality analysis with strength metrics and detected periods.

ts_analyze_seasonality(
    table_name VARCHAR,
    group_col VARCHAR,
    date_col COLUMN,
    value_col COLUMN,
    params MAP
) → STRUCT

Returns:

Field	Type	Description
`detected_periods`	INTEGER[]	All detected seasonal periods
`primary_period`	INTEGER	Most significant period
`seasonal_strength`	DOUBLE	Strength of seasonality (0-1)
`trend_strength`	DOUBLE	Strength of trend (0-1)

Example:

SELECT * FROM ts_analyze_seasonality(
    'sales_data',
    NULL,
    date,
    sales,
    MAP{}
);

Period Detection Algorithms

The extension provides 11 specialized algorithms for period detection, each optimized for different data characteristics.

Algorithm Comparison

Method	Speed	Noise Robustness	Best Use Case	Min Observations
FFT	Very Fast	Low	Clean signals	4
ACF	Fast	Medium	Cyclical patterns	4
Autoperiod	Fast	High	General purpose	8
CFD-Autoperiod	Fast	Very High	Trending data	9
Lomb-Scargle	Medium	High	Irregular sampling	4
AIC	Slow	High	Model selection	8
SSA	Medium	Medium	Complex patterns	16
STL	Slow	Medium	Decomposition	16
Matrix Profile	Slow	Very High	Pattern repetition	32
SAZED	Medium	High	Frequency resolution	16
Multi-Period	Medium	High	Multiple seasonalities	8

ts_detect_periods

Multi-method period detection with automatic method selection.

ts_detect_periods(values DOUBLE[]) → STRUCT
ts_detect_periods(values DOUBLE[], method VARCHAR) → STRUCT

Methods: 'fft', 'acf', 'regression', 'multi', 'wavelet', 'auto' (default)

Returns:

STRUCT(
    periods          INTEGER[],     -- Detected periods sorted by strength
    confidences      DOUBLE[],      -- Confidence score for each period (0-1)
    primary_period   INTEGER,       -- Dominant period
    method_used      VARCHAR        -- Method that was used
)

Example:

-- Detect periods using FFT
SELECT ts_detect_periods(LIST(value ORDER BY date), 'fft') AS periods
FROM sales GROUP BY product_id;

-- Default auto-selection
SELECT (ts_detect_periods([1,2,3,4,1,2,3,4,1,2,3,4]::DOUBLE[])).primary_period;
-- Returns: 4

ts_estimate_period_fft

Fast Fourier Transform periodogram analysis.

ts_estimate_period_fft(values DOUBLE[]) → STRUCT

Returns: STRUCT(period, frequency, power, confidence, method)

Best for: Clean signals, fast analysis

Example:

SELECT ts_estimate_period_fft([1,2,3,4,1,2,3,4,1,2,3,4]::DOUBLE[]);
-- Returns: {period: 4.0, frequency: 0.25, power: ..., confidence: ..., method: "fft"}

ts_estimate_period_acf

Autocorrelation function based detection.

ts_estimate_period_acf(values DOUBLE[]) → STRUCT
ts_estimate_period_acf(values DOUBLE[], max_lag INTEGER) → STRUCT

Best for: Cyclical patterns, clear periodicity

ts_autoperiod

Hybrid FFT + ACF approach for robust detection.

ts_autoperiod(values DOUBLE[]) → STRUCT
ts_autoperiod(values DOUBLE[], acf_threshold DOUBLE) → STRUCT

Returns:

STRUCT(
    period         DOUBLE,
    fft_confidence DOUBLE,
    acf_validation DOUBLE,
    detected       BOOLEAN,  -- TRUE if acf_validation > threshold
    method         VARCHAR
)

Best for: General purpose detection

Example:

-- Check if period was confidently detected
SELECT (ts_autoperiod(LIST(value ORDER BY date))).detected
FROM sales GROUP BY product_id;

ts_cfd_autoperiod

Clustered Filtered Detrended variant for trending data.

ts_cfd_autoperiod(values DOUBLE[]) → STRUCT
ts_cfd_autoperiod(values DOUBLE[], acf_threshold DOUBLE) → STRUCT

Best for: Data with trends

Example:

-- Better for data with trends
SELECT ts_cfd_autoperiod([1,3,5,7,2,4,6,8,3,5,7,9]::DOUBLE[]);

ts_estimate_period_lomb_scargle

Lomb-Scargle periodogram for unevenly sampled data.

ts_estimate_period_lomb_scargle(values DOUBLE[]) → STRUCT
ts_estimate_period_lomb_scargle(values DOUBLE[], times DOUBLE[]) → STRUCT
ts_estimate_period_lomb_scargle(values DOUBLE[], times DOUBLE[], min_period DOUBLE, max_period DOUBLE) → STRUCT

Returns:

STRUCT(
    period           DOUBLE,
    frequency        DOUBLE,
    power            DOUBLE,
    false_alarm_prob DOUBLE,  -- Lower = more significant
    method           VARCHAR
)

Best for: Irregular sampling, missing data

Example:

-- For irregularly sampled data
SELECT ts_estimate_period_lomb_scargle(
    [1.0, 2.1, 0.9, 2.0, 1.1]::DOUBLE[],
    [0.0, 0.25, 0.5, 0.75, 1.0]::DOUBLE[]
);

ts_estimate_period_aic

Information criterion based period selection using AIC.

ts_estimate_period_aic(values DOUBLE[]) → STRUCT
ts_estimate_period_aic(values DOUBLE[], min_period DOUBLE, max_period DOUBLE) → STRUCT

Returns:

STRUCT(
    period    DOUBLE,
    aic       DOUBLE,
    bic       DOUBLE,
    rss       DOUBLE,
    r_squared DOUBLE,
    method    VARCHAR
)

Best for: Model selection, statistical rigor

ts_estimate_period_ssa

Singular Spectrum Analysis for complex patterns.

ts_estimate_period_ssa(values DOUBLE[]) → STRUCT
ts_estimate_period_ssa(values DOUBLE[], window_size INTEGER) → STRUCT

Returns:

STRUCT(
    period             DOUBLE,
    variance_explained DOUBLE,
    n_eigenvalues      UBIGINT,
    method             VARCHAR
)

Best for: Complex patterns, noise separation

ts_estimate_period_stl

STL-based period detection maximizing seasonal strength.

ts_estimate_period_stl(values DOUBLE[]) → STRUCT
ts_estimate_period_stl(values DOUBLE[], min_period INTEGER, max_period INTEGER) → STRUCT

Returns:

STRUCT(
    period            DOUBLE,
    seasonal_strength DOUBLE,  -- 0-1, higher = stronger
    trend_strength    DOUBLE,
    method            VARCHAR
)

Strength interpretation: 0.0-0.3 Weak, 0.3-0.6 Moderate, 0.6-0.8 Strong, 0.8-1.0 Very strong

ts_estimate_period_matrix_profile

Matrix Profile based detection for pattern repetition.

ts_estimate_period_matrix_profile(values DOUBLE[]) → STRUCT
ts_estimate_period_matrix_profile(values DOUBLE[], subsequence_length INTEGER) → STRUCT

Returns:

STRUCT(
    period             DOUBLE,
    confidence         DOUBLE,
    n_motifs           UBIGINT,
    subsequence_length UBIGINT,
    method             VARCHAR
)

Best for: Repeating patterns regardless of amplitude

ts_estimate_period_sazed

Spectral Analysis with Zero-padded Enhanced DFT for better frequency resolution.

ts_estimate_period_sazed(values DOUBLE[]) → STRUCT
ts_estimate_period_sazed(values DOUBLE[], padding_factor INTEGER) → STRUCT

Returns: STRUCT(period, power, snr, method)

Best for: Fine frequency resolution

ts_detect_multiple_periods

Iterative detection for multiple seasonal components.

ts_detect_multiple_periods(values DOUBLE[]) → STRUCT
ts_detect_multiple_periods(values DOUBLE[], max_periods INTEGER) → STRUCT
ts_detect_multiple_periods(values DOUBLE[], max_periods INTEGER, min_confidence DOUBLE, min_strength DOUBLE) → STRUCT

Returns:

STRUCT(
    period_values     DOUBLE[],
    confidence_values DOUBLE[],
    strength_values   DOUBLE[],
    amplitude_values  DOUBLE[],
    phase_values      DOUBLE[],
    iteration_values  UBIGINT[],
    n_periods         UBIGINT,
    primary_period    DOUBLE,
    method            VARCHAR
)

Best for: Hourly data (daily + weekly), complex seasonality

Example:

-- Detect multiple seasonalities (e.g., daily and weekly)
SELECT ts_detect_multiple_periods(LIST(value ORDER BY date), 3)
FROM hourly_data;

Seasonal Strength Analysis

ts_seasonal_strength

Calculate seasonal strength using various methods.

ts_seasonal_strength(values DOUBLE[], method VARCHAR) → DOUBLE
ts_seasonal_strength(values DOUBLE[], method VARCHAR, period INTEGER) → DOUBLE

Methods: 'variance', 'spectral', 'wavelet', 'auto'

Returns: DOUBLE in range [0, 1]

Example:

SELECT ts_seasonal_strength([1,2,3,4,1,2,3,4]::DOUBLE[], 'spectral');
-- Returns: 0.95

ts_seasonal_strength_windowed

Time-varying seasonal strength using sliding windows.

ts_seasonal_strength_windowed(values DOUBLE[], window_size INTEGER) → STRUCT
ts_seasonal_strength_windowed(values DOUBLE[], window_size INTEGER, step INTEGER) → STRUCT

Returns:

STRUCT(
    window_starts    UBIGINT[],
    strengths        DOUBLE[],
    mean_strength    DOUBLE,
    min_strength     DOUBLE,
    max_strength     DOUBLE,
    is_stable        BOOLEAN  -- std < 0.1
)

ts_classify_seasonality

Classify the type of seasonal pattern including timing stability and amplitude modulation.

ts_classify_seasonality(values DOUBLE[], period DOUBLE) → STRUCT
ts_classify_seasonality(values DOUBLE[], period DOUBLE, strength_threshold DOUBLE, timing_threshold DOUBLE) → STRUCT

Returns:

STRUCT(
    timing_classification VARCHAR,  -- 'non_seasonal', 'stable_seasonal', 'variable_seasonal', 'intermittent_seasonal'
    modulation_type      VARCHAR,   -- 'non_seasonal', 'stable', 'modulated', 'unknown'
    has_stable_timing    BOOLEAN,
    timing_variability   DOUBLE,
    seasonal_strength    DOUBLE,
    is_seasonal          BOOLEAN,
    cycle_strengths      DOUBLE[],
    weak_seasons         BIGINT[]
)

Example:

SELECT ts_classify_seasonality(LIST(value ORDER BY date), 12) AS classification
FROM sales;

Variants:

ts_classify_seasonality_agg(ts, value, period) - Aggregate function
ts_classify_seasonality_by(source, group_col, date_col, value_col, period) - Table macro

ts_detect_seasonality_changes

Detect when seasonality begins or ends.

ts_detect_seasonality_changes(values DOUBLE[]) → STRUCT
ts_detect_seasonality_changes(values DOUBLE[], threshold DOUBLE) → STRUCT

Returns:

STRUCT(
    change_indices       UBIGINT[],
    change_types         VARCHAR[],  -- 'onset' or 'cessation'
    strength_before      DOUBLE[],
    strength_after       DOUBLE[],
    n_changes            UBIGINT
)

ts_instantaneous_period

Estimate time-varying period using Hilbert transform.

ts_instantaneous_period(values DOUBLE[]) → STRUCT

Returns:

STRUCT(
    periods              DOUBLE[],
    mean_period          DOUBLE,
    period_drift         DOUBLE,  -- Positive = lengthening
    is_stationary        BOOLEAN
)

ts_detect_amplitude_modulation

Detect amplitude modulation in seasonal patterns.

ts_detect_amplitude_modulation(values DOUBLE[]) → STRUCT
ts_detect_amplitude_modulation(values DOUBLE[], period INTEGER) → STRUCT

Returns:

STRUCT(
    has_modulation       BOOLEAN,
    modulation_period    INTEGER,
    modulation_strength  DOUBLE,
    envelope             DOUBLE[],
    is_growing           BOOLEAN
)

Analysis Workflow

-- Step 1: Quick multi-method detection
SELECT ts_detect_periods(LIST(value ORDER BY date), 'auto') AS periods
FROM sales_data;

-- Step 2: Classify seasonality type
SELECT ts_classify_seasonality(LIST(value ORDER BY date), 7) AS classification
FROM daily_sales;

-- Step 3: Check for multiple seasonalities
SELECT ts_detect_multiple_periods(LIST(value ORDER BY date), 3) AS multi
FROM hourly_data;

-- Step 4: Analyze stability over time
SELECT ts_seasonal_strength_windowed(LIST(value ORDER BY date), 52) AS windowed
FROM weekly_sales;

-- Step 5: Use findings to select model
-- Strong stable seasonality → AutoETS, HoltWinters
-- Multiple periods → AutoTBATS, AutoMSTL
-- Variable seasonality → DynamicTheta
-- No seasonality → Naive, SimpleMovingAverage

Interpretation Guide

Metric	Value	Interpretation
`seasonal_strength`	> 0.6	Strong seasonality, use seasonal models
`seasonal_strength`	0.3 - 0.6	Moderate seasonality
`seasonal_strength`	< 0.3	Weak or no seasonality
`trend_strength`	> 0.6	Strong trend, consider trend components
`timing_classification`	stable_seasonal	Consistent peak timing
`timing_classification`	variable_seasonal	Peak timing varies
`modulation_type`	modulated	Seasonal amplitude changes

Basic Detection​

ts_detect_seasonality​

ts_analyze_seasonality​

Period Detection Algorithms​

Algorithm Comparison​

ts_detect_periods​

ts_estimate_period_fft​

ts_estimate_period_acf​

ts_autoperiod​

ts_cfd_autoperiod​

ts_estimate_period_lomb_scargle​

ts_estimate_period_aic​

ts_estimate_period_ssa​

ts_estimate_period_stl​

ts_estimate_period_matrix_profile​

ts_estimate_period_sazed​

ts_detect_multiple_periods​

Seasonal Strength Analysis​

ts_seasonal_strength​

ts_seasonal_strength_windowed​

ts_classify_seasonality​

ts_detect_seasonality_changes​

ts_instantaneous_period​

ts_detect_amplitude_modulation​

Analysis Workflow​

Interpretation Guide​

Basic Detection

ts_detect_seasonality

ts_analyze_seasonality

Period Detection Algorithms

Algorithm Comparison

ts_detect_periods

ts_estimate_period_fft

ts_estimate_period_acf

ts_autoperiod

ts_cfd_autoperiod

ts_estimate_period_lomb_scargle

ts_estimate_period_aic

ts_estimate_period_ssa

ts_estimate_period_stl

ts_estimate_period_matrix_profile

ts_estimate_period_sazed

ts_detect_multiple_periods

Seasonal Strength Analysis

ts_seasonal_strength

ts_seasonal_strength_windowed

ts_classify_seasonality

ts_detect_seasonality_changes

ts_instantaneous_period

ts_detect_amplitude_modulation

Analysis Workflow

Interpretation Guide