Как работает BETWEEN в MySQL?

103

23 марта 2022, 10:00

Встретился с дилеммой. В некоторых ситуациях оператор BETWEEN включают вторую дату ДО, а в некоторых нет. Почему так происходит? Например мне нужно выбрать данные за определенную дату:

... AND (`date` BETWEEN '2020-01-28' AND '2020-01-29') // В этом случае выборка происходит по двум датам за 28 и 29
... AND (`created_at` BETWEEN '2020-01-28' AND '2020-01-29') // В этом случае выборка происходит ТОЛЬКО по дате 28
... AND (DATE(`created_at`) BETWEEN '2020-01-28' AND '2020-01-29') // В этом случае выборка тоже происходит по двум датам за 28 и 29

(Поле date в БД в формате DATE, created_at в DATETIME)

Как это работает?

Answer 1

Любой оператор сравнения, и BETWEEN не исключение, приводит ВСЕ операнды к одному типу данных. В подавляющем большинстве случаев этот тип - либо тип самого первого из операндов по тексту (и тогда жди type mismatch), либо самый "общий" из всех (теоретически возможен convertion failed), т.е. способный принять все операнды.

Как правило, выбор коррелирует со строгостью типизации данных системы. MySQL/MariaDB в этом отношении либеральны, так что имеет место приведение к самому общему из типов.

Оператор (x BETWEEN a AND b) является более компактной записью совокупности ((x >= a) AND (x <= b)).

В первом случае, ... AND (`date` BETWEEN '2020-01-28' AND '2020-01-29'), все три операнда имеют тип даты. Поэтому выбирается и 28, и 29 числа.

Во втором случае, ... AND (`created_at` BETWEEN '2020-01-28' AND '2020-01-29'), мы имеем 2 типа данных дата и один дата-время. Более общим является дата-время, и все операнды приводятся к этому типу. Отсутствующие компоненты получают нулевые значения, и оператор эквивалентен ... AND (`created_at` BETWEEN '2020-01-28 00:00:00' AND '2020-01-29 00:00:00'). Т.е. за 28 будет выбрано любое значение, а вот за 29-е только самая полночь, '2020-01-29 00:00:00'), а любые значения с ненулевой компонентой времени проверку не пройдут.

В третьем случае, ... AND (DATE(`created_at`) BETWEEN '2020-01-28' AND '2020-01-29'), два операнда дата, третий - функция, возвращающая дату, так что все три операнда будут типа даты, и в выборку опять попадут все записи и за 28, и за 29, потому что функция просто обнуляет компоненту времени. Вот кабы округляла - выбиралось бы только до полудня, но чего нет - того нет...

Answer 2

Потому что у вас поле created_at типа DATETIME он ищет до 29 числа 00:00:00 поэтому вы видите только 28 число. Если хотите чтоб попадало 29 тогда ставьте до 30 числа, или указывайте время