Как составить «хитрый» MySQL запрос?

Question

[limon_spb]

Здравствуйте! Подскажите пожалуйста, как это сделать лучше, и как это сделать вообще.

Есть в БД MySQL таблица, в которой присутствуют 2 столбца TIMESTAMP: start, end.

start — дата+время начала некоего процесса, end — конца.

Нужно выбрать все записи, у которых временной отрезок [start,end] охватывает, скажем 16:00, т.е. за это время часы хоть раз, но показали 16:00.

Например:

01-01-2012 12:00; 01-01-2012 18:00 — подходит, а

01-01-2012 17:00; 01-01-2012 18:00 — не подходит, а

01-01-2012 23:00; 02-01-2012 17:00 — подходит.


SELECT * FROM `table` WHERE ?????


В MySQL не гуру. Буду очень благодарен!


PS вначале случайно поместил не в вопросы, а в посты. Кто успел увидеть — всем «простите» :-)

Answer 1

[FloppyFormator]

Итак, исправляю сам себя.

Чтобы в наш интервал не попали 16 часов, он целиком должен помещаться между двумя ближайшими 16-часовыми отметками на временной прямой. Их можно получить искусственно: первая отметка это наиболее поздние дата-время, когда было 16 часов, но не позже, чем start, а вторая — ровно на сутки позже. Назовём условно эти отметки a и b. Так как start всегда попадает в [a, b], вся задача сводится к проверке попадания end в интервал [a, b]. При end < b в интервале [start, end] не будет ни одной 16-часовой отметки. Теперь вернёмся к исходной задаче. Обращаем условие: при end >= b в интервал [start, end] попадёт хотя бы одна отметка в 16 часов.

Теперь постараемся получить b. Чтобы получить a, вычтем из start 16 часов, обнулим временную часть (прыгнув таким образом к началу суток) и прибавим обратно 16 часов. Величину b получить ещё проще: прибавляем сутки к a.

И в SQL:

SELECT *
FROM my_table
WHERE
    end >= DATE( start - INTERVAL '16:00:00' HOUR_SECOND ) + INTERVAL '16:00:00' HOUR_SECOND + INTERVAL 1 DAY

Comments

[limon_spb]

Спасибо за ответ. С 2х прочтений пока не до конца врубился :-) Чуть чуть попозже буду пытаться вникать
Но пока могу вот, что сказать интересного: в самом первом ответе мы общими усилиями собрали здоровенный запрос, которые уже успели назвать пистолетом для мускуля, но он находит, вроде правильно. Ваш запрос находит почти тоже самое, не считая одной записи. Вот такую ваш запрос пропустил:
06.02.2012 09:30 — begin
07.02.2012 01:20 — end
простите, что не в формате мускуля даты, а в ДД.ММ.ГГГГ

[limon_spb]

тфу, START и end. что-то паскаль всплыл :-) Ну вы поняли, наверное

[FloppyFormator]

Странно, что пропустил. Ведь '2012-02-07 01:20:00' позднее, чем DATE( '2012-02-06 09:30:00' — INTERVAL '16:00:00' HOUR_SECOND ) + INTERVAL '16:00:00' HOUR_SECOND + INTERVAL 1 DAY, и условие в WHERE соблюдается.

[limon_spb]

Да, простите, напутал! Это не ваш запрос пропустил, а наоборот запрос сверху пропустил её, а ваш — поймал!
Спасибо! А как вы оцените скорость такого запроса? Не будет он пистолетом для мускуля?

[FloppyFormator]

Поскольку правая часть сравнения не константная, не будут работать индексы по start и end. Это несколько замедлит поиск, но можно ускорить работу добавлением вспомогательного поля или функционального индекса, если речь пойдёт о более фичастой СУБД (Postgres, Oracle и т. п.).

[limon_spb]

А какое вспомогательное поле можно добавить? Речь только про MySQL

[FloppyFormator]

Да прямо с результатом сравнения end >= DATE( start - INTERVAL '16:00:00' HOUR_SECOND ) + INTERVAL '16:00:00' HOUR_SECOND + INTERVAL 1 DAY. Правда это решение на один-единственный частный случай :)

[limon_spb]

ну вот да, выборки то хочется для разного времени суток :-)
Ладно, спасибо вам больше!

[FloppyFormator]

Пожалуй, попробую предложить решение для произвольного времени. Правда оно от этого поменялось чуть менее, чем полностью. Итак, создаём два вспомогательных поля: start_delta и end_delta типа TIME. В start_delta с помощью триггера или планировщика записываем результат вычисления TIME(start), в end_delta записываем TIMEDIFF( end, TIMESTAMP(DATE(start)) ).

Далее, в запросе:

SELECT *
FROM my_table
WHERE
    TIME('16:00:00') BETWEEN start_delta AND end_delta OR
    ADDTIME( '16:00:00', '24:00:00') BETWEEN start_delta AND end_delta

