MySQL has the ability to compare two different dates written as a string expression.
When you need to compare dates between a date column and an arbitrary date, you can use the DATE() function to extract the date part from your column and compare it with a string that represents your desired date.
For example, suppose you have a MySQL table named users with the following rows:
mysql> SELECT * FROM users;
+---------+------------+-----------+---------------------+
| user_id | first_name | last_name | last_update |
+---------+------------+-----------+---------------------+
| 201 | Peter | Parker | 2021-08-01 16:15:00 |
| 202 | Thor | Odinson | 2021-08-02 12:15:00 |
| 204 | Loki | Laufeyson | 2021-08-03 10:43:24 |
+---------+------------+-----------+---------------------+
3 rows in set (0.00 sec)
Now you want to query all rows from the users table that have the last_update value greater than 2021-08-01.
This is how you do it:
SELECT * FROM users
WHERE DATE(last_update) > "2021-08-01"
ORDER BY last_update ASC;
The output of the query above will be as follows:
+---------+------------+-----------+---------------------+
| user_id | first_name | last_name | last_update |
+---------+------------+-----------+---------------------+
| 202 | Thor | Odinson | 2021-08-02 12:15:00 |
| 204 | Loki | Laufeyson | 2021-08-03 10:43:24 |
+---------+------------+-----------+---------------------+
2 rows in set (0.00 sec)
When comparing a DATETIME or TIMESTAMP column with a string representing a date like in the query above, MySQL will transform both column and expression values into long integer types for comparison.
This is why even though you are comparing a date column with a string, you don’t need to convert your date column values into a string manually.
To inspect your query result, you should include the ORDER BY statement above.
You can immediately tell if the query satisfies your requirement by looking at the first row. As in the example above, the last_update column’s earliest value should be 2021-08-02.
You can also use the BETWEEN operator to select all rows that have the date column between two specified date expressions:
SELECT * FROM users
WHERE DATE(last_update)
BETWEEN "2021-08-01" AND "2021-08-02"
ORDER BY last_update ASC;
The query above will produce the following result:
+---------+------------+-----------+---------------------+
| user_id | first_name | last_name | last_update |
+---------+------------+-----------+---------------------+
| 201 | Peter | Parker | 2021-08-01 16:15:00 |
| 202 | Thor | Odinson | 2021-08-02 12:15:00 |
+---------+------------+-----------+---------------------+
2 rows in set (0.00 sec)
MySQL only allows one date format which is yyyy-mm-dd, so you need to format any string date expression you may have as such.
Why use the DATE() function for comparison
The MySQL DATE() function extracts the date part from your DATETIME or TIMESTAMP column into a string as shown below:
mysql> SELECT DATE('2005-08-28 01:02:03');
-> '2005-08-28'
The function is used so that MySQL will consider only the date part of your column values for comparison.
Without using the DATE() function, then MySQL will also compare the time portion of the column with your string expression. This will make any type of comparison includes the specified string expression into the result set.
Returning to the example table above, the following query:
SELECT * FROM users
WHERE last_update > "2021-08-01"
ORDER BY last_update ASC;
Will produce the following result set:
+---------+------------+-----------+---------------------+
| user_id | first_name | last_name | last_update |
+---------+------------+-----------+---------------------+
| 201 | Peter | Parker | 2021-08-01 16:15:00 |
| 202 | Thor | Odinson | 2021-08-02 12:15:00 |
| 204 | Loki | Laufeyson | 2021-08-03 10:43:24 |
+---------+------------+-----------+---------------------+
3 rows in set (0.01 sec)
As you can see from the output, the comparison clause last_update > "2021-08-01" becomes last_update > "2021-08-01 00:00:00" and so MySQL proceeds to produce the result set accordingly.
If you intend to compare the time portion for your query, then you need to include the time portion in your string expression as well.
The following query from the same table:
SELECT * FROM users
WHERE last_update > "2021-08-01 20:00:00"
ORDER BY last_update ASC;
Will produce the following result:
+---------+------------+-----------+---------------------+
| user_id | first_name | last_name | last_update |
+---------+------------+-----------+---------------------+
| 202 | Thor | Odinson | 2021-08-02 12:15:00 |
| 204 | Loki | Laufeyson | 2021-08-03 10:43:24 |
+---------+------------+-----------+---------------------+
2 rows in set (0.00 sec)
Compare dates between two date columns
If you already have two date columns, then you can immediately compare them with the <, <=, =, >=, >, or BETWEEN operators.
Suppose you have a last_update and a last_login column in your table as shown below:
+---------+------------+-----------+---------------------+---------------------+
| user_id | first_name | last_name | last_update | last_login |
+---------+------------+-----------+---------------------+---------------------+
| 201 | Peter | Parker | 2021-08-01 16:15:00 | 2021-08-17 10:00:00 |
| 202 | Thor | Odinson | 2021-08-02 12:15:00 | 2021-08-02 08:00:00 |
| 204 | Loki | Laufeyson | 2021-08-03 10:43:24 | 2021-08-10 06:00:00 |
+---------+------------+-----------+---------------------+---------------------+
To grab all rows that have the last_login column later than the last_update column, you can use the following query:
SELECT * FROM users
WHERE last_login > last_update
ORDER BY last_login ASC;
The result will be as follows:
+---------+------------+-----------+---------------------+---------------------+
| user_id | first_name | last_name | last_update | last_login |
+---------+------------+-----------+---------------------+---------------------+
| 204 | Loki | Laufeyson | 2021-08-03 10:43:24 | 2021-08-10 06:00:00 |
| 201 | Peter | Parker | 2021-08-01 16:15:00 | 2021-08-17 10:00:00 |
+---------+------------+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)
And that’s how you can perform date comparisons in MySQL.
Don’t forget that you need to have a date string expression formatted as yyyy-mm-dd or yyyy-mm-dd hh:mm:ss if you want to compare the time portion as well.
Also, here’s the CREATE TABLE and INSERT statements if you need my example table for exercises:
CREATE TABLE `users` (
`user_id` smallint unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_login` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `users` (`user_id`, `first_name`, `last_name`, `last_update`, `last_login`)
VALUES
(201,'Peter','Parker','2021-08-01 16:15:00','2021-08-17 10:00:00'),
(202,'Thor','Odinson','2021-08-02 12:15:00','2021-08-02 08:00:00'),
(204,'Loki','Laufeyson','2021-08-03 10:43:24','2021-08-10 06:00:00');
Feel free to use all the resources you see here for your project 😉
