Time-Series Forecasting Database

Problem Being Solved

Root Cause: Traditional databases store time-series data but cannot analyze it:

• Data Movement: Export to Python/R → Train model → Import predictions back
• Pipeline Complexity: Airflow/Prefect orchestration for ML workflows
• Expertise Gap: Requires data scientists separate from database engineers
• Stale Predictions: Models trained on old data, predictions lag reality

HeliosDB Solution: Native Forecasting

SQL-Native Forecasting Interface

-- Create time-series table with forecasting enabled
CREATE TABLE sensor_readings (
    sensor_id UUID,
    timestamp TIMESTAMPTZ,
    temperature FLOAT,
    humidity FLOAT,
    pressure FLOAT,
    PRIMARY KEY (sensor_id, timestamp)
) WITH (
    time_series = true,
    retention = '90 days',
    forecasting_enabled = true
);

-- Simple forecasting: predict next 24 hours
SELECT
    timestamp,
    FORECAST(temperature, horizon := 24, interval := '1 hour') as predicted_temp,
    FORECAST_LOWER(temperature, horizon := 24, confidence := 0.95) as lower_bound,
    FORECAST_UPPER(temperature, horizon := 24, confidence := 0.95) as upper_bound
FROM sensor_readings
WHERE sensor_id = 'sensor-001'
ORDER BY timestamp DESC
LIMIT 1;

-- Advanced: specify algorithm and parameters
SELECT
    timestamp,
    FORECAST(
        temperature,
        algorithm := 'lstm',
        horizon := 168,           -- 1 week ahead
        seasonality := 'daily',
        include_confidence := true
    ) as weekly_forecast
FROM sensor_readings
WHERE sensor_id = 'sensor-001';

-- Anomaly prediction: detect future anomalies
SELECT
    timestamp,
    temperature,
    PREDICT_ANOMALY(temperature, lookahead := 6) as anomaly_probability,
    PREDICT_ANOMALY_CAUSE(temperature) as likely_cause
FROM sensor_readings
WHERE sensor_id = 'sensor-001'
AND timestamp > NOW() - INTERVAL '1 day';

Auto-ML for Time-Series

-- Automatic algorithm selection based on data characteristics
SELECT
    FORECAST_AUTOML(
        target := sales,
        horizon := 30,
        optimize_for := 'accuracy',  -- or 'speed', 'interpretability'
        max_training_time := '5 minutes'
    ) as forecast,
    FORECAST_MODEL_INFO() as model_details
FROM daily_sales
WHERE store_id = 'store-123';

-- Returns: algorithm selected, feature importance, accuracy metrics
-- Model details: {"algorithm": "ensemble", "rmse": 0.042, "features": [...]}

Concrete Examples

Example 1: Retail Demand Forecasting

Company Profile: National retailer with 2,000 stores, 50K SKUs

Challenge: Optimize inventory across stores, reduce stockouts and overstock

-- Create demand forecasting table
CREATE TABLE product_demand (
    store_id VARCHAR(20),
    sku VARCHAR(50),
    date DATE,
    units_sold INT,
    price DECIMAL(10,2),
    promotion BOOLEAN,
    PRIMARY KEY (store_id, sku, date)
) WITH (time_series = true, forecasting_enabled = true);

-- Generate 14-day forecasts for all SKUs at a store
SELECT
    sku,
    date,
    FORECAST(
        units_sold,
        horizon := 14,
        algorithm := 'prophet',
        regressors := ARRAY['price', 'promotion'],
        seasonality := 'weekly'
    ) as predicted_demand,
    FORECAST_CONFIDENCE(units_sold, confidence := 0.90) as confidence_interval
FROM product_demand
WHERE store_id = 'store-1234'
GROUP BY sku
ORDER BY predicted_demand DESC;

-- Real-time reorder alerts
SELECT
    sku,
    current_inventory,
    FORECAST(units_sold, horizon := 7) as week_ahead_demand,
    CASE
        WHEN current_inventory < FORECAST(units_sold, horizon := 7) * 1.2
        THEN 'REORDER'
        ELSE 'OK'
    END as inventory_status
FROM product_demand
JOIN inventory USING (store_id, sku)
WHERE store_id = 'store-1234';

Example 2: IoT Predictive Maintenance

Company Profile: Manufacturing plant with 10,000 sensors

Challenge: Predict equipment failures before they occur

-- Create sensor monitoring table
CREATE TABLE equipment_sensors (
    equipment_id VARCHAR(50),
    sensor_type VARCHAR(20),
    timestamp TIMESTAMPTZ,
    reading FLOAT,
    PRIMARY KEY (equipment_id, sensor_type, timestamp)
) WITH (
    time_series = true,
    forecasting_enabled = true,
    anomaly_detection = true
);

