규칙을 사용하여 시간 간격 가져오기

규칙을 사용하여 시간 간격 가져오기

데이터베이스(mariaDB) 구조는 다음과 같습니다.

CREATE TABLE `events` (
  `ID` int(10) UNSIGNED NOT NULL,
  `evt_id` varchar(10) NOT NULL,
  `service` int(1) DEFAULT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `events` (`ID`, `evt_id`, `service`, `start`, `end`) VALUES
(464, '5423', 2, '2021-06-21 18:00:00', '2021-06-21 19:00:00'),
(465, '5424', 3, '2021-06-22 18:00:00', '2021-06-22 19:00:00'),
(474, '5436', 0, '2021-06-19 09:00:00', '2021-06-19 10:00:00'),
(475, '5437', 0, '2021-06-23 18:00:00', '2021-06-23 21:00:00'),
(476, '5438', 0, '2021-06-24 16:00:00', '2021-06-24 17:00:00'),
(477, '5439', 2, '2021-06-21 16:00:00', '2021-06-21 17:00:00'),
(478, '5441', 2, '2021-06-21 17:00:00', '2021-06-21 18:00:00'),
(479, '5442', 1, '2021-06-30 17:00:00', '2021-06-30 18:00:00'),
(480, '5443', 1, '2021-06-28 17:00:00', '2021-06-28 18:00:00'),
(484, '5447', 1, '2021-06-23 17:00:00', '2021-06-23 18:00:00'),
(486, '5449', 2, '2021-06-22 17:00:00', '2021-06-22 18:00:00'),
(491, '5450', 2, '2021-06-23 16:00:00', '2021-06-23 17:00:00'),
(494, '5455', 1, '2021-06-25 15:00:00', '2021-06-25 16:00:00'),
(495, '5456', 2, '2021-06-25 16:00:00', '2021-06-25 17:00:00'),
(496, '5457', 1, '2021-06-25 17:00:00', '2021-06-25 18:00:00'),
(497, '5458', 3, '2021-06-25 18:00:00', '2021-06-25 19:00:00'),
(498, '5459', 2, '2021-06-25 14:00:00', '2021-06-25 15:00:00');

https://dbfiddle.uk/ ?rdbms=httpadb_10.5&httpdle=e747c069fd2813d1f936d0a77a41a6c7

제 테이블에는 예약된 모든 이벤트가 있습니다.현재 날짜부터 현재 날짜까지 자유로운 시간대 + 30일, 14:00에서 18:00 사이에만 가능한 시간대를 알고 싶습니다.

이에 대해 다음과 같은 질문이 있습니다.

SELECT 
DATE_ADD(DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY), INTERVAL day.seq DAY), INTERVAL hour.seq HOUR) as freetime
FROM 
seq_0_to_30 AS day, 
seq_14_to_18 AS hour
HAVING freetime NOT IN ( SELECT `start` FROM `events`)

잘 작동합니다! 하지만 이제 제 질문을 확장해야 합니다.위와 같은 빈 시간대를 알고 싶습니다만, 만약 서비스 ID가 1인 이벤트가 당일 예약되었다면 이후의 모든 시간대는 이용할 수 없습니다.

이를 실현하려면 어떻게 쿼리를 수정해야 합니까?

예:

2021-06-28 17:00:00에 서비스 ID 1로 예약된 이벤트가 있습니다. 위의 질문과 함께 다음과 같은 빈 시간 슬롯이 표시됩니다.

2021-06-28 14:00:00
2021-06-28 15:00:00
2021-06-28 16:00:00
2021-06-28 18:00:00

그러나 이전 이벤트(17:00:00)의 서비스 ID가 1이기 때문에 18:00:00이 있는 이벤트는 사용할 수 없습니다.

주의: mariaDB 쿼리 대신 mysql 쿼리를 사용할 수도 있습니다.

존재 및 사이를 사용할 수 있습니다.

CREATE TABLE `events` (
  `ID` int(10) UNSIGNED NOT NULL,
  `evt_id` varchar(10) NOT NULL,
  `service` int(1) DEFAULT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `events` (`ID`, `evt_id`, `service`, `start`, `end`) VALUES
(464, '5423', 2, '2021-06-21 18:00:00', '2021-06-21 19:00:00'),
(465, '5424', 3, '2021-06-22 18:00:00', '2021-06-22 19:00:00'),
(474, '5436', 0, '2021-06-19 09:00:00', '2021-06-19 10:00:00'),
(475, '5437', 0, '2021-06-23 18:00:00', '2021-06-23 21:00:00'),
(476, '5438', 0, '2021-06-24 16:00:00', '2021-06-24 17:00:00'),
(477, '5439', 2, '2021-06-21 16:00:00', '2021-06-21 17:00:00'),
(478, '5441', 2, '2021-06-21 17:00:00', '2021-06-21 18:00:00'),
(479, '5442', 1, '2021-06-30 17:00:00', '2021-06-30 18:00:00'),
(480, '5443', 1, '2021-06-28 17:00:00', '2021-06-28 18:00:00'),
(484, '5447', 1, '2021-06-23 17:00:00', '2021-06-23 18:00:00'),
(486, '5449', 2, '2021-06-22 17:00:00', '2021-06-22 18:00:00'),
(491, '5450', 2, '2021-06-23 16:00:00', '2021-06-23 17:00:00'),
(494, '5455', 1, '2021-06-25 15:00:00', '2021-06-25 16:00:00'),
(495, '5456', 2, '2021-06-25 16:00:00', '2021-06-25 17:00:00'),
(496, '5457', 1, '2021-06-25 17:00:00', '2021-06-25 18:00:00'),
(497, '5458', 3, '2021-06-25 18:00:00', '2021-06-25 19:00:00'),
(498, '5459', 2, '2021-06-25 14:00:00', '2021-06-25 15:00:00');

SELECT 
DATE_ADD(DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY), INTERVAL day.seq DAY), INTERVAL hour.seq HOUR) as freetime
FROM 
seq_0_to_30 AS day, 
seq_14_to_18 AS hour
HAVING NOT EXISTS ( SELECT 1 FROM `events` WHERE  freetime >=  `start` AND  freetime < `end`
OR IF(`service` = 1,IF(DAY(freetime) = DAY(`start`),1,0),0))

