Как запросом проверить входит ли дата из списка в диапазон дат?

Question

[Aljo]

Как запросом проверить входит ли дата из списка в диапазон дат?

таблица

В столбце dates через запятую указаны даты, в которые будет проводиться мероприятие. На сайте в фильтре задается диапазон дат От-До в таком же формате, как и в таблице (d-m-Y).
Как запросом получить записи, у которых хотя бы одна дата входит в этот диапазон?

Comments

[Евгений Старцев]

что-то мне подсказывает что способ хранения дат не совсем корректный

[Aljo]

Евгений Старцев, согласен, но таким уж достался сайт и записей там очень много, это я вывел только 3, на деле их порядка 3000 и переделывать это - не меньший геморрой. Поэтому и задал вопрос, можно ли провести сравнение сразу в SQL

[Aljo]

Геннадий, причем тут LIKE, если нужно сравнивать с диапазоном дат?

[ThunderCat]

Aljo,

причем тут LIKE, если нужно сравнивать с диапазоном дат?

При том, что в таблице у вас хранятся не даты, а строки (легко проверить - тип поля dates у вас скорее всего варчар или вообще текст). По этому работать с ними как с датами затруднительно.

[Aljo]

ThunderCat, я понимаю, что даты хранятся неверным образом. ок, даже если я все переделаю так, что они будут записаны должным образом, но также списком. То как мне поможет оператор LIKE? Мне ведь нужно проверить условно if between.
Я же написал, что сравнивать надо с диапазоном. например От 24-12-2022 До 06-05-2023. И как же тут LIKE поможет? Или я чего-то не знаю об этом операторе?

[Дмитрий]

Aljo, по хорошему надо привести записи в нормальную форму и использовать between

[alexalexes]

Aljo, тогда гуглите "mysql string split into rows" - решение будет рабочим, но очень не производительным.

[ThunderCat]

я понимаю, что даты хранятся неверным образом. ок, даже если я все переделаю так, что они будут записаны должным образом, но также списком. То как мне поможет оператор LIKE?

Записаны должным образом это значит выведены в отдельные таблицы со связью один-ко-многим или многие-ко-многим, в зависимости от того как работает логика приложения, и вот тогда можно использовать between.
В текущем же формате хранения, учитывая что даты даже не в исо формате, думаю что и лайк будет достаточно кривым решением. И REGEXP не сильно лучше, не говоря уже о том что это очень затратные операторы.

Answer 1

[alexalexes]

Выносите даты в отдельную таблицу:
Название таблицы: location_event_dates
id - идентификатор даты;
location_event_id - идентификатор из таблицы location_event (то, что у вас в вопросе)
event_date - одна из дат события (в формате даты, а не строки!)
Тогда будет нормальный поиск:

select le.id, le.locationid, le.eventid,
          led.event_date
from location_event le
join location_event_dates led on led.location_event_id = le.id
where led.event_date between :begin_date and :end_date

Answer 2

[Rsa97]

Для MySQL 8 можно, но запрос страшненький

WITH RECURSIVE `cte` AS (
  (SELECT `id`, `locationid`, `eventid`,
          TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(`list`, ','), ',', 1), ',', -1)) AS `date`,
          1 AS `idx`
    FROM `test`
    HAVING `date` != '')
  UNION
  (SELECT `t`.`id`, `t`.`locationid`, `t`.`eventid`,
          TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(`t`.`list`, ','), ',', `cte`.`idx` + 1), ',', -1)) AS `date`,
          `cte`.`idx` + 1
     FROM `cte`
     JOIN `test` AS `t` ON `t`.`id` = `cte`.`id`
     HAVING `date` != '')
),
SELECT `id`, `locationid`, `eventid`, 
       CONCAT (SUBSTRING(`date`, 7, 4), '-', SUBSTRING(`date`, 4, 2), '-', SUBSTRING(`date`, 1, 2)) AS `date`
  FROM `cte`
  HAVING `date` >= :firstDate AND `date` < :lastDate

Лучше один раз потратить время и нормализовать таблицу.

Comments

[Akina]

Для MySQL 8 гораздо разумнее не со строковыми функциям вошкаться, а преобразовать CSV в JSON массив, и натравить на него JSON_OVERLAPS().

[Rsa97]

Akina, И как будет выглядеть JSON_OVERLAPS, если надо найти данные, скажем, за два года? Список из 730 дат?

[Akina]

Конечно.

А если хочется работать с диапазоном, используя BETWEEN - надо нормализовать. Для чего самое то на описанной структуре - функция JSON_TABLE().

Впрочем, если списки короткие - то, может, рекурсивный CTE и вправду предпочтительнее. Разве что имеет смысл сразу обрабатывать выделенное значение функцией STR_TO_DATE(), чтобы потом работать сразу с датой, а не конкатенировать подстроки.