time_bucket groups timestamped rows into fixed-width intervals so SQL queries can calculate rollups such as average, maximum, or minimum heart rate for each period. section 9.5.1
time_bucket(bucket_width, ts, [origin, timezone, offset])
Use the two required arguments, then optionally control bucket alignment, timezone calculation, and offset. section 9.5.1
Aggregate workout heart rates
SELECT time_bucket('10 minutes', recorded_at) AS period, activity, AVG(heart_rate)::int AS avg_rate, MAX(heart_rate)::int AS max_rate FROM watch.heart_rate_measurements WHERE watch_id = 1 AND activity = 'workout' AND recorded_at >= '2025-04-23' AND recorded_at < '2025-04-24' GROUP BY period, activity ORDER BY period; This returns one row per populated ten-minute bucket, including average and maximum rates. section 9.5.1
Adjust bucket behavior for reporting needs:
- Use intervals such as '5 minutes', '3 days', or '1 week'. section 9.5.1
- Supply an origin to align buckets with a chosen date instead of the default alignment. section 9.5.1
- Supply a timezone or offset when bucket boundaries must reflect user-local calculations. section 9.5.1
- Expect gaps to disappear when no rows occupy a bucket; use time_bucket_gapfill to create empty buckets, then LOCF or interpolate to fill values. section 9.5.2
