Time-Series Databases

-- Install extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Create a regular table
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    battery_level INTEGER
);

-- Convert to hypertable (partitioned by time)
SELECT create_hypertable('sensor_data', 'time');

-- Insert data (works like regular PostgreSQL)
INSERT INTO sensor_data VALUES
    (NOW(), 1, 72.5, 45.0, 85),
    (NOW() - INTERVAL '1 hour', 1, 71.2, 46.0, 84);

-- Query with time bucketing
SELECT
    time_bucket('1 hour', time) AS hour,
    AVG(temperature) AS avg_temp,
    MAX(humidity) AS max_humidity
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

# Write data point (line protocol)
curl -XPOST 'http://localhost:8086/write?db=metrics' \
  -d 'cpu,host=server01,region=us-west usage_idle=82.3,usage_user=15.1 1718450000000000000'

# Query with InfluxQL
curl -G 'http://localhost:8086/query?db=metrics' \
  --data-urlencode 'q=SELECT mean("usage_idle") FROM "cpu" WHERE time > now() - 1h GROUP BY time(5m)'

# Query with Flux (newer, more powerful)
curl -XPOST 'http://localhost:8086/api/v2/query' \
  -d 'from(bucket: "metrics")
    |> range(start: -1h)
    |> filter(fn: (r) => r._measurement == "cpu")
    |> aggregateWindow(every: 5m, fn: mean)'

-- Drop data older than 90 days
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

-- Check retention policies
SELECT * FROM timescaledb_information.policies;

-- Drop retention policy
SELECT remove_retention_policy('sensor_data');

# Create retention policy (30 days)
curl -XPOST 'http://localhost:8086/query' \
  -d 'CREATE RETENTION POLICY "30d" ON "metrics" DURATION 30d REPLICATION 1'

# Create infinite retention policy (default)
curl -XPOST 'http://localhost:8086/query' \
  -d 'CREATE RETENTION POLICY "forever" ON "metrics" DURATION INF REPLICATION 1'

# Set default retention policy
curl -XPOST 'http://localhost:8086/query' \
  -d 'ALTER RETENTION POLICY "30d" ON "metrics" DEFAULT'

-- Create continuous aggregate (materialized view that auto-refreshes)
CREATE MATERIALIZED VIEW hourly_sensor_data
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp,
    AVG(humidity) AS avg_humidity
FROM sensor_data
GROUP BY hour, sensor_id;

-- Add refresh policy (refresh every 5 minutes)
SELECT add_continuous_aggregate_policy('hourly_sensor_data',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '5 minutes'
);

-- Add retention policy to continuous aggregate
SELECT add_retention_policy('hourly_sensor_data', INTERVAL '1 year');

-- Last N minutes of data
SELECT * FROM sensor_data
WHERE time > NOW() - INTERVAL '10 minutes'
  AND sensor_id = 1;

-- Time-weighted average (interpolation between points)
SELECT
    time_bucket('5 minutes', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY bucket, sensor_id;

-- Detect anomalies (values outside 2 standard deviations)
WITH stats AS (
    SELECT
        AVG(temperature) AS mean,
        STDDEV(temperature) AS stddev
    FROM sensor_data
    WHERE time > NOW() - INTERVAL '24 hours'
)
SELECT *
FROM sensor_data, stats
WHERE ABS(temperature - mean) > 2 * stddev
  AND time > NOW() - INTERVAL '1 hour';

-- Gap detection (missing data points)
SELECT time, sensor_id
FROM sensor_data
WHERE time > NOW() - INTERVAL '1 hour'
  AND sensor_id = 1
  AND time - LAG(time) OVER (ORDER BY time) > INTERVAL '5 minutes';