Time-Series Examples

Time-Series Examples

Version: 6.0

This document provides practical code examples for common time-series use cases in HeliosDB.

---

SQL Examples

Create Time-Series Tables

-- IoT sensor readings table
CREATE TABLE sensor_readings (
    timestamp TIMESTAMPTZ NOT NULL,
    sensor_id VARCHAR(64) NOT NULL,
    facility VARCHAR(32) NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    pressure DOUBLE PRECISION,
    vibration DOUBLE PRECISION
) WITH (
    partition_by = 'HOUR',
    retention = '7 days',
    compression = 'gorilla'
);

-- Create continuous aggregate for hourly averages
CREATE MATERIALIZED VIEW sensor_hourly AS
SELECT
    time_bucket('1 hour', timestamp) AS bucket,
    sensor_id,
    facility,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity,
    AVG(pressure) AS avg_pressure,
    AVG(vibration) AS avg_vibration,
    COUNT(*) AS sample_count
FROM sensor_readings
GROUP BY bucket, sensor_id, facility
WITH (
    continuous = true,
    refresh_interval = '5 minutes'
);

Query Patterns

-- Last hour of raw data
SELECT timestamp, sensor_id, temperature, humidity
FROM sensor_readings
WHERE timestamp > NOW() - INTERVAL '1 hour'
  AND sensor_id = 'sensor-001'
ORDER BY timestamp;

-- Hourly averages for last 24 hours
SELECT
    time_bucket('1 hour', timestamp) AS hour,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp
FROM sensor_readings
WHERE timestamp > NOW() - INTERVAL '24 hours'
  AND sensor_id = 'sensor-001'
GROUP BY hour
ORDER BY hour;

-- Gap filling with linear interpolation
SELECT
    time_bucket_gapfill('5 minutes', timestamp) AS bucket,
    sensor_id,
    interpolate(avg(temperature)) AS temperature
FROM sensor_readings
WHERE timestamp BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 12:00:00'
GROUP BY bucket, sensor_id
ORDER BY bucket, sensor_id;

-- Detect anomalies (values > 3 standard deviations)
WITH stats AS (
    SELECT
        sensor_id,
        AVG(temperature) AS mean,
        STDDEV(temperature) AS stddev
    FROM sensor_readings
    WHERE timestamp > NOW() - INTERVAL '1 day'
    GROUP BY sensor_id
)
SELECT r.timestamp, r.sensor_id, r.temperature,
       (r.temperature - s.mean) / s.stddev AS z_score
FROM sensor_readings r
JOIN stats s ON r.sensor_id = s.sensor_id
WHERE timestamp > NOW() - INTERVAL '1 hour'
  AND ABS(r.temperature - s.mean) > 3 * s.stddev
ORDER BY timestamp;

-- Moving average
SELECT
    timestamp,
    temperature,
    AVG(temperature) OVER (
        ORDER BY timestamp
        ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    ) AS moving_avg_10
FROM sensor_readings
WHERE sensor_id = 'sensor-001'
  AND timestamp > NOW() - INTERVAL '1 hour';

-- Rate of change per second
SELECT
    timestamp,
    temperature,
    (temperature - LAG(temperature) OVER (ORDER BY timestamp)) /
    EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (ORDER BY timestamp)))
    AS rate_per_second
FROM sensor_readings
WHERE sensor_id = 'sensor-001'
  AND timestamp > NOW() - INTERVAL '1 hour';

-- OHLCV bars for financial data
SELECT
    time_bucket('5 minutes', timestamp) AS bucket,
    symbol,
    FIRST(price, timestamp) AS open,
    MAX(price) AS high,
    MIN(price) AS low,
    LAST(price, timestamp) AS close,
    SUM(volume) AS volume
FROM market_ticks
WHERE timestamp > NOW() - INTERVAL '1 day'
  AND symbol = 'AAPL'
GROUP BY bucket, symbol
ORDER BY bucket;

-- Session-based analysis
WITH sessions AS (
    SELECT
        user_id,
        timestamp,
        SUM(CASE
            WHEN timestamp - LAG(timestamp) OVER (
                PARTITION BY user_id ORDER BY timestamp
            ) > INTERVAL '30 minutes'
            THEN 1 ELSE 0
        END) OVER (PARTITION BY user_id ORDER BY timestamp) AS session_id
    FROM user_events
    WHERE timestamp > NOW() - INTERVAL '1 day'
)
SELECT
    user_id,
    session_id,
    MIN(timestamp) AS session_start,
    MAX(timestamp) AS session_end,
    COUNT(*) AS event_count
FROM sessions
GROUP BY user_id, session_id
ORDER BY user_id, session_start;

Retention and Downsampling via SQL

-- Set retention policy
ALTER TABLE sensor_readings
SET RETENTION POLICY '30 days';

-- Create downsampling policy
CREATE DOWNSAMPLING POLICY ON sensor_readings
    EVERY '1 hour'
    WITH AGGREGATION (
        temperature = AVG,
        humidity = AVG,
        pressure = AVG,
        vibration = AVG
    )
    AFTER '7 days'
    RETAIN '365 days';

-- Force immediate compaction
VACUUM (COMPACT) sensor_readings;

---

See Also: README | Quick Start | User Guide