понедельник, 3 июня 2013 г.

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

http://tigor.com.ua/blog/2008/08/23/date_comparison_by_between_operator_of_mysql/

Оператор 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 и без него:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# Поле 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');
Получаем самый лучший запрос:
1
2
3
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');
Автор: на
Ярлыки: