How to Group by Time in BigQuery

If you want to group by minute, hour, day, or week, don't just group by your timestamp column. You'd get one group per second, which is probably not what you want. Instead, first "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc.

The BigQuery function you need is timestamp_trunc, datetime_trunc, date_trunc, or time_trunc depending on the data type of the schema.

Timestamp

select
  timestamp_trunc('minute', created_at), -- or hour, day, week, month, year
  count(1)
from users
group by 1

Datetime

select
  datetime_trunc('minute', created_at), -- or hour, day, week, month, year
  count(1)
from users
group by 1

Date

select
  date_trunc('week', created_at), -- or month, year
  count(1)
from users
group by 1

Time

select
  time_trunc('minute', created_at), -- or second, hour
  count(1)
from users
group by 1

Note: each function accepts a slightly different list of date_parts (second, hour, minute, etc). Check out the how to round timestamps in BigQuery guide for a list.

If you don't have new users every minute, you'll have gaps in your data. To have one row per minute, even when there's no data, you'll want to use generate_series.