How to Group by Time in MySQL

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 function you need here is DATE_FORMAT:













SELECT

   date_format(created_at,
'%Y-%m-%d %H-%i'





), 
-- leave out -%i 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 `DATE() function:


SELECT

   
date
(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.

How to Group by Time in MySQL – PopSQL

How to Group by Time in MySQL