How to Group by Month in MySQL

Problem:

You'd like to group records by month in a MySQL database.

Example:

Our database has a table named hoodie with data in the columns id, color, and production_timestamp.

Solution:

You can use the MONTH() and YEAR() functions to group records in a table by month. Use the DATE_FORMAT() function to display a label for the month.

Here's the query you would write:

SELECT
  DATE_FORMAT(production_timestamp, ‘%m-%Y’) AS production_month,
  COUNT(id) AS count
FROM hoodie
GROUP BY
  MONTH(production_timestamp),
  YEAR(production_timestamp);

Here’s the result of the query:

Discussion:

Grouping records by month is a very common operation in MySQL. In our example, the number of products is totaled for each month. You usually want to take into account both the year and the month, i.e. you want to group events in January 2022 separately from events in January 2021.

To group data by month in MySQL, use the MONTH() and YEAR() functions. The MONTH() function extracts the number of the month (1 for January, 2 for February, etc.) from the date. The YEAR() function extracts the year from the date. When you combine the functions in the GROUP BY clause, you group the hoodies by the month and year they were produced.

We use DATE_FORMAT() to display a readable label for the month. It retrieves a date or time with a specific precision from a MySQL database. Here, we used month and month-year precisions.

DATE_FORMAT() takes two arguments: the timestamp value (in our example, the column production_timestamp) followed by the date part specifier (here, '%m-%Y'). The specifier is a string and must be enclosed in quotes. The %m is a placeholder for the month as a numeric value (00 to 12) and the %Y stands for the year as a 4-digit numeric value. In our database, the Blush hoodie has the production date 2022-01-22 17:22:05; after it's been truncated to month precision, it becomes 01-2022.