[Mysql] 달력 시간예약 질문 드리겠습니다 0 9 3,179

by 오라클네티 [SQL Query] mysql [2019.02.12 17:12:25]


안녕하세요 여러분, 달력에서 데이터를 입력받아,

년,월,일 시간을 입력받고있습니다.

 

입력받은 날짜와 시간값으로 달력에 예약이 완료되면, 예약완료되었다는 표시를 보여줘야하는데,

몇일째 고민하다가 어떻게 해결해야 할지 감이 안잡혀서 질문을 드립니다.

 

CHECK_IN_DAY , CHECK_IN_TIME , CHECK_OUT_DAY , CHECK_OUT_TIME 와 같이 컬럼이 있습니다.

 

샘플 데이터는 아래와 같습니다.

 

2019-02-12 00:00 2019-02-13 00:00
2019-02-13 01:00 2019-02-13 03:00
2019-02-13 03:00 2019-02-13 05:00
2019-02-13 05:00 2019-02-13 07:00
2019-02-13 07:00 2019-02-13 09:00
2019-02-13 15:00 2019-02-13 18:00
2019-02-13 23:00 2019-02-14 01:00
2019-02-14 01:00 2019-02-14 03:00
2019-02-14 22:00 2019-02-15 01:00
2019-02-15 01:00 2019-02-15 02:00
2019-02-15 02:00 2019-02-15 05:00
2019-02-15 05:00 2019-02-15 07:00
2019-02-15 07:00 2019-02-15 23:00
2019-02-15 23:00 2019-02-16 00:00
2019-02-16 00:00 2019-02-16 01:00
2019-02-17 01:00 2019-02-17 02:00
2019-02-19 01:00 2019-02-20 02:00
2019-02-26 00:00 2019-02-27 00:00
2019-02-28 00:00 2019-02-28 01:00
2019-02-28 01:00 2019-02-28 02:00
2019-02-28 02:00 2019-02-28 03:00
2019-02-28 03:00 2019-02-28 04:00
2019-02-28 05:00 2019-02-28 06:00
2019-02-28 06:00 2019-02-28 07:00
2019-02-28 08:00 2019-02-28 09:00
2019-02-28 14:00 2019-02-28 15:00
2019-02-28 15:00 2019-02-28 16:00
2019-02-28 16:00 2019-02-28 17:00

 

체크인 날로 부터 24시까지 모두 예약이 완료되면 해당일은 예약이 완료 되었다고 표시해야합니다.

예를들어

 

체크인                      체크아웃

2019-02-13 01:00 ~ 2019-02-14 02:00

이렇게 예약이 되면

2019-02-13 00:00 ~ 2019-02-13 01:00 까지의 1시간 예약가능하기떄문에 예약이 모두 다찬것은 아닙니다.

 

00:00 ~ 24:00 시간이 꽉차야지만 해당일의 예약이 모두 다찬것으로 보여지는데요.

 

2019-02-14 05:00 ~ 2019-02-16 17:00 이렇게 연박도 가능하고

 

문제가 쉽지않아 도움을 요청드립니다ㅠ 도와주세요.

 

 

 

 

 

by 마농 [2019.02.12 17:42:29]

MySQL 버전이 어떻게 되나요?
달력 테이블은 따로 있나요?


by 오라클네티 [2019.02.12 17:44:26]

mysql 버전은 5.6.41-log 버전을 사용중입니다.

달력 테이블은 따로없이 , 위와같이 테이블에 데이터가 들어있습니다.


by 마농 [2019.02.12 17:53:19]

달력이 있어야 할 것 같은데요? 원하시는 결과가 달력 형태 아닌가요?
아니면 특정 일자 기준으로 결과 리턴하면 되는 건지요?


by 오라클네티 [2019.02.12 18:01:01]

달력은 아래와 같은 쿼리로 보여주고있습니다.

달력형태가 아닌 로우형태여도 상관없습니다.


 SELECT ym
     , MIN(CASE dw WHEN 1 THEN d END) Sun
     , MIN(CASE dw WHEN 2 THEN d END) Mon
     , MIN(CASE dw WHEN 3 THEN d END) Tue
     , MIN(CASE dw WHEN 4 THEN d END) Wed
     , MIN(CASE dw WHEN 5 THEN d END) Thu
     , MIN(CASE dw WHEN 6 THEN d END) Fri
     , MIN(CASE dw WHEN 7 THEN d END) Sat
  FROM (SELECT date_format(dt,'%Y%m') ym
             , Week(dt) w
             , Day(dt) d
             , DayofWeek(dt) dw
          FROM (SELECT CONCAT(y, '0101') + INTERVAL a*100 + b*10 + c DAY dt
                  FROM (SELECT 0 a
                        UNION ALL SELECT 1
                        UNION ALL SELECT 2
                        UNION ALL SELECT 3
                        ) a
                     , (SELECT 0 b
                        UNION ALL SELECT 1
                        UNION ALL SELECT 2
                        UNION ALL SELECT 3
                        UNION ALL SELECT 4
                        UNION ALL SELECT 5
                        UNION ALL SELECT 6
                        UNION ALL SELECT 7
                        UNION ALL SELECT 8
                        UNION ALL SELECT 9
                        ) b
                     , (SELECT 0 c
                        UNION ALL SELECT 1
                        UNION ALL SELECT 2
                        UNION ALL SELECT 3
                        UNION ALL SELECT 4
                        UNION ALL SELECT 5
                        UNION ALL SELECT 6
                        UNION ALL SELECT 7
                        UNION ALL SELECT 8
                        UNION ALL SELECT 9
                        ) c
                     , (SELECT '2019' y) d
                 WHERE a*100 + b*10 + c < DayOfYear(CONCAT(y, '1231'))
                ) a
        ) a
        where ym = '201901'
 GROUP BY ym, w;
 

 

 


