MySQL allows you to aggregate data using GROUP BY clause. Typically, users group by using string or numerical columns but sometimes you may have a datetime column with date+time values but you may need to group by only the date for each value of datetime column. In this article, we will learn how to group by date on datetime column in MySQL.
How to Group By Date on Datetime Column in MySQL
Let us say you have a table sales(id, order_datetime, amount, product) that stores information about sales orders.
mysql> create table sales(id int, order_datetime datetime, amount int);
mysql> insert into sales(id, order_datetime, amount)
values(1, '2022-01-01 10:30:30', 180),
(2, '2022-01-01 10:35:50', 100),
(3, '2022-01-01 11:50:30', 280),
(4, '2022-01-01 11:55:30', 150),
(5, '2022-01-01 12:30:30', 120),
(6, '2022-01-01 13:30:30', 100),
...
Let us say you want to group by date on order_datetime column to get daily sales numbers. Here is the SQL query to do this.
SELECT DATE(order_datetime), SUM(amount) FROM sales GROUP BY DATE(order_datetime);
In the above query, we use date() function to get date value out of datetime column. We use it in both SELECT as well as GROUP BY clause to aggregate data by the date value of column.
date(order_datetime) | sum(amount) ----------------------------------- 2022-01-01 | 930 ...
Alternatively, you can also use alias to group by.
SELECT DATE(order_datetime) date_only, SUM(amount) FROM sales GROUP BY date_only;
The key is to use a function that returns the date of datetime column. The date() function only returns the date value of datetime column. If you want to customize it further you can use date_format() function that allows you to customize the format of date returned. Here is the syntax of date_format() function
date_format(column, format)
Here is the query to group by date on datetime column using date_format function.
SELECT DATE_FORMAT(order_datetime, '%Y-%m-%d'), SUM(amount) FROM sales GROUP BY DATE_FORMAT(order_datetime, '%Y-%m-%d');
Here is a query to aggregate information as YYYY-MM format, that is, for each month.
SELECT DATE_FORMAT(order_datetime, '%Y-%m'), SUM(amount) FROM sales GROUP BY DATE_FORMAT(order_datetime, '%Y-%m');
In this article, we have learnt how to group by date on datetime column in MySQL. date_format() is more versatile since it allows you to customize date value formats as per your requirement. In fact it not only allows you to aggregate information for each day but also at other aggregation levels such as months, years, etc.
Also read:
How to Select by String Length in MySQL
How to Remove All Tables in MySQL
How to Skip Tables in MySQLdump
How to Reset Query Cache Without Restarting
How to Delete All Rows in MySQL Table Except Some