How to Group by Time in Redshift

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 Redshift function you need here is TO_CHAR():

SELECT
   to_char(created_at, 'YYYY-MM-DD HH24:MI'), -- leave out :MI if you want to group by hour
   count(1)
FROM users
GROUP BY 1;

Grouping by date is easier as you can just use the TRUNC() function:

SELECT
   trunc(created_at),
   count(1)
FROM users
GROUP BY 1;

If you don't have new users for every minute/hour/day, you're going to have gaps in your data. To have one row per interval, even when there's no data, you'll want to generate data for it.

database icon
SQL editing that just rocks
PopSQL and Redshift, better together