by 마농 [2019.02.12 18:06:49]

매번 이렇게 하실거면 달력을 미리 만들어 두고 사용하시면 편리합니다.
테이블의 4개 항목의 자료형은 모두 문자인가요?
아니면 date, time 형인가요?


by 오라클네티 [2019.02.12 18:09:05]

4개 컬럼모두 varchar type 으로 구성되어있습니다


by 오라클네티 [2019.02.12 18:10:34]

테이블을 만들어놓는건 미쳐 생각을 못했네요

감사합니다


by 마농 [2019.02.12 18:55:21]
WITH RECURSIVE calendar_t AS
(
SELECT '2019-02-01' dt
 UNION ALL
SELECT dt + INTERVAL 1 DAY
  FROM calendar_t
 WHERE dt < '2019-02-28'
)
, data_t AS
(
SELECT '2019-02-12' check_in_day, '00:00' check_in_time, '2019-02-13' check_out_day, '00:00' check_out_time
UNION ALL SELECT '2019-02-13', '01:00', '2019-02-13', '03:00'
UNION ALL SELECT '2019-02-13', '03:00', '2019-02-13', '05:00'
UNION ALL SELECT '2019-02-13', '05:00', '2019-02-13', '07:00'
UNION ALL SELECT '2019-02-13', '07:00', '2019-02-13', '09:00'
UNION ALL SELECT '2019-02-13', '15:00', '2019-02-13', '18:00'
UNION ALL SELECT '2019-02-13', '23:00', '2019-02-14', '01:00'
UNION ALL SELECT '2019-02-14', '01:00', '2019-02-14', '03:00'
UNION ALL SELECT '2019-02-14', '22:00', '2019-02-15', '01:00'
UNION ALL SELECT '2019-02-15', '01:00', '2019-02-15', '02:00'
UNION ALL SELECT '2019-02-15', '02:00', '2019-02-15', '05:00'
UNION ALL SELECT '2019-02-15', '05:00', '2019-02-15', '07:00'
UNION ALL SELECT '2019-02-15', '07:00', '2019-02-15', '23:00'
UNION ALL SELECT '2019-02-15', '23:00', '2019-02-16', '00:00'
UNION ALL SELECT '2019-02-16', '00:00', '2019-02-16', '01:00'
UNION ALL SELECT '2019-02-17', '01:00', '2019-02-17', '02:00'
UNION ALL SELECT '2019-02-19', '01:00', '2019-02-20', '02:00'
UNION ALL SELECT '2019-02-26', '00:00', '2019-02-27', '00:00'
UNION ALL SELECT '2019-02-28', '00:00', '2019-02-28', '01:00'
UNION ALL SELECT '2019-02-28', '01:00', '2019-02-28', '02:00'
UNION ALL SELECT '2019-02-28', '02:00', '2019-02-28', '03:00'
UNION ALL SELECT '2019-02-28', '03:00', '2019-02-28', '04:00'
UNION ALL SELECT '2019-02-28', '05:00', '2019-02-28', '06:00'
UNION ALL SELECT '2019-02-28', '06:00', '2019-02-28', '07:00'
UNION ALL SELECT '2019-02-28', '08:00', '2019-02-28', '09:00'
UNION ALL SELECT '2019-02-28', '14:00', '2019-02-28', '15:00'
UNION ALL SELECT '2019-02-28', '15:00', '2019-02-28', '16:00'
UNION ALL SELECT '2019-02-28', '16:00', '2019-02-28', '17:00'
)
-- MariaDB 10.3 에서 테스트 했습니다. With 문 빼고 아래 쿼리만 봐주세요.
SELECT dt
     , IFNULL(FLOOR(SUM(TIMEDIFF(e, s))/10000), 0) tm  -- 예약시간
  FROM (SELECT a.dt
             , GREATEST(ADDTIME(ADDDATE(dt, 0), 0), ADDTIME(ADDDATE(check_in_day , 0), check_in_time )) s
             , LEAST   (ADDTIME(ADDDATE(dt, 1), 0), ADDTIME(ADDDATE(check_out_day, 0), check_out_time)) e
          FROM calendar_t a
          LEFT OUTER JOIN data_t b
            ON a.dt BETWEEN b.check_in_day AND b.check_out_day
         WHERE a.dt LIKE '2019-02%'
        ) a
 GROUP BY dt
;

 


by 오라클네티 [2019.02.12 19:12:10]

정말 너무 감사합니다.

너무 원했던 쿼리였습니다

쿼리보고 더 공부해야겠습니다ㅠ

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입