자유 시간 || :------------------ || 2021-06-24 14:00:00 || 2021-06-24 15:00:00 || 2021-06-24 17:00:00 || 2021-06-24 18:00:00 || 2021-06-26 14:00:00 || 2021-06-26 15:00:00 || 2021-06-26 16:00:00 || 2021-06-26 17:00:00 || 2021-06-26 18:00:00 || 2021-06-27 14:00:00 || 2021-06-27 15:00:00 || 2021-06-27 16:00:00 || 2021-06-27 17:00:00 || 2021-06-27 18:00:00 || 2021-06-29 14:00:00 || 2021-06-29 15:00:00 || 2021-06-29 16:00:00 || 2021-06-29 17:00:00 || 2021-06-29 18:00:00 || 2021-07-01 14:00:00 || 2021-07-01 15:00:00 || 2021-07-01 16:00:00 || 2021-07-01 17:00:00 || 2021-07-01 18:00:00 || 2021-07-02 14:00:00 || 2021-07-02 15:00:00 || 2021-07-02 16:00:00 || 2021-07-02 17:00:00 || 2021-07-02 18:00:00 || 2021-07-03 14:00:00 || 2021-07-03 15:00:00 || 2021-07-03 16:00:00 || 2021-07-03 17:00:00 || 2021-07-03 18:00:00 || 2021-07-04 14:00:00 || 2021-07-04 15:00:00 || 2021-07-04 16:00:00 || 2021-07-04 17:00:00 || 2021-07-04 18:00:00 || 2021-07-05 14:00:00 || 2021-07-05 15:00:00 || 2021-07-05 16:00:00 || 2021-07-05 17:00:00 || 2021-07-05 18:00:00 || 2021-07-06 14:00:00 || 2021-07-06 15:00:00 || 2021-07-06 16:00:00 || 2021-07-06 17:00:00 || 2021-07-06 18:00:00 || 2021-07-07 14:00:00 || 2021-07-07 15:00:00 || 2021-07-07 16:00:00 || 2021-07-07 17:00:00 || 2021-07-07 18:00:00 || 2021-07-08 14:00:00 || 2021-07-08 15:00:00 || 2021-07-08 16:00:00 || 2021-07-08 17:00:00 || 2021-07-08 18:00:00 || 2021-07-09 14:00:00 || 2021-07-09 15:00:00 || 2021-07-09 16:00:00 || 2021-07-09 17:00:00 || 2021-07-09 18:00:00 || 2021-07-10 14:00:00 || 2021-07-10 15:00:00 || 2021-07-10 16:00:00 || 2021-07-10 17:00:00 || 2021-07-10 18:00:00 || 2021-07-11 14:00:00 || 2021-07-11 15:00:00 || 2021-07-11 16:00:00 || 2021-07-11 17:00:00 || 2021-07-11 18:00:00 || 2021-07-12 14:00:00 || 2021-07-12 15:00:00 || 2021-07-12 16:00:00 || 2021-07-12 17:00:00 || 2021-07-12 18:00:00 || 2021-07-13 14:00:00 || 2021-07-13 15:00:00 || 2021-07-13 16:00:00 || 2021-07-13 17:00:00 || 2021-07-13 18:00:00 || 2021-07-14 14:00:00 || 2021-07-14 15:00:00 || 2021-07-14 16:00:00 || 2021-07-14 17:00:00 || 2021-07-14 18:00:00 || 2021-07-15 14:00:00 || 2021-07-15 15:00:00 || 2021-07-15 16:00:00 || 2021-07-15 17:00:00 || 2021-07-15 18:00:00 || 2021-07-16 14:00:00 || 2021-07-16 15:00:00 || 2021-07-16 16:00:00 || 2021-07-16 17:00:00 || 2021-07-16 18:00:00 || 2021-07-17 14:00:00 || 2021-07-17 15:00:00 || 2021-07-17 16:00:00 || 2021-07-17 17:00:00 || 2021-07-17 18:00:00 || 2021-07-18 14:00:00 || 2021-07-18 15:00:00 || 2021-07-18 16:00:00 || 2021-07-18 17:00:00 || 2021-07-18 18:00:00 || 2021-07-19 14:00:00 || 2021-07-19 15:00:00 || 2021-07-19 16:00:00 || 2021-07-19 17:00:00 || 2021-07-19 18:00:00 || 2021-07-20 14:00:00 || 2021-07-20 15:00:00 || 2021-07-20 16:00:00 || 2021-07-20 17:00:00 || 2021-07-20 18:00:00 || 2021-07-21 14:00:00 || 2021-07-21 15:00:00 || 2021-07-21 16:00:00 || 2021-07-21 17:00:00 || 2021-07-21 18:00:00 || 2021-07-22 14:00:00 || 2021-07-22 15:00:00 || 2021-07-22 16:00:00 || 2021-07-22 17:00:00 || 2021-07-22 18:00:00 |

db<>여기로 이동

언급URL : https://stackoverflow.com/questions/68065757/get-time-slots-with-rules