-- Predict equipment health for next 24 hours
SELECT
    equipment_id,
    FORECAST(reading, horizon := 24, interval := '1 hour') as predicted_reading,
    PREDICT_ANOMALY(reading, lookahead := 24) as failure_probability,
    PREDICT_TIME_TO_FAILURE(reading) as estimated_tta
FROM equipment_sensors
WHERE sensor_type = 'vibration'
GROUP BY equipment_id
HAVING PREDICT_ANOMALY(reading, lookahead := 24) > 0.7;

-- Automated maintenance scheduling
SELECT
    equipment_id,
    PREDICT_TIME_TO_FAILURE(reading) as hours_until_failure,
    RECOMMEND_MAINTENANCE_WINDOW(
        failure_time := PREDICT_TIME_TO_FAILURE(reading),
        maintenance_duration := '4 hours',
        shift_schedule := 'shifts'
    ) as recommended_window
FROM equipment_sensors
WHERE PREDICT_ANOMALY(reading, lookahead := 48) > 0.5;

Example 3: Energy Grid Load Forecasting

Company Profile: Regional utility serving 2M customers

Challenge: Balance grid load with 15-minute granularity

-- Create grid load table
CREATE TABLE grid_load (
    region_id VARCHAR(20),
    timestamp TIMESTAMPTZ,
    load_mw FLOAT,
    temperature FLOAT,
    is_holiday BOOLEAN,
    PRIMARY KEY (region_id, timestamp)
) WITH (
    time_series = true,
    forecasting_enabled = true,
    resolution = '15 minutes'
);

-- 24-hour load forecast with weather correlation
SELECT
    timestamp,
    FORECAST(
        load_mw,
        horizon := 96,           -- 24 hours * 4 (15-min intervals)
        algorithm := 'ensemble',
        regressors := ARRAY['temperature', 'is_holiday'],
        seasonality := ARRAY['daily', 'weekly']
    ) as predicted_load,
    FORECAST_DECOMPOSITION(load_mw) as load_components
FROM grid_load
WHERE region_id = 'region-west'
ORDER BY timestamp DESC
LIMIT 1;

-- Peak demand prediction for capacity planning
SELECT
    DATE(timestamp) as date,
    MAX(FORECAST(load_mw, horizon := 96)) as predicted_peak,
    PREDICT_PEAK_TIME(load_mw, horizon := 96) as peak_hour,
    RECOMMEND_GENERATION_MIX(
        predicted_load := FORECAST(load_mw, horizon := 96),
        available_sources := 'generation_capacity'
    ) as generation_plan
FROM grid_load
WHERE region_id = 'region-west'
GROUP BY DATE(timestamp);

Example 4: Financial Transaction Forecasting

Challenge: Forecast transaction volumes for capacity planning

-- Create transaction volume table
CREATE TABLE transaction_volumes (
    merchant_category VARCHAR(50),
    timestamp TIMESTAMPTZ,
    transaction_count BIGINT,
    transaction_value DECIMAL(15,2),
    PRIMARY KEY (merchant_category, timestamp)
) WITH (
    time_series = true,
    forecasting_enabled = true,
    resolution = '1 minute'
);

-- Real-time capacity forecasting
SELECT
    merchant_category,
    CURRENT_TIMESTAMP as forecast_time,
    FORECAST(
        transaction_count,
        horizon := 60,           -- Next 60 minutes
        algorithm := 'lstm',
        update_frequency := 'streaming'
    ) as predicted_tps,
    FORECAST_SPIKE_PROBABILITY(transaction_count, threshold := 2.0) as spike_risk
FROM transaction_volumes
GROUP BY merchant_category
HAVING FORECAST_SPIKE_PROBABILITY(transaction_count, threshold := 2.0) > 0.3;

-- Capacity auto-scaling recommendations
SELECT
    SUM(FORECAST(transaction_count, horizon := 60)) as total_predicted_tps,
    RECOMMEND_CAPACITY(
        predicted_load := SUM(FORECAST(transaction_count, horizon := 60)),
        current_capacity := 100000,
        target_utilization := 0.7
    ) as scaling_recommendation
FROM transaction_volumes;

Conclusion

HeliosDB's native time-series forecasting eliminates the complexity of external ML pipelines while delivering superior performance. Organizations can:

• Eliminate ML Infrastructure: No separate training/serving systems
• Democratize Forecasting: SQL-native interface for any developer
• Improve Accuracy: Ensemble methods with automatic tuning