How to Group by Time in BigQuery

Learn how to efficiently group your BigQuery data by time intervals to unlock insights from your time-series data.

1. Why Group by Time?

Grouping by time allows you to summarize data at useful intervals—like hourly, daily, weekly, or monthly—which is essential for trend analysis, reporting, and dashboards. For example, you might want to know daily active users or monthly sales totals instead of viewing raw event-level data.

2. Use TIMESTAMP_TRUNC for Timestamps

The TIMESTAMP_TRUNC function lets you truncate timestamps to a specific unit (like day or month). Here’s an example that groups events by day:

SELECT
  TIMESTAMP_TRUNC(event_timestamp, DAY) AS day,
  COUNT(*) AS event_count
FROM
  `project.dataset.events`
GROUP BY
  day
ORDER BY
  day;

You can replace DAY with HOUR, WEEK, MONTH, or YEAR depending on your needs.

3. Format Time Values with FORMAT_TIMESTAMP

To create more readable labels, use FORMAT_TIMESTAMP:

SELECT
  FORMAT_TIMESTAMP('%Y-%m', TIMESTAMP_TRUNC(event_timestamp, MONTH)) AS month,
  COUNT(*) AS event_count
FROM
  `project.dataset.events`
GROUP BY
  month
ORDER BY
  month;

This outputs months in the format YYYY-MM (e.g., 2025-05).

4. Group Dates with DATE_TRUNC or DATETIME_TRUNC

If you work with DATE or DATETIME columns, use DATE_TRUNC or DATETIME_TRUNC:

SELECT
  DATE_TRUNC(order_date, MONTH) AS month,
  SUM(order_amount) AS total_sales
FROM
  `project.dataset.orders`
GROUP BY
  month
ORDER BY
  month;

5. Best Practices

• Use ORDER BY to keep time results in chronological order.
• Be mindful of time zones; use DATETIME or set time zones explicitly if needed.
• When grouping by week, note that BigQuery starts weeks on Sunday by default.

Summary

BigQuery’s time grouping functions like TIMESTAMP_TRUNC, DATE_TRUNC, and FORMAT_TIMESTAMP make it easy to transform raw data into clear, actionable insights. Mastering these tools will help you deliver better reports, dashboards, and analyses.

Want to go further? Explore WINDOW functions for moving averages and more advanced time-based analytics.