How to Group the Datetime Column by Date Only in MySQL
This tutorial uses the GROUP BY clause, COUNT() and DATE() functions to group the DATETIME type column by DATE only in MySQL.
Use GROUP BY, COUNT(), and DATE() to Group the DATETIME Column by DATE Only in MySQL
Before moving on, remember that the DATETIME values look like YYYY-MM-DD hh:mm:ss while the DATE values are in the format YYYY-MM-DD. We want to group the data by DATE only while the column’s data type is DATETIME.
We will create two tables named students and the student_attendance. The students table has basic details about every student, while the student_attendance table contains STUDENT_ID and ATTENDANCE as attributes (columns).
We may also create both tables using the queries below for learning purposes.
Example code (Create Tables):
# Create a `students` table
CREATE TABLE students(
ID INT NOT NULL PRIMARY KEY,
FIRSTNAME VARCHAR(45) NOT NULL,
LASTNAME VARCHAR(45) NOT NULL,
GENDER VARCHAR(10) NOT NULL
);
# Create a `student_attendance` table
CREATE TABLE student_attendance(
STUDENT_ID INT NOT NULL,
ATTENDANCE DATETIME NOT NULL,
FOREIGN KEY (STUDENT_ID) REFERENCES students(ID)
);
Example code (Insert Data Into the Tables):
# Insert data into the `students` table
INSERT INTO students (ID, FIRSTNAME, LASTNAME, GENDER)
VALUES
(1, 'Mehvish', 'Ashiq', 'Female'),
(2, 'Thomas', 'Christopher', 'Male'),
(3, 'John', 'Jackson', 'Male');
# Insert data into the `stduent_attendance` table
INSERT INTO student_attendance (STUDENT_ID, ATTENDANCE)
VALUES
(1, '2022-05-02 08:15:10'),
(2, '2022-05-02 08:15:10'),
(3, '2022-05-02 08:15:10'),
(1, '2022-05-03 08:15:10'),
(2, '2022-05-03 08:15:10'),
(3, '2022-05-03 08:15:10'),
(1, '2022-05-04 08:15:10'),
(2, '2022-05-04 08:15:10'),
(3, '2022-05-04 08:15:10'),
(1, '2022-05-05 08:15:10'),
(2, '2022-05-05 08:15:10'),
(3, '2022-05-05 08:15:10'),
(1, '2022-05-06 08:15:10'),
(2, '2022-05-06 08:15:10'),
(3, '2022-05-06 08:15:10');
Example code (Display Data):
SELECT * from students;
SELECT * from student_attendance;
Output (for students table):
+----+-----------+-------------+--------+
| ID | FIRSTNAME | LASTNAME | GENDER |
+----+-----------+-------------+--------+
| 1 | Mehvish | Ashiq | Female |
| 2 | Thomas | Christopher | Male |
| 3 | John | Jackson | Male |
+----+-----------+-------------+--------+
3 rows in set (0.00 sec)
Output (for student_attendance table):
+------------+---------------------+
| STUDENT_ID | ATTENDANCE |
+------------+---------------------+
| 1 | 2022-05-02 08:15:10 |
| 2 | 2022-05-02 08:15:10 |
| 3 | 2022-05-02 08:15:10 |
| 1 | 2022-05-03 08:15:10 |
| 2 | 2022-05-03 08:15:10 |
| 3 | 2022-05-03 08:15:10 |
| 1 | 2022-05-04 08:15:10 |
| 2 | 2022-05-04 08:15:10 |
| 3 | 2022-05-04 08:15:10 |
| 1 | 2022-05-05 08:15:10 |
| 2 | 2022-05-05 08:15:10 |
| 3 | 2022-05-05 08:15:10 |
| 1 | 2022-05-06 08:15:10 |
| 2 | 2022-05-06 08:15:10 |
| 3 | 2022-05-06 08:15:10 |
+------------+---------------------+
15 rows in set (0.04 sec)
Group the DATETIME Column by DATE Only in MySQL
We want to check how many students attended the class from Monday to Friday. We will group by the DATE only as follows.
Example code:
SELECT COUNT(STUDENT_ID), DATE(ATTENDANCE)
FROM student_attendance
GROUP BY DATE(student_attendance.ATTENDANCE);
Output:
+-------------------+------------------+
| COUNT(STUDENT_ID) | DATE(ATTENDANCE) |
+-------------------+------------------+
| 3 | 2022-05-02 |
| 3 | 2022-05-03 |
| 3 | 2022-05-04 |
| 3 | 2022-05-05 |
| 3 | 2022-05-06 |
+-------------------+------------------+
5 rows in set (0.00 sec)
Alternatively, we can use the ALIAS to make the output clear.
Example code:
SELECT COUNT(STUDENT_ID) AS NumbOfStudents, DATE(ATTENDANCE) AS DateOnly
FROM student_attendance
GROUP BY DATE(DateOnly);
Output:
+----------------+------------+
| NumbOfStudents | DateOnly |
+----------------+------------+
| 3 | 2022-05-02 |
| 3 | 2022-05-03 |
| 3 | 2022-05-04 |
| 3 | 2022-05-05 |
| 3 | 2022-05-06 |
+----------------+------------+
5 rows in set (0.00 sec)
We cast the DATETIME type to DATE only to achieve the goal in the above query. We used different functions and clauses that are briefly explained below.
MySQL COUNT() Function
The COUNT() is an aggregate function that we use to return the expression’s count. It lets us count all table records that meet a particular condition.
The COUNT() function’s return type is BIGINT. It returns 0 if there are no matching records in the table.
There are 3 ways we can use the COUNT() function with the SELECT statement.
Count (*)- The output produced by this form contains all the duplicate,NULL, andNOT NULLvalues.Count (expression)- The number of records returned by thisCOUNT()function does not have theNULLvalues.Count (distinct)- It returns the number of distinct records that do not have theNULLvalues as an expression’s result.
MySQL DATE() Function
The DATE() method extracts only the DATE from the DATETIME expression. If the expression is not a valid DATETIME or DATE value, it returns NULL.
MySQL GROUP BY Clause
The GROUP BY clause can group the records with the same values into the summary records. For instance, find the number of students present in the class.
We use this statement with the aggregate functions to group the output by single or multiple fields (columns).
