PopSQL

How to Group by Time in PostgreSQL

When you want to group by minute, hour, day, week, etc., it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc. The PostgreSQL function you need here is date_trunc.

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

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

database icon
Shared queries and folders ✅ Version history ✅ One-click connection to PostgreSQL ✅
Get more done, together, with PopSQL and PostgreSQL