В этом случае удаётся записать во вспомогательные поля значения, не зависящие от желаемой отметки времени, которая, в свою очередь, передаётся в сравнение в виде константы, что позволит использовать составной индекс по start_delta и end_delta.

[limon_spb]

Спасибо! Вроде работает ваш предыдущий вариант. Начнет тормозить — будет уже не жалко вносить доп поля в БД. А пока — не очень хочется, все ровненько и понятно, что ввели, то и есть в БД, ничего лишнего :-) Надеюсь, на паре десятков тысяч записей, все не умрет на виртуальном хостинге

[FloppyFormator]

Не, не должно. А оптимизировать всегда успеете :-)

Answer 2

[himik]

select * from table where '16:00:00' between CAST(start AS DATE) and CAST(end AS TIME)

не проверял, но что-то вроде этого должно работать

Comments

[himik]

CAST(start AS TIME)*

[limon_spb]

Спасибо за ответ!
Работает почти всегда, но к сожалению почти…
вот такой вариант не найдет, например:
01-01-2012 21:00; 02-01-2012 22:00
т.к. если рассматривать только время, то 16:00 не было, но если учесть, что прошли сутки — то 16:00 случалось.

[Юрий Чернышев]

проверка на сутки
TIMESTAMPDIFF(DAY, `start`, `end` ) > 1

[limon_spb]

jowee, спасибо, однако сутки могут не пройти, например такой вариант:
01-01-2012 22:00
— 02-01-2012 21:00

[Юрий Чернышев]

вот нечто такое у меня получилось
select * from `dates` where
(TIMESTAMPDIFF(DAY, `start`, `end` ) >= 1) OR
((CAST(`start` AS DATE) = CAST(`end` AS DATE)) AND (CAST(start AS TIME) < '16:00') AND (CAST(end AS TIME) > '16:00')) OR
((CAST(`start` AS DATE) != CAST(`end` AS DATE)) AND ((CAST(start AS TIME) < '16:00') OR ((CAST(start AS TIME) > '16:00') AND (CAST(end AS TIME) > '16:00'))))

[betal]

Я бы еще вынес (CAST(`start` AS TIME) в select к списку полей, думаю преобразование тогда будет только один раз вызываться. а не для каждого условия.
т.е. типа select (CAST(`start` AS DATE) as start_date, (CAST(`start` AS TIME) as start_time и т.дю

[limon_spb]

jowee, да, у меня уже, благодаря вашим подсказкам всем получилось нечто подобное, я чуть выше накидал, что вышло. По вашему варианту, вот тут:
((CAST(`start` AS DATE) = CAST(`end` AS DATE)) AND (CAST(start AS TIME) < '16:00') AND (CAST(end AS TIME) > '16:00'))
вроде необязательно делать проверку, что даты одинаковые. Т.е. если
(CAST(start AS TIME) < '16:00') AND (CAST(end AS TIME) > '16:00')
выполнено, то так и так запись подходит, вроде.
Третье условия — просто ппц :-) Как вы не запутались? Я сперва подумал, что вы закончили сообщение смайлами :-)))) А это скобки. ПО подсказкам Betal, вроде третье условие поменьше. Посмотрите и вы его, пожалуйста, вдруг найдете что-то не то. (чуть выше в пред. ответе)

Answer 3

[betal]

start_time < 16:00 AND end_time > 16:00 OR start_time > end_time
Если ничего не упустил можно как-то так попробовать.

Comments

[betal]

хотя упустил случай start_time = 19:00 end_time= 12:00, т.е. еще пара AND'ов нужен и запрос потяжелеет

[betal]

На большом проекте чтобы не грузить запросом сделал бы наверно флаг, отвечающий за это и по крону ежедневно обновлял.

[limon_spb]

А разве можно сравнивать вот так? Ведь у меня нет отдельно столбца «время», там время с датой.
т.е., например запрос start< '16:00' не вернет ничего, вроде, поскольку start — еще и дата…

[betal]

Если преобразовать одной из стандартных MySql команд, то да. Например, как в ответе ниже (правда я не уверен что данный способ будет корректно работать для разных дней и не потребуется дополнительных условий). Другой вопрос, что это тоже требует вычислительных ресурсов.
Вообще у mysql много функций для работы с датой и временем, можно сделать аккуратнее запрос, чем у меня.

[limon_spb]

Да, вы правы, у запроса из ответа снизу как раз проблема с разными датами т.к. там идет учет только времени.
А сделать такой флаг в самой БД — не вариант т.к. нужны будут выборки не только с 16:00, но и с любым другим временем суток…

[betal]

Скорее всего если делать много условий через OR, то при встрече первого true остальные условия проверяться даже не будут. Потому не так критично.
Первое условие что между датами больше одного дня,
второе условие start_time<16:00 and end_time>16:00,
Третье условие end_time > 16:00 AND start_time > and_time
Такое попробуйте

