MySQL: cравнение даты (datetime) через оператор BETWEEN

Оператор BETWEEN идеально подходит для сравнения диапазона между датами (datetime). Но тут есть подводные камни. Например, есть задача – выбрать данные из таблицы за некоторый промежуток времени (с ’2008-08-14′ по ’2008-08-23′).

Рекомендации:
1. Выполняя любые сравнения, приводить все данные к одному типу.
2. Если один операнд имеет значение типа TIMESTAMP или DATETIME, а другой является константой, операнды сравниваются как значения типа TIMESTAMP. А это значит, что если была строка в виде ’2008-08-14′, то она автоматически преобразуется в TIMESTAMP ’2008-08-14 00:00:00′ и это влияет на результат запроса.
3. Над данными, которые участвуют в условиях сравнения желательно не делать никаких операций – это позволяет для них использовать индексы, иначе они игнорируются.

Примеры запросов с BETWEEN и без него:

# Поле created_at - тип DATETIME
# КОРРЕКТНЫЕ ЗАПРОСЫ

# 1: Индексы для created_at поля не используются.
# Условие BETWEEN \'2008-08-14\' AND \'2008-08-23\'
# преобразуется в BETWEEN \'2008-08-14 00:00:00\' AND \'2008-08-23 00:00:00\'.
SELECT * FROM news
WHERE DATE(created_at) BETWEEN \'2008-08-14\' AND \'2008-08-23\';

# 2: Оператор BETWEEN не используем, но тоже не самый лучший вариант, индексы не используются
SELECT * FROM news
WHERE DATE(created_at) >= \'2008-08-14\' AND DATE(created_at) <= \'2008-08-23\'; # 3: Строки \'2008-08-14 00:00:00\' и \'2008-08-23 23:59:59\' не приведены к типу данных DATE SELECT * FROM news WHERE created_at BETWEEN \'2008-08-14 00:00:00\' AND \'2008-08-23 23:59:59\'; # 4: Самый лучший вариант, привели к типу DATETIME, индексы будут использоваться SELECT * FROM news WHERE created_at BETWEEN STR_TO_DATE(\'2008-08-14 00:00:00\', \'%Y-%m-%d %H:%i:%s\') AND STR_TO_DATE(\'2008-08-23 23:59:59\', \'%Y-%m-%d %H:%i:%s\'); ############################# # НЕПРАВИЛЬНЫЕ ЗАПРОСЫ # 5: Строки \'2008-08-14\' и \'2008-08-23\' преобразуются в TIMESTAMP и дополняются \'00:00:00\' SELECT * FROM news WHERE created_at >= \'2008-08-14\' AND created_at <= \'2008-08-23\';

# 6: Аналогично запросу 5
SELECT * FROM news
WHERE created_at BETWEEN STR_TO_DATE(\'2008-08-14\', \'%Y-%m-%d\') AND STR_TO_DATE(\'2008-08-23\', \'%Y-%m-%d\');

Получаем самый лучший запрос:

SELECT * FROM news
WHERE created_at BETWEEN STR_TO_DATE(\'2008-08-14 00:00:00\', \'%Y-%m-%d %H:%i:%s\')
AND STR_TO_DATE(\'2008-08-23 23:59:59\', \'%Y-%m-%d %H:%i:%s\');


Ссылки по теме:
• MySQL 5.1 Reference Manual :: 11.2.3 Comparison Functions and Operators :: Operator BETWEEN
• MySQL 5.1 Reference Manual :: 11 Functions and Operators :: 11.6 Date and Time Functions
• Search by Date or Timestamp in MySQL