MySQL allows you to group data by certain attributes. Typically you’re grouping data when creating statistics. For example, you want to count the number of users made on a given day or within a selected month.
This tutorial shows you how to group data in MySQL by date or date-time values in your database table.
MySQL Series Overview
Group by Date or Time in MySQL
Usually, you have a timestamp column in your database table, like a createdon column storing the timestamp of a given row's creation date. Grouping by these timestamps isn’t helpful because you’ll receive one group by second, and that’s likely not what you want.
You typically want to group data according to the granularity of your choice, for example, by hour, day, week, month, and so on. To group your data in any of these units of time, you should use MySQL’s DATE_FORMAT function. The DATE_FORMAT function uses a specifier to transform a date or timestamp value into a given format.
Here’s a sample SQL query grouping and counting users by their creation date:
SELECT
DATE_FORMAT(createdon, '%Y-%m-%d') as day_created,
COUNT(*)
FROM users
GROUP BY day_created;
You can adjust the date format to your desired unit of time. If you want to group by hours, you should add the %H specifier. The MySQL docs for the DATE_FORMAT function provides a comprehensive list of specifiers.
You may also use MySQL’s DATE function to extract the date from a timestamp and use that value for grouping:
SELECT
DATE(createdon) as day_created,
COUNT(*)
FROM users
GROUP BY day_created;
That’s it!