Как исключить из промежутка дат?

Question

[Dmitriy G]

Есть таблица Машина
(id)

Есть таблица (событие) где машина (id_car) забронирована c полями date_start (datetime) и date_end ( datetime)
например: id_car | 01.03.2019 14:40:00 | 01.03.2019 18:45:00

мне надо исключить чтоб не выводилась эта машина, если например выбрать 01.03.2019 13:40:00 до 01.03.2019 15:45:00 и.т.д

SELECT * FROM `car` WHERE (`status`='1')  
AND (`id` NOT IN (SELECT `id_car` AS `id` FROM `event`
 WHERE (`date_end`  > '01.03.2019 15:40:00')  AND (`date_start`  < '01.03.2019 18:30:00')))

но так не работает, машина все равно выводится, следовательно подзапрос не работает, почему ?

SELECT `id_car` AS `id` FROM `event`
 WHERE (`date_end`  > '01.03.2019 15:40:00') 
 AND (`date_start`  < '01.03.2019 18:30:00')

Comments

[Dmitry Bay]

`id` NOT IN ?

[Dmitriy G]

Dmitry Bay, ну да

[Дмитрий]

Работать с датами как со строками?

[Dmitriy G]

Дмитрий, STR_TO_DATE() ?

[Дмитрий]

Dmitriy G, ну я бы перевёл бы значения в timestamp как то с числами попроще работать

[Dmitriy G]

Дмитрий, а дальше также писать запрос ?

[Dmitry Bay]

