Как сравнивать даты в 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 >
Резюме
В этой статье поговорили про работу с датами в 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'); 
В серці. Назавжди.
Вчора у мене помер однокласник. А сьогодні бабуся. І хто б міг уявити, що цей рік принесе війну, смерть товариша, та смерть члена сім’ї? Це боляче. Проте це добре нагадування про те, як швидко тече час. І як його ціна збільшується кожної марно витраченої секунди. І я не скажу щось
20 мая 2022 г. 1 min read
Ось такий він, руський мир
«Руський мир» — звучить дуже сильно та виправдовуюче. Гарна обгортка виправдання слабкості, аморальності та нікчемності своїх дійсних намірів. Руський мир, який дуже солодко звучить для всіх, хто хоче закрити очі на факт повномасштабної війни. Дуже добре виправдання вбивства для купки звірів. Втім, це ж росія, в якій все виглядає логічно
16 апр. 2022 г. 3 min read
Перехват запросов и ответов JavaScript Fetch API
Перехватчики — это блоки кода, которые вы можете использовать для предварительной или последующей обработки HTTP-вызовов, помогая в обработке глобальных ошибок, аутентификации, логирования, изменения тела запроса и многом другом. В этой статье вы узнаете, как перехватывать вызовы JavaScript Fetch API. Есть два типа событий, для которых вы можете захотеть перехватить HTTP-вызовы:
выборка актуальных по дате данных
Имеется табличка, в которой тип группы меняется с течением времени:
select * from user_groups_by_types ; id | change_date | user_group | group_type ----+-------------+------------+------------ 1 | 2022-05-01 | A | type1 2 | 2022-05-05 | A | type2 3 | 2022-05-06 | B | type1 Требуется выбрать для каждой группы актуальный тип (т.е. самый последний по дате тип). Решение для одной конкретной группы очевидно. Как это обобщить на все группы?
select * from user_groups_by_types where user_group = 'A' and change_date = (select max(change_date) from user_groups_by_types where user_group = 'A'); id | change_date | user_group | group_type ----+-------------+------------+------------ 2 | 2022-05-05 | A | type2 scisearcher
30.05.22 13:18:41 MSK
- Ответить на это сообщение
- Ссылка
select distinct on (user_group) * from user_groups_by_types order by user_group, change_date desc; theNamelessOne ★★★★★
( 30.05.22 13:48:55 MSK )
Последнее исправление: theNamelessOne 30.05.22 13:49:09 MSK (всего исправлений: 1)
- Ответить на это сообщение
- Показать ответ
- Ссылка
У вас структура говна, ибо из конкретной записи не возможно узнать дату когда тип станет неактуальным.
По-нормальному делаются 2 даты — начальная и конечная. В таком случае всё выбирается банальным between, да и вообще, это удобнее в большинстве других кейсов.
crutch_master ★★★★★
( 30.05.22 17:32:10 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от crutch_master 30.05.22 17:32:10 MSK
структура мне дана и менять ее нельзя, к сожалению
scisearcher
( 30.05.22 17:53:59 MSK ) автор топика
- Ответить на это сообщение
- Показать ответы
- Ссылка
Ответ на: комментарий от theNamelessOne 30.05.22 13:48:55 MSK
scisearcher
( 30.05.22 17:54:45 MSK ) автор топика
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от scisearcher 30.05.22 17:53:59 MSK
структура мне дана и менять ее нельзя, к сожалению
Печально. Но если было бы можно приделать таблицу сбоку я бы приделал. А так изъёжам не будет конца.
crutch_master ★★★★★
( 30.05.22 17:55:39 MSK )
Последнее исправление: crutch_master 30.05.22 17:56:56 MSK (всего исправлений: 2)
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от scisearcher 30.05.22 17:53:59 MSK
Значит надо приводить к нормальному виду и добавлять недостающие данные заджоинив таблицу саму с собой по следующей-предыдущей записи, что то еще удовольствие.
Хотя в конкретном случае можно просто приджоинить
select max(change_date) from user_groups_by_types group by user_group по этой самой user_group и стравнить по change_date.
crutch_master ★★★★★
( 30.05.22 17:58:33 MSK )
Последнее исправление: crutch_master 30.05.22 18:01:20 MSK (всего исправлений: 1)
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от crutch_master 30.05.22 17:58:33 MSK
Спасибо за идею. Кстати, можно проще. Добавить столбец is_active и при назначении нового типа заполнять этот столбец в 1-й или 2-х записях. Хотя start_date и end_date более универсально. Но, править БД незя, c distinct group by выкрутился )
scisearcher
( 30.05.22 18:16:18 MSK ) автор топика
- Ответить на это сообщение
- Ссылка
21 октября 2022 г.
Во-первых решение для одной группы есть и лучше — без подзапроса:
SELECT * FROM user_groups_by_types WHERE user_group='A' ORDER BY change_date DESC LIMIT 1
Во-вторых для всех групп оно тоже очевидное, но не слишком быстрое и читаемое (которое предложил crutch_master ):
SELECT t1.id, t1.change_date, t1.user_group, t1.group_type FROM user_groups_by_types t1 INNER JOIN (SELECT MAX(change_date) AS max_date, user_group FROM user_groups_by_types GROUP BY user_group) t2 ON t1.change_date=t2.max_date AND t1.user_group=t2.user_groupЧтобы сделать хорошо, можно сделать так (но для этого придется поменять структуру):
Добавить в таблицу булев столбец is_current
SELECT * FROM user_groups_by_types WHERE is_current=TRUEВообще это гуглится slowly changing dimension.
damix9 ★★★
( 21.10.22 00:01:47 MSK )
- Ответить на это сообщение
- Ссылка
SQL выборка по дате?
Выводит:
Но вот вопрос, можно, что бы sql подставил сам даты, которых не хватает, то есть
2018-12-14
2018-12-15
2018-12-16
и до
2018-12-23
Или нужно ему подставить даты
SELECT * FROM `table_user` WHERE U_ID = 2 and date_format(DATE_CREATE, '%Y%m') in ('2018-12-23','2018-12-22' и тд)ТО есть, подставить я и сам могу, вопрос может ли это сделать mySQL?
- Вопрос задан более трёх лет назад
- 4535 просмотров
Выборка по дате
Интересует такой вопрос. Как сделать выборку данных по дате?
на пример что было неделю назад если отталкиваться от сегодня
что было в прошлом месяце (т.е. например сегодня февраль, а надо узнать что было в январе)
и что происходило в этом месяце (с начала по сегодняшнее число)
ну или просто где это почитать?
да, гугл забанил)
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
Выборка по дате
Есть 2 столбца: Номер телефона, Дата разговора. Необходимо вывести абонентов, которые не.
Выборка по дате
Подскажите кто-нибудь, как правильно написать запрос? требуется вывести записи с критерием по дате.
Выборка по дате и времени
SELECT dbo.TestCS.idTestCS, dbo.TestCS.NoOrder, dbo.Customer.Name as Customer.
Выборка по дате, не затрагивая время
Здравствуйте! Такой вопрос: в таблице есть поле типа smalldatetime. Мне нужно сделать выборку по.
1634 / 1135 / 171
Регистрация: 23.07.2010
Сообщений: 6,713
1113 / 758 / 183
Регистрация: 27.11.2009
Сообщений: 2,264
Что было в январе?
WHERE DateField >= '20180101' AND DateField '20180201'
Главное — стараться накладывать ограничение на поле, а не на функцию от поля. Иначе нельзя будет использовать индекс.
1634 / 1135 / 171
Регистрация: 23.07.2010
Сообщений: 6,713
Так datediff детерминированная же. В чем подвох с использованием индексов?
Регистрация: 27.11.2014
Сообщений: 263
iap, искал по вашему примеру то что было только в феврале
результат — ничего
1 2 3 4 5 6 7 8
SELECT Dohod.UsersID ,SUM(Dohod.KolichestvoSredstv) AS expr1 FROM dbo.Dohod WHERE Dohod.UsersID = 1 AND Dohod.Var_Scheta = 1 AND Dohod.Date ! 01022018 GROUP BY Dohod.UsersID
1449 / 1121 / 347
Регистрация: 11.04.2011
Сообщений: 2,621
Сообщение от 19boom96 
искал по вашему примеру то что было только в феврале
результат — ничего
SQLВыделить код
1
2
3
4
5
6
7
8
SELECT
* Dohod.UsersID
*,SUM(Dohod.KolichestvoSredstv) AS expr1
FROM dbo.Dohod
WHERE Dohod.UsersID = 1
AND Dohod.Var_Scheta = 1
AND Dohod.Date ! < 01022018
GROUP BY Dohod.UsersID
Целое число 01022018 конвертируется в дату ‘11.03.4698’. В вашей таблице есть данные, которые не меньше этой даты? Дату писать нужно так ‘20180201’. Ну или так ‘01.02.2018’
Сообщение от pincet
Так datediff детерминированная же. В чем подвох с использованием индексов?
В том, что при такой записи:
1 2 3
SELECT * FROM MyTable WHERE DATEDIFF(MONTH, CURRENT_TIMESTAMP, MyDate) = -1
Будет происходит полное сканирование индекса, так как для проверки условия нужно вычислить значение функции DATEDIFF для каждого значения MyDate в таблице.
А при такой записи:
1 2 3 4
SELECT * FROM MyTable WHERE MyDate >= DATEADD(MONTH, -1, CONVERT(DATETIME, CONVERT(CHAR(6), CURRENT_TIMESTAMP, 112) + '01')) AND MyDate CONVERT(DATETIME, CONVERT(CHAR(6), CURRENT_TIMESTAMP, 112) + '01')
Будет происходить поиск в индексе.
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
Помогаю со студенческими работами здесь
Выборка по дате из поля DateTime
Здравствуйте. Есть таблица с полем DateTime. Мне необходимо получить количество записей в которых.
Выборка строк с сортировкой по дате
Всем доброго времени суток. Подскажите один маленький момент: есть запрос на выбор строк из.
Выборка по заданной дате и времени
Здравствуйте, уважаемые форумчане. Помогите, пожалуйста, решить задачу: Есть таблица со столбцом.
Выборка по дате
Добрый день! Для того, что бы выбрать необходимую мне дату пишу условие "where date =.
Выборка по дате
Как сделать выборку: | | Отчетный | Отчетный | Тип | Дата | | № | .
Выборка по дате
Здравствуйте! Есть итоговая таблица: номер1 25.10.2016 11:36:10 номер2 25.10.2016 15:14:32 .