Как сравнивать даты в MySQL

MySQL предоставляет возможность сравнивать различные даты между собой, или с каким-то определенным выражением. В этой статье мы обсудим, как работать с датой в Mysql, как из сравнивать и строить запросы с учетом дат.

Когда вам нужно сравнить дату какого-то столбца с произвольной датой, вы можете использовать функцию DATE(), которая извлекает дату из переданного аргумента (без учета времени) и сравнить ее со строкой нужной вам даты.

В прошлой своей статье я писал про работу с индексами. А в этой затрону не менее важную тему работы с датой в MySQL, понимание которое необходимо каждому разработчику.

Например, предположим, что у вас есть таблица MySQL под названием users со следующими строками:

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)

Теперь вам нужно выбрать все строки из таблицы users, у которых значение last_update больше 2021-08-01.

И это достаточно просто сделать:

SELECT 
    user_id,
    first_name,
    last_name,
    last_update
FROM 
    users
WHERE 
    DATE(last_update) > "2021-08-01"
ORDER BY 
    last_update ASC

Результат запроса выше будет следующим:

+---------+------------+-----------+---------------------+
| 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)

При сравнении столбца типа DATETIME или TIMESTAMP с датой в виде строки (как в запросе выше), MySQL автоматически преобразует значения к единому формату для сравнения и веронет подходящзие результаты.

Чтобы проверить результат запроса добавим оператор ORDER BY в приведенный выше запрос.

Вы можете сразу определить, удовлетворяет ли запрос вашему требованию, взглянув на первую строку. Как и в приведенном выше примере, самое раннее значение столбца last_update должно быть 2021-08-02.

Вы также можете использовать оператор BETWEEN, чтобы выбрать все строки, в которых столбец даты находится между двумя указанными выражениями даты:

SELECT 
    user_id,
    first_name,
    last_name,
    last_update
FROM 
    users
WHERE 
    BETWEEN "2021-08-01" AND "2021-08-02"
ORDER BY 
    last_update ASC

Приведенный выше запрос выведет следующий результат:

+---------+------------+-----------+---------------------+
| 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 допускает только один формат даты: yyyy-mm-dd, поэтому вам нужно форматировать любое строковое выражение даты по которым вы хотите выбирать данные к нужному формату.

Зачем использовать функцию DATE() для сравнения дат

Функция MySQL DATE() извлекает часть даты из столбца DATETIME или TIMESTAMP и приводит её к формату строки, как показано ниже:

mysql> SELECT DATE('2005-08-28 01:02:03');
-> '2005-08-28'

Эта функция используется для того, чтобы MySQL использовал для сравнения только часть даты вашего столбца, без учета времени.

Если не использовать функцию DATE(), то MySQL также будет сравнивать и время столбца с вашим строковым выражением. Таким образом, результатом будут записи, полностью совпадаюащие до секунды.

Возвращаясь к приведенному выше примеру таблицы, следующий запрос:

SELECT 
    user_id,
    first_name,
    last_name,
    last_update
FROM 
    users
WHERE 
    last_update > "2021-08-01"
ORDER BY 
    last_update ASC

Вернет такие результаты:

+---------+------------+-----------+---------------------+
| 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)

Как видно из результатов, условие сравнения last_update > "2021-08-01" становится last_update > "2021-08-01 00:00:00", и MySQL возвращает соответствующий набор результатов.

Если вы собираетесь сравнивать записи включая время, то вам нужно добавить часть времени в ваше строковое выражение.

Следующий запрос из той же таблицы:

SELECT 
    user_id,
    first_name,
    last_name,
    last_update
FROM 
    users
WHERE 
    last_update > "2021-08-01 20:00:00"
ORDER BY 
    last_update ASC

Даст следующий результат:

+---------+------------+-----------+---------------------+
| 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)

Сравнение дат между двумя столбцами дат

Если у вас уже есть два столбца даты, то вы можете сразу же сравнить их с помощью операторов <, <=, =, >=, > или BETWEEN.

Предположим, что в вашей таблице есть столбцы last_update и last_login, как показано ниже:

+---------+------------+-----------+---------------------+---------------------+
| 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 |
+---------+------------+-----------+---------------------+---------------------+

Чтобы получить все строки, в которых столбец last_login находится позже столбца last_update, вы можете использовать следующий запрос:

SELECT 
    user_id,
    first_name,
    last_name,
    last_update
FROM 
    users
WHERE 
    last_login > last_update
ORDER BY 
    last_update ASC

Результат будет следующим:

+---------+------------+-----------+---------------------+---------------------+
| 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)

Вот каким образом вы можете выполнять сравнение дат в MySQL.

Не забудьте, что вам нужно иметь строковое выражение даты, отформатированное как yyyy-mm-dd или yyyy-mm-dd hh:mm:ss, если вы хотите сравнить и временную часть.

Выбор дат за определнный интервал

Очень часто приходится выбирать записи с датой за определнный интервал. Для этих целей в MySql существуют встроенная функции INTERVAL, упрощающая работу с датами за определенный период.

Например, для того, чтобы выбрать записи, обновленные за последний год, выполним запрос:

SELECT
    user_id,
    first_name,
    last_name,
    last_update
FROM
    users
WHERE
    last_login > CURDATE() - INTERVAL 1 YEAR
ORDER BY
    last_update ASC

При том, что INTERVAL может работать с различными интервалами времени:

• SECOND - секунды
• MINUTE - минуты
• HOUR - часы
• DAY - дни
• MONTH - месяцы
• YEAR - года

Те же самые действия применимы и к UPDATE-операциям:

UPDATE 
    users
SET 
    last_update = CURDATE() - INTERVAL 10 DAY
WHERE
    id = 1

Резюме

В этой статье поговорили про работу с датами в Mysql. Как можно выбрать даты определенной даты, или по указанному интервалу. А так же, как работать с колонками дат разных типов.

Если вы хотите поупражняться с запросами, то вот вам демонстрационная таблица:

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');