[limon_spb]

Betal, спасибо! Получилось вот такая штукенция (благодаря еще jowee):
SELECT * FROM table WHERE
('16:00:00' between CAST(startDT AS TIME) and CAST(endDT AS TIME))
OR
(TIMESTAMPDIFF(DAY, startDT, endDT ) >= 1)
OR
(CAST(endDT AS TIME) >= '16:00:00' AND CAST(startDT AS TIME)>=CAST(endDT AS TIME))

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

[limon_spb]

PS: думал все-таки как-то красивее можно, чем такие условия :-) Остается молится, что ничего не упущено :-)

[limon_spb]

PPS: startDT = start, endDT = end. Просто у меня в таблице с *DT

[AxisPod]

Использование CAST в условии, может проще мускул застрелить сразу, ему придется обрабатывать значения каждой строки, про индексы естественно сразу можно забыть, он просто будет стреляться на больших объемах данных.

[limon_spb]

И все-такие такая комбинация условий пропускает вот такое, к сожалению:
06.02.2012 09:30 — begin
07.02.2012 01:20 — end

[betal]

А есть возможность разделить datetime на два поля time и date? все таки значительно пошустрее будет работать.
Такой запрос умрет на большой таблице.
А для этой комбинации нужно еще условие… Кажется такое: begin_time>end_time AND begin_time<16:00

[limon_spb]

Посмотрите, пожалуйста, ответ, который я решением сделал, он, вроде покороче, может вы какие-то нестыковки в нем найдете?

[betal]

Навскидку решение красивое, нужно прогнать для различных значений start и end и сравнить в плане производительности, хотя думаю оно пошустрее будет.

Answer 4

[FloppyFormator]

Предлагаю идти от обратного: в какой интервал не попадает время 16 часов? Только в такой, который целиком помещается между 16 часами первого и следующего дня. То есть, нужно проверить, попадает ли наш интервал в этот промежуток. Пересечение интервалов [a, b] и [c, d] легко проверяется условием a <= d && b >= c. В нашем случае, start >= next_date_16_00 && end <= start_date_16_00, где start_date_16_00 — 16 часов в день начала интервала, а next_date_16_00 — то же, но на следующие сутки. Обращаем условие (мы ведь начинали от обратного): start < next_date_16_00 || end > start_date_16_00.

И наш запрос:

SELECT *
FROM my_table
WHERE
    start < TIMESTAMP( DATE(start) + INTERVAL 1 DAY, '16:00:00' ) OR
    end > TIMESTAMP( DATE(start), TIME('16:00:00') )

Comments

[FloppyFormator]

Я ошибся с самого начала, попадание между 16 часами первого и следующего дня — не единственное условие.

Answer 5

[Vampiro]

А я бы добавил в таблицу поле "продолжительность", (или вместо "дата завершения") И имел запрос
((время начала + продолжительность)по модулю 24) >= нужная точка

where MOD(`start`+`length`,24)>=16

Comments

[limon_spb]

Так это нужно еще все даты привести к часам… или как? не совсем понял. А если будет не 16.00, а 16.37?

[Vampiro]

Если у вас точость не часовая, то и приводить надо к меньшим величинам.

`length` = UNIX_TIMESTAMP(`end`)-UNIX_TIMESTAMP(`begin`)

тогда 16 часов у вас будет (16*60*60), а модуль брать по количеству секунд в сутках. (24*60*60)
как получить количество секунд в сутках для начальной записи что-то сходу не могу придумать, но может как-то

UNIX_TIMESTAMP('2012-01-01 01:00:00')-UNIX_TIMESTAMP(`2012-01-01 00:00:00`)

разница между началом записи, и таймстампом от начала суток

[Vampiro]

а, блин

TIME_TO_SEC(TIME(`start))

и получите

where MOD(TIME_TO_SEC(TIME(`start)) + ( UNIX_TIMESTAMP(`end`)-UNIX_TIMESTAMP(`start`)) , 86000) >= TIME_TO_SEC('16:37:12')

ничего не тестил, мог накосячить. =/

[limon_spb]

хм… если брать ваш первый пример:
where MOD(`start`+`length`,24)>=16
это ведь тоже самое, что
where MOD(`end`,24)>=16

[Vampiro]

Не-не, время начала + продолжительность, это не то же самое, что «дата завершения». Например, если запись началась в 23 часа и продолжалась два, то «время+длина» дадут вам число 25. Что несколько отличается от записи «01 час следующих суток» Я специально указал там «время», поскольку не ясно было оперируете-ли вы часами или произвольными интервалами.
Но в любом случае вам выгоднее пользоваться этим запросом, поскольку этот нужно будет добивать еще одним условием + в нем слишком много преобразований, которые не позволят использовать индексы.

[limon_spb]

Да, я вас понял. Спасибо!