SELECT CONCAT(`h`.`hour`, ':', `m`.`minute`, ':00')
FROM (
SELECT '09' AS `hour`
UNION SELECT '10'
UNION SELECT '11'
UNION SELECT '12'
UNION SELECT '13'
UNION SELECT '14'
UNION SELECT '15'
UNION SELECT '16'
UNION SELECT '17'
) AS `h`
JOIN (
SELECT '00' AS `minute`
UNION SELECT '15'
UNION SELECT '30'
UNION SELECT '45'
) AS `m`
SELECT t.event_date, -- атрибут таблицы типа datetime, для которого определяем интервал, выведен для того, чтобы наблюдать за процессом отладки
case -- SQL-конструкция вида "case when ... then ... else ... end", с помощью которой можно выводить не только конкретное значение атрибута, но и задавать условие, при котором конечное значение результата будет вычисляться по-разному в пределах одной выбранной записи.
when TIME_TO_SEC(t.event_date) >= inter.begin AND TIME_TO_SEC(t.event_date) <= inter.end -- если t.event_date, выраженное в суточных секундах, находится в отрезке [inter.begin, inter.end], также выраженных в суточных секундах,...
then floor((TIME_TO_SEC(t.event_date) - inter.begin) / inter.width) -- ... то вычисляем количество полных интервалов
else null -- иначе, если время не попадает в период отслеживания, то выводим "пусто", чтобы явно указать, что значение не находится в интересуемом периоде
end as full_interval_number -- кол-во прошедших полных интервалов, начинается с 0
FROM table t, -- ваша таблица
(select TIME_TO_SEC('09:00:00') as begin, -- начало дневного периода
TIME_TO_SEC('18:00:00') as end, -- конец дневного периода
TIME_TO_SEC('00:15:00') as width -- ширина интервала
) inter -- параметрический подзапрос, все настраиваемые константы в одном месте - удобно при отладке
SELECT t.event_date, -- атрибут таблицы типа datetime, для которого определяем интервал, выведен для того, чтобы наблюдать за процессом отладки
case -- SQL-конструкция вида "case when ... then ... else ... end", с помощью которой можно выводить не только конкретное значение атрибута, но и задавать условие, при котором конечное значение результата будет вычисляться по-разному в пределах одной выбранной записи.
when TIME_TO_SEC(t.event_date) > inter.begin AND TIME_TO_SEC(t.event_date) < inter.end -- если t.event_date, выраженное в суточных секундах, находится в периоде (inter.begin, inter.end), также выраженных в суточных секундах,...
then floor((TIME_TO_SEC(t.event_date) - inter.begin) / inter.width) -- ... то вычисляем количество полных интервалов
when TIME_TO_SEC(t.event_date) <= inter.begin -- если еще рано до периода подсчета интервалов, ...
then 0 -- то, по текущей дате t.event_date ноль интервалов
when TIME_TO_SEC(t.event_date) >= inter.end -- если время больше или равно окончанию периода, ...
then floor((inter.end - inter.begin) / inter.width) -- ...то выдаем кол-во интервалов
else null -- по идее, сюда никогда не попадем, а вдруг?
end as full_interval_number -- кол-во прошедших полных интервалов, начинается с 0
FROM table t, -- ваша таблица
(select TIME_TO_SEC('09:00:00') as begin, -- начало дневного периода
TIME_TO_SEC('18:00:00') as end, -- конец дневного периода
TIME_TO_SEC('00:15:00') as width -- ширина интервала
) inter -- параметрический подзапрос, все настраиваемые константы в одном месте - удобно при отладке
select * from
(
-- подзапрос интервалов
) a
where
-- отбрасывание из выборки событий между 9 и 10 часами
TIME_TO_SEC(a.event_date) < TIME_TO_SEC('09:00:00')
and TIME_TO_SEC(a.event_date) > TIME_TO_SEC('10:00:00')
-- исключение определенного перечня интервалов
a.full_interval_number not in (0, 3, 7, 15)
-- включить в выборку только интервалы 1 < full_interval_number < 10
a.full_interval_number > 1
and a.full_interval_number < 10
FOR, GROUP, HAVING, INTO, LIMIT, LOCK, ORDER, PROCEDURE, UNION or '[' expected, got 'a'
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'a.full_interval_number not in (1,3) -- включить в выборку т' at line 28
SELECT * FROM
(
SELECT
t.event_date,
CASE
WHEN TIME_TO_SEC(t.event_date) > inter.begin AND TIME_TO_SEC(t.event_date) < inter.end
THEN floor((TIME_TO_SEC(t.event_date) - inter.begin) / inter.width)
WHEN TIME_TO_SEC(t.event_date) <= inter.begin
THEN 0
WHEN TIME_TO_SEC(t.event_date) >= inter.end
THEN floor((inter.end - inter.begin) / inter.width)
ELSE NULL
END AS full_interval_number
FROM table t,
(SELECT
TIME_TO_SEC('09:00:00') AS begin,
TIME_TO_SEC('18:00:00') AS end,
TIME_TO_SEC('00:15:00') AS width
) inter
)
a
where
-- отбрасывание из выборки событий между 9 и 10 часами
TIME_TO_SEC(a.event_date) < TIME_TO_SEC('09:00:00')
and TIME_TO_SEC(a.event_date) > TIME_TO_SEC('10:00:00')
-- исключение определенного перечня интервалов
a.full_interval_number not in (1,3)
-- включить в выборку только интервалы 1 < full_interval_number < 10
a.full_interval_number > 1
and a.full_interval_number < 10
where
-- отбрасывание из выборки событий между 9 и 10 часами
TIME_TO_SEC(a.event_date) < TIME_TO_SEC('09:00:00')
and TIME_TO_SEC(a.event_date) > TIME_TO_SEC('10:00:00')
where
-- исключение определенного перечня интервалов
a.full_interval_number not in (0, 3, 7, 15)
where
-- включить в выборку только интервалы 1 < full_interval_number < 10
a.full_interval_number > 1
and a.full_interval_number < 10
where
-- исключение определенного перечня интервалов
a.full_interval_number not in (0, 3, 7, 15)
-- отбрасывание из выборки событий между 9 и 10 часами
and TIME_TO_SEC(a.event_date) < TIME_TO_SEC('09:00:00')
and TIME_TO_SEC(a.event_date) > TIME_TO_SEC('10:00:00')
SELECT t.event_date,
case
when TIME_TO_SEC(t.event_date) >= inter.begin AND TIME_TO_SEC(t.event_date) < inter.end -- типичный случай, определяем номер интервала
then floor((TIME_TO_SEC(t.event_date) - inter.begin) / inter.width) + 1
when TIME_TO_SEC(t.event_date) = inter.end -- особый случай, определяем правильный номер интервала ровно на окончании дневного периода
then floor((inter.end - inter.begin) / inter.width)
when TIME_TO_SEC(t.event_date) < inter.begin -- маркируем номер интервала до начала дневного периода
then -1
when TIME_TO_SEC(t.event_date) > inter.end -- маркируем номер интервала после окончания дневного периода
then -2
else null -- экстраситуация, если when-then охватывают все условия, то сюда никогда не попадем
end as full_interval_number,
case
when TIME_TO_SEC(t.event_date) >= inter.begin AND TIME_TO_SEC(t.event_date) < inter.end -- типичный случай, определяем время начала интервала
then SEC_TO_TIME(floor((TIME_TO_SEC(t.event_date) - inter.begin) / inter.width) * inter.width + inter.begin)
when TIME_TO_SEC(t.event_date) = inter.end -- особый случай, определяем правильное время начала интервала ровно на окончании дневного периода
then SEC_TO_TIME(floor((TIME_TO_SEC(t.event_date) - inter.begin - 1) / inter.width) * inter.width + inter.begin)
when TIME_TO_SEC(t.event_date) < inter.begin -- если за пределами дневного периода, то время окончания за-null-яем
OR TIME_TO_SEC(t.event_date) > inter.end
then null
else null -- экстраситуация, если when-then охватывают все условия, то сюда никогда не попадем
end as full_interval_begin,
case
when TIME_TO_SEC(t.event_date) >= inter.begin AND TIME_TO_SEC(t.event_date) < inter.end -- типичный случай, определяем время окончания интервала
then SEC_TO_TIME(ceil((TIME_TO_SEC(t.event_date) - inter.begin + 1) / inter.width) * inter.width + inter.begin)
when TIME_TO_SEC(t.event_date) = inter.end -- особый случай, определяем правильное время окончания интервала ровно на окончании дневного периода
then SEC_TO_TIME(ceil((TIME_TO_SEC(t.event_date) - inter.begin - 1) / inter.width) * inter.width + inter.begin)
when TIME_TO_SEC(t.event_date) < inter.begin -- если за пределами дневного периода, то время окончания за-null-яем
OR TIME_TO_SEC(t.event_date) > inter.end
then null
else null -- экстраситуация, если when-then охватывают все условия, то сюда никогда не попадем
end as full_interval_end
FROM (select STR_TO_DATE('01.06.2018 08:50:00', '%d.%m.%Y %H:%i:%s') event_date union all -- до дневного периода
select STR_TO_DATE('01.06.2018 08:59:59', '%d.%m.%Y %H:%i:%s') event_date union all -- за секунду до начала дневного периода
select STR_TO_DATE('01.06.2018 09:00:00', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно на начале дневного периода первого интервала
select STR_TO_DATE('01.06.2018 09:00:01', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно после одной секунды в первом интервале дневного периода
select STR_TO_DATE('01.06.2018 09:14:59', '%d.%m.%Y %H:%i:%s') event_date union all -- за секунду до окончания первого интервала
select STR_TO_DATE('01.06.2018 09:15:00', '%d.%m.%Y %H:%i:%s') event_date union all -- начало второго интервала
select STR_TO_DATE('01.06.2018 09:15:01', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно после первой секунды второго интервала
select STR_TO_DATE('01.06.2018 17:59:59', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно за секунду до окончания дневного периода
select STR_TO_DATE('01.06.2018 18:00:00', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно на границе дневного периода
select STR_TO_DATE('01.06.2018 18:00:01', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно секунда после дневного периода
select STR_TO_DATE('01.06.2018 18:10:00', '%d.%m.%Y %H:%i:%s') event_date) t, -- после дневного периода
(select TIME_TO_SEC('09:00:00') as begin, -- начало дневного периода
TIME_TO_SEC('18:00:00') as end, -- окончание дневного периода
TIME_TO_SEC('00:15:00') as width -- ширина интервала
) inter
select * from
(
-- подзапрос интервалов
) a
where
-- исключение определенного перечня интервалов
a.full_interval_number not in (0, 3, 7, 15)
-- отбрасывание из выборки событий между 9 и 10 часами
and TIME_TO_SEC(a.event_date) < TIME_TO_SEC('09:00:00')
and TIME_TO_SEC(a.event_date) > TIME_TO_SEC('10:00:00')
where
a.event_date not in (select time from time where status=0)
FROM (select STR_TO_DATE('01.06.2018 08:50:00', '%d.%m.%Y %H:%i:%s') event_date union all -- до дневного периода
select STR_TO_DATE('01.06.2018 08:59:59', '%d.%m.%Y %H:%i:%s') event_date union all -- за секунду до начала дневного периода
select STR_TO_DATE('01.06.2018 09:00:00', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно на начале дневного периода первого интервала
select STR_TO_DATE('01.06.2018 09:00:01', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно после одной секунды в первом интервале дневного периода
select STR_TO_DATE('01.06.2018 09:14:59', '%d.%m.%Y %H:%i:%s') event_date union all -- за секунду до окончания первого интервала
select STR_TO_DATE('01.06.2018 09:15:00', '%d.%m.%Y %H:%i:%s') event_date union all -- начало второго интервала
select STR_TO_DATE('01.06.2018 09:15:01', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно после первой секунды второго интервала
select STR_TO_DATE('01.06.2018 17:59:59', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно за секунду до окончания дневного периода
select STR_TO_DATE('01.06.2018 18:00:00', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно на границе дневного периода
select STR_TO_DATE('01.06.2018 18:00:01', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно секунда после дневного периода
select STR_TO_DATE('01.06.2018 18:10:00', '%d.%m.%Y %H:%i:%s') event_date) t,
select STR_TO_DATE('01.06.2018 18:10:00', '%d.%m.%Y %H:%i:%s') event_date) t,
2018-06-01 18:00:00 16 17:30:00 18:00:00
2018-06-01 18:00:00 1 09:00:00 09:30:00
2018-06-01 18:00:00 2 09:30:00 10:00:00
2018-06-01 18:00:00 3 10:00:00 10:30:00
...
2018-06-01 18:00:00 16 17:30:00 18:00:00
2018-06-01 18:00:00 1 09:00:00 09:30:00
2018-06-01 18:00:00 2 09:30:00 10:00:00
2018-06-01 18:00:00 3 12:00:00 12:30:00
...
2018-06-01 18:00:00 12 17:30:00 18:00:00
Select 1 (30 min)
1. 0.30 h 09.00.00 - 09.30.00
2. 0.30 h 09.30.00 -10.00.00
3. 0.30 h 10.00.00 -10.30.00
4. 0.30 h 10.30.00 - 11.00.00
5. 0.30 h 11.00.00 - 11.30.00
6. 0.30 h 11.30.00 - 12.00.00
7. 0.30 h 12.00.00 - 12.30.00
8. 0.30 h 12.30.00 - 13.00.00
9. 0.30 h 13.00.00 - 13.30.00
10. 0.30 h 13.30.00 - 14.00.00
11. 0.30 h 14.00.00 - 14.30.00
12. 0.30 h 14.30.00 - 15.00.00
13. 0.30 h 15.00.00 - 15.30.00
14. 0.30 h 15.30.00 - 16.00.00
15. 0.30 h 16.00.00 - 16.30.00
16. 0.30 h 16.30.00 - 17.00.00
Booking
( 0.30 h, 10.00.00 -10.30.00)
1. 0.30 h 09.00.00 - 09.30.00
2. 0.30 h 09.30.00 -10.00.00
3. 0.30 h 10.00.00 -10.30.00
4. 0.30 h 10.30.00 - 11.00.00
5. 0.30 h 11.00.00 - 11.30.00
6. 0.30 h 11.30.00 - 12.00.00
7. 0.30 h 12.00.00 - 12.30.00
8. 0.30 h 12.30.00 - 13.00.00
9. 0.30 h 13.00.00 - 13.30.00
10. 0.30 h 13.30.00 - 14.00.00
11. 0.30 h 14.00.00 - 14.30.00
12. 0.30 h 14.30.00 - 15.00.00
13. 0.30 h 15.00.00 - 15.30.00
14. 0.30 h 15.30.00 - 16.00.00
15. 0.30 h 16.00.00 - 16.30.00
16. 0.30 h 16.30.00 - 17.00.00
Select 2 (1 h)
Time: 10.00.00 -10.30.00
is not in the list
1. 1 h 09.00.00 – 10.00.00
2. 1 h 10.30.00 – 11.30.00
3. 1 h 11.30.00 – 12.30.00
4. 1 h 12.30.00 – 13.30.00
5. 1 h 13.30.00 – 14.30.00
6. 1 h 14.30.00 – 15.30.00
7. 1 h 15.30.00 – 16.30.00
Booking
(1 h, 12.30.00 -13.30.00)
1. 1 h 09.00.00 – 10.00.00
2. 1 h 10.30.00 – 11.30.00
3. 1 h 11.30.00 – 12.30.00
4. 1 h 12.30.00 – 13.30.00
5. 1 h 13.30.00 – 14.30.00
6. 1 h 14.30.00 – 15.30.00
7. 1 h 15.30.00 – 16.30.00
Select 3 (30 min)
Time:
10.00.00 -10.30.00,
12.30.00 – 13.30.00
is not in the list
1. 0.30 h 09.00.00 - 09.30.00
2. 0.30 h 09.30.00 -10.00.00
3. 0.30 h 10.30.00 - 11.00.00
4. 0.30 h 11.00.00 - 11.30.00
5. 0.30 h 11.30.00 - 12.00.00
6. 0.30 h 12.00.00 - 12.30.00
7. 0.30 h 13.30.00 - 14.00.00
8. 0.30 h 14.00.00 - 14.30.00
9. 0.30 h 14.30.00 - 15.00.00
10. 0.30 h 15.00.00 - 15.30.00
11. 0.30 h 15.30.00 - 16.00.00
12. 0.30 h 16.00.00 - 16.30.00
13. 0.30 h 16.30.00 - 17.00.00
Booking
(0.30 h, 14.30.00 -15.00.00)
1. 0.30 h 09.00.00 - 09.30.00
2. 0.30 h 09.30.00 -10.00.00
3. 0.30 h 10.30.00 - 11.00.00
4. 0.30 h 11.00.00 - 11.30.00
5. 0.30 h 11.30.00 - 12.00.00
6. 0.30 h 12.00.00 - 12.30.00
7. 0.30 h 13.30.00 - 14.00.00
8. 0.30 h 14.00.00 - 14.30.00
9. 0.30 h 14.30.00 - 15.00.00
10. 0.30 h 15.00.00 - 15.30.00
11. 0.30 h 15.30.00 - 16.00.00
12. 0.30 h 16.00.00 - 16.30.00
13. 0.30 h 16.30.00 - 17.00.00
Select 4 (1.30 min)
Time:
10.00.00 -10.30.00,
12.30.00 – 13.30.00
14.30.00 - 15.00.00
is not in the list
1. 1.30 h 10.30.00 – 12.00.00
2. 1.30 h 15.00.00 – 16.30.00
С помощью отладочного подзапроса, который представлен вместо таблицы t, можно увидеть особенности нумерации и определения отрезка на границах интервала и всего периода.
(select STR_TO_DATE('01.06.2018 08:50:00', '%d.%m.%Y %H:%i:%s') event_date union all -- до дневного периода
select STR_TO_DATE('01.06.2018 08:59:59', '%d.%m.%Y %H:%i:%s') event_date union all -- за секунду до начала дневного периода
select STR_TO_DATE('01.06.2018 09:00:00', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно на начале дневного периода первого интервала
select STR_TO_DATE('01.06.2018 09:00:01', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно после одной секунды в первом интервале дневного периода
select STR_TO_DATE('01.06.2018 09:14:59', '%d.%m.%Y %H:%i:%s') event_date union all -- за секунду до окончания первого интервала
select STR_TO_DATE('01.06.2018 09:15:00', '%d.%m.%Y %H:%i:%s') event_date union all -- начало второго интервала
select STR_TO_DATE('01.06.2018 09:15:01', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно после первой секунды второго интервала
select STR_TO_DATE('01.06.2018 17:59:59', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно за секунду до окончания дневного периода
select STR_TO_DATE('01.06.2018 18:00:00', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно на границе дневного периода
select STR_TO_DATE('01.06.2018 18:00:01', '%d.%m.%Y %H:%i:%s') event_date union all -- ровно секунда после дневного периода
select STR_TO_DATE('01.06.2018 18:10:00', '%d.%m.%Y %H:%i:%s') event_date) t
select @time_counter := @time_counter + 1 as number, -- номер интервала
SEC_TO_TIME(inter2.width) as width, -- ширина интервала
SEC_TO_TIME(@time_step) as begin, -- начало интервала
SEC_TO_TIME(@time_step := @time_step + inter2.width) as end -- конец интервала
from any_big_table, -- любая таблица, у которой в любую погоду всегда найдется over9000 строк
(
select inter.*, -- передаем константы выше как они есть
@time_step := inter.begin, -- счетчик по времени
@time_counter := 0 -- счетчик по порядку
from (
select TIME_TO_SEC('09:00:00') as begin, -- начало дневного периода
TIME_TO_SEC('18:00:00') as end, -- окончание дневного периода
TIME_TO_SEC('00:15:00') as width -- ширина интервала
) inter -- константно-параметрический подзапрос
) inter2 -- общий параметрический подзапрос, несет на себе функцию инициализации переменных-счетчиков @time_step и @time_counter
where @time_step >= inter2.begin -- нижнее ограничение выборки
and @time_step + inter2.width <= inter2.end -- верхнее ограничение выборки