-- Time-weighted average
SELECT
sensor_id,
SUM(value * extract(epoch from lead(time) OVER w - time)) /
SUM(extract(epoch from lead(time) OVER w - time)) as time_weighted_avg
FROM metrics
WHERE time >= NOW() - interval '1 day'
WINDOW w AS (PARTITION BY sensor_id ORDER BY time)
ORDER BY sensor_id;

-- Downsampling for graphs
WITH downsampled AS (
SELECT
time_bucket('5 minutes', time) as bucket,
sensor_id,
AVG(value) as value
FROM metrics
WHERE time >= NOW() - interval '24 hours'
GROUP BY bucket, sensor_id
)
SELECT * FROM downsampled ORDER BY bucket;

-- Gap detection
SELECT
sensor_id,
time as gap_start,
lead(time) OVER (PARTITION BY sensor_id ORDER BY time) as gap_end,
lead(time) OVER (PARTITION BY sensor_id ORDER BY time) - time as gap_duration
FROM metrics
WHERE lead(time) OVER (PARTITION BY sensor_id ORDER BY time) - time > interval '5 minutes';

### Performance Numbers:

**My production setup:**
- 50 million records/day
- 6 months retention (9 billion rows)
- $40 VPS (8 CPU, 16GB RAM)
- Query response: <100ms for most queries
- Storage: 480GB (with compression)

**Equivalent TimescaleDB Cloud:** $400+/month
**Your savings:** $360/month

### The time_bucket Function (DIY TimescaleDB):

sql
-- Create your own time_bucket
CREATE OR REPLACE FUNCTION time_bucket(
bucket_width INTERVAL,
ts TIMESTAMPTZ
) RETURNS TIMESTAMPTZ AS $$
BEGIN
RETURN date_trunc('epoch', ts)::timestamptz +
(extract(epoch from date_trunc('epoch', ts)) /
extract(epoch from bucket_width))::bigint *
extract(epoch from bucket_width) * interval '1 second';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Now you have TimescaleDB's best feature for free!

### Tomorrow: [PREMIUM] API Rate Limiting System

What's your time-series use case? IoT, analytics, monitoring?

#PostgreSQL #TimeSeries #Partitioning #Analytics #Performance

@postgres