Dmitriy G,
1) используйте BETWEEN
2) разберитесь с условиями. id` NOT IN и запрос внутри можно трансформировать в запрос
id in (select id where date between "date start" and "date end")

[Дмитрий]

Dmitriy G, я родил какой то кирпич =) , удивлюсь если заработает

SELECT *,  unix_timestamp(STR_TO_DATE(date_start, "%d.%m.%Y %H:%i:%s")) as st, unix_timestamp(STR_TO_DATE(date_end, "%d.%m.%Y %H:%i:%s")) as en  FROM `cars` having st > unix_timestamp(STR_TO_DATE('01.03.2019 15:40:00', "%d.%m.%Y %H:%i:%s")) and en < unix_timestamp(STR_TO_DATE('01.03.2019 18:30:00', "%d.%m.%Y %H:%i:%s"))

[ThunderCat]

Дмитрий, за шо вы так не любите эскуель? там простое условие, просто надо внимательнее смотреть что с чем и как сравнивать, без всех этих ухтыжтвоюлюсю запросов.

[Дмитрий]

ThunderCat, я думаю там дата записана строкой, потому так)

[Dmitriy G]

Дмитрий, ThunderCat, Dmitry Bay, @ssenkevich

Господа на запись event
id_car | date_start | date_end
1 | 01.03.2019 15:00:00 | 01.03.2019 18:00:00

ни один запрос не работает,

SELECT * FROM `car` WHERE NOT EXISTS  (SELECT  1 FROM `event` WHERE `id_car` = `car`.`id_cars` AND
  `date_start` BETWEEN STR_TO_DATE('01.03.2019 15:40:00', "%d.%m.%Y %H:%i:%s")  AND STR_TO_DATE('01.03.2019 16:40:00', "%d.%m.%Y %H:%i:%s") OR  
  `date_end`   BETWEEN STR_TO_DATE('01.03.2019 15:40:00', "%d.%m.%Y %H:%i:%s") AND STR_TO_DATE('01.03.2019 16:40:00', "%d.%m.%Y %H:%i:%s")  OR
  (STR_TO_DATE('01.03.2019 15:40:00', "%d.%m.%Y %H:%i:%s") < `date_start` AND STR_TO_DATE('01.03.2019 16:40:00', "%d.%m.%Y %H:%i:%s") > `date_end`))

SELECT *, 
  unix_timestamp(STR_TO_DATE(date_start, "%d.%m.%Y %H:%i:%s")) as st, 
  unix_timestamp(STR_TO_DATE(date_end, "%d.%m.%Y %H:%i:%s")) as en 
  FROM event e having st > unix_timestamp(STR_TO_DATE('01.03.2019 13:40:00', "%d.%m.%Y %H:%i:%s")) and en < unix_timestamp(STR_TO_DATE('01.03.2019 14:30:00', "%d.%m.%Y %H:%i:%s"))

SELECT * FROM `car` WHERE 
(`id_cars`  NOT IN 
  (SELECT  `id_car` AS `id_cars` FROM `event` WHERE 
    `date_start`  BETWEEN '01.03.2019 15:40:00' AND '01.03.2019 16:40:00' OR  
    `date_end`    BETWEEN '01.03.2019 15:40:00' AND '01.03.2019 16:40:00' ))

SELECT * FROM `car` WHERE 
(`id_cars`  IN 
  (SELECT  `id_car` AS `id_cars` FROM `event` WHERE 
    `date_start`  NOT BETWEEN '01.03.2019 15:40:00' AND '01.03.2019 16:40:00' OR  
    `date_end`   NOT BETWEEN '01.03.2019 15:40:00' AND '01.03.2019 16:40:00' ))

SELECT * FROM `car` WHERE   
 (`id_cars` NOT IN (SELECT `id_car` AS `id_cars` FROM `event`
 WHERE (`date_start`  >= STR_TO_DATE('01.03.2019 15:40:00', "%d.%m.%Y %H:%i:%s"))  AND (`date_end`  <= STR_TO_DATE('01.03.2019 18:40:00', "%d.%m.%Y %H:%i:%s"))))

SELECT * FROM `event` 
  WHERE   
  '01.03.2019 15:40:00' NOT BETWEEN `date_start` AND `date_end` OR 
  '01.03.2019 16:40:00' NOT BETWEEN `date_start` AND `date_end`

но работает

SELECT
  *
FROM car c
WHERE c.id_cars NOT IN (SELECT
    viewevent.id_car AS id_cars
  FROM viewevent
  WHERE viewevent.date_start < :end_date
  AND viewevent.date_end > :start_date)

если подставлять параметры как datetime
следовательно дело в формате,

но почему то на сайте потом не могу понять, как конвертировать строку date_start.val() в datetime
пишу так

$sub = Viewevent::find()->select('id_car')
					  ->andWhere(['<', 'date_start', date_create_from_format('d.m.Y H:i:s', $date_2)])
					  ->andWhere(['>', 'date_end', date_create_from_format('d.m.Y H:i:s', $date_1) ]);

ошибка Object of class DateTime could not be converted to string
пишу без

$sub = Viewevent::find()->select('id_car')
					  ->andWhere(['<', 'date_start', $date_2) ])
					  ->andWhere(['>', 'date_end',  $date_1) ]);

он переводит в строку запрос не работает.
получаю дату напрямую с bootstrap datetimipcker

var date = $('#event-date_end-disp').datetimepicker('getDate')

ошибка Illegal mix of collations for operation '>'
и формат с datimepicker приходит 2019-02-22T07:26:03.000Z

[Dmitriy G]

Dmitriy G, нашел проблему Module::FORMAT_DATETIME => 'php:Y-m-d H:i:s',

Answer 1

[Сергей Сенкевич]

SELECT * 
FROM `car` 
WHERE `status` = '1' AND
    NOT EXISTS (
        SELECT 1 
        FROM `event` 
        WHERE `id_car` = `car`.`id` AND (
            '01.03.2019 15:40:00' BETWEEN `date_start` AND `date_end` OR
            '01.03.2019 18:30:00' BETWEEN `date_start` AND `date_end` OR
            ('01.03.2019 15:40:00' < `date_start` AND '01.03.2019 18:30:00' > `date_end`)
        )
    )

Comments

[ThunderCat]

как то у вас телега впереди лошади и боком, не проще включить граничные условия и сделать правильный порядок сравнения?

[Сергей Сенкевич]

ThunderCat, а что вас не устраивает в этом порядке сравнения? По моему, все логично - первые два условия для "пересечений" временных интервалов, а также для случая, когда искомый интервал лежит внутри занятого интервала. А третье условие для случая, когда искомый интервал полностью перекрывает занятый интервал.

[ThunderCat]

Сергей Сенкевич, просто условие простое как дверь: "начало больше_равно дата и конец меньше_равно дата".

[Сергей Сенкевич]

ThunderCat, Если я правильно понял задание, то условие становится не таким простым. Насколько я понял, нужно выбрать все машины, которые свободны в заданный промежуток времени. "Свободны" - значит в events нет записей для машины с диапазоном, который бы пересекался с заданным. При таком условии, мой запрос должен работать правильно. Может я неправильно понял задание?

[Сергей Сенкевич]

только с форматом дат ошибся)

SELECT * 
FROM `car` 
WHERE `status` = '1' AND
    NOT EXISTS (
        SELECT 1 
        FROM `event` 
        WHERE `id_car` = `car`.`id` AND (
            '2019-03-01 15:40:00' BETWEEN `date_start` AND `date_end` OR
            '2019-03-01 18:30:00' BETWEEN `date_start` AND `date_end` OR
            ('2019-03-01 15:40:00' < `date_start` AND '2019-03-01 18:30:00' > `date_end`)
        )
    )

[ThunderCat]

грубо говоря надо выбрать машины забронированные в этот промежуток(и потом исключить их id из выборки). Ввиду того что у автора все форматы попутаны и задача описана криво все эти танцы с бубном теряют смысл, ибо там просто в 2 местах знаки поменять надо и будет все работать. Ну и формат соответственно поправить.

[Dmitriy G]

Да, нужно исключить и пересечения и совпадение, у меня изначально все работало, когда я не поменял в конфиге Module::FORMAT_DATETIME => 'php:Y-m-d H:i:s', на Module::FORMAT_DATETIME => 'php:d.m.Y H:i:s',
я это и забыл, и начал разбираться в запросе, сейчас вернул правильный формат и все работает, но ваши ответы учту, спасибо

[Сергей Сенкевич]

Dmitriy G, рад, что разобрались! Но, раз уж сделал, вот песочница, где я свой вариант запроса проверял sqlfiddle.com/#!9/0ea40d/1

[Dmitriy G]

Сергей Сенкевич, большое спасибо

Answer 2

[ThunderCat]

WHERE (`date_start`  >= '2019-03-01 14:40') 
 AND (`date_end`  <= '2019-03-01 18:45')

UPD:
итак, для начала - стандартный формат хранения datetime - Y-m-d H:i:s , то есть у вас СРАЗУ все не правильно будет работать.
далее, если вы используете запрос в таком виде как у меня описано - все работает.
Что бы перевести ваши кривые даты в нормальные для запроса либо пишите что-то типа

$startDate = date('Y-m-d H:i:s',strtotime('01.03.2019 15:40:00');

, либо вынесите это в хелпер или функцию.

Comments

[Dmitriy G]

ваш запрос работает если ровно промежуток, но мне надо и пересечения, тогда запрос будет такой

SELECT * FROM `car` WHERE (`status`='1')
 AND (`id_cars` NOT IN (SELECT `id_car` FROM `viewevent` WHERE
 (`date_start` <= '2019-03-01 15:00:00') AND (`date_end` >= '2019-03-01 14:40:00')))

[ThunderCat]

Dmitriy G, ну, про пересечения у вас ничего не сказано, так что тут сами логику выборки додумывайте, а формат даты имхо сразу надо в орм верный передавать в любом случае.