SQL Time Bucketing for Event Data

Time bucketing turns individual events into a trend. The important choices are not only hour versus day: you also need a consistent timestamp, time zone, interval boundary, completeness rule, and treatment for missing buckets. A chart can look authoritative while comparing a full hour with the first three minutes of the current hour.

Build a stable UTC series

SELECT
  date_trunc('hour', timestamp_utc) AS hour,
  COUNT(*) AS requests,
  100.0 * SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS error_rate_pct
FROM api_request_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
GROUP BY date_trunc('hour', timestamp_utc)
ORDER BY hour;

Use the server-managed timestamp_utc field for the query boundary. Keep source timestamps as separate fields when they describe another business moment, such as when a mobile client began an offline action. If the dashboard is operational, UTC avoids daylight-saving gaps and repeated local hours.

date_trunc is the simplest choice for calendar-aligned hours and days. date_bin is useful for a fixed interval such as five minutes:

date_bin(INTERVAL '5 minutes', timestamp_utc, TIMESTAMP '1970-01-01')

Use the same origin in every query that should line up.

Handle incomplete and empty buckets

The newest bucket is usually incomplete. Exclude it from comparisons or label it clearly. For alerting, allow for ingestion delay before judging the last completed interval.

Aggregation only returns buckets that contain rows. A missing point might mean zero activity, delayed ingestion, or a query gap; those interpretations are not interchangeable. If a visualization fills gaps with zero, make that transformation explicit and confirm that zero is semantically correct.

Choose a bucket large enough to produce stable volume but small enough to support the response. Five-minute buckets can expose an incident; daily buckets are often better for retention. See Working with Timestamps, the incident response guide, and the rolling error-rate recipe for complete examples.