mysql 시간 계산 질문있습니다. 0 9 1,465

by 도리비 [SQL Query] [2020.02.04 17:47:33]


시작시간(start)과 종료시간(end)의 실제 소요시간을 계산하고 싶습니다.

전제조건은 아래와 같습니다.

1. 평일 근무시간은 06시 ~ 22시 까지이다.

2. 주말은 금요일 22시 부터 월요일 06시 까지다

실제 소요시간을 계산해라.

 

Ex.

평일 오후 6시에 시작됐고 23시에 종료 됐을 경우 소요시간은 4시간임.

금요일 오후 22시에 시작됐고 월요일 오전 6시에 종료 됐을 경우 소요시간은 0시간 0분임

월요일 오후 6시에 시작됐고 화요일 오전 9시에 종료 됐을 경우 소요시간은 7시간임

월요일 오후 6시에 시작됐고 월요일 오후 11시에 종료 됐을 경우 소요시간은 4시간임

프로그램으로 만들고 있는데 쿼리로 될까해서 질문올려봅니다...

by 마농 [2020.02.05 09:11:17]

데이터는 어떤 형태로 저장되나요?
DATETIME 형태의 시작일시 종료일시 형태인가요?
아니면 문자형인 VARCHAR 형태인가요?
문자형이라면 관리되는 포멧이 어떻게 되는지?
일자와 시간이 하나의 항목인지? 분리되어 있는지?
시간까지만 관리하는지? 분 초 단위까지 관리 되는지?


by 도리비 [2020.02.05 09:16:37]

마농님 안녕하세요 !!! 늘 감사드립니다 !!!

DateTime 형태의 시작과 종료 입니다.

일자와 시간이 하나의 항목이며 분 단위까지 관리합니다!


by 마농 [2020.02.05 09:40:48]
WITH t AS
(
SELECT 1 idx, '2020.02.04 18:00:00' sdt, '2020.02.04 23:00:00' edt
UNION ALL SELECT 2, '2020.02.07 22:00:00', '2020.02.10 06:00:00'
UNION ALL SELECT 3, '2020.02.10 18:00:00', '2020.02.11 09:00:00'
UNION ALL SELECT 4, '2020.02.10 18:00:00', '2020.02.10 23:00:00'
UNION ALL SELECT 5, '2020.02.10 17:30:30', '2020.02.13 10:10:10'
)
, t1 AS
(
SELECT idx
     , CAST(sdt AS DATETIME) sdt
     , CAST(edt AS DATETIME) edt
  FROM t
)
, copy_t AS
(
SELECT 0 lv
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
)
SELECT idx
     , sdt
     , edt
     , FLOOR(sec / 60 / 60) hh
     , MOD(FLOOR(sec / 60), 60) mi
     , MOD(sec, 60) ss
  FROM (SELECT idx
             , sdt
             , edt
             , SUM( HOUR  (TIMEDIFF(e, s)) * 60 * 60
                  + MINUTE(TIMEDIFF(e, s)) * 60
                  + SECOND(TIMEDIFF(e, s))
                  ) sec
          FROM (SELECT idx
                     , sdt
                     , edt
                     , GREATEST(sdt, DATE(sdt) + INTERVAL lv DAY + INTERVAL  6 HOUR) s
                     , LEAST   (edt, DATE(sdt) + INTERVAL lv DAY + INTERVAL 22 HOUR) e
                     , DAYOFWEEK(DATE(sdt) + INTERVAL lv DAY) dy
                  FROM t1 a
                 INNER JOIN copy_t b
                    ON lv <= DATEDIFF(DATE(edt), DATE(sdt))
                ) a
         WHERE dy NOT IN (1, 7)
         GROUP BY idx, sdt, edt
        ) a
;

 


by 도리비 [2020.02.05 09:58:05]

답변 감사합니다..근데 확인을 어떻게하나요..? 쿼리를 날리면 Syntax 에러가 나옵니다..ㅜ


by 마농 [2020.02.05 10:11:22]

MariaDB 10.4 에서 테스트 했습니다.
WITH 구문 은 제가 샘플 데이터로 테스트 하기 위해 만든 구문입니다.
MySQL 버전에 따라 WITH 구문이 동작하지 않을 수 있습니다.
SELECT 이후 구문들이 어떤 방식으로 작성되어 있는지를 보세요.


by 도리비 [2020.02.05 10:23:20]

정말 감사드립니다. 

Maria에서 정상동작 확인했습니다. 감사합니다!


by 마농 [2020.02.06 17:01:30]

쿼리에 오류가 있어서 수정합니다.

-- 쿼리에 오류가 있어서 수정합니다.
WITH t AS
(
SELECT 1 idx, '2020.02.04 18:00:00' sdt, '2020.02.04 23:00:00' edt
UNION ALL SELECT 2, '2020.02.07 22:00:00', '2020.02.10 06:00:00'
UNION ALL SELECT 3, '2020.02.10 18:00:00', '2020.02.11 09:00:00'
UNION ALL SELECT 4, '2020.02.10 18:00:00', '2020.02.10 23:00:00'
UNION ALL SELECT 5, '2020.02.10 17:30:30', '2020.02.13 10:10:10'
UNION ALL SELECT 6, '2020.02.13 04:00:00', '2020.02.13 05:00:00'  -- test 추가
UNION ALL SELECT 7, '2020.02.13 22:30:00', '2020.02.13 23:30:00'  -- test 추가
UNION ALL SELECT 8, '2020.02.13 23:00:00', '2020.02.14 05:00:00'  -- test 추가
)
, t1 AS
(
SELECT idx
     , CAST(sdt AS DATETIME) sdt
     , CAST(edt AS DATETIME) edt
  FROM t
)
, copy_t AS
(
SELECT 0 lv
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
)
SELECT idx
     , sdt
     , edt
     , FLOOR(sec / 60 / 60) hh
     , MOD(FLOOR(sec / 60), 60) mi
     , MOD(sec, 60) ss
  FROM (SELECT idx
             , sdt
             , edt
             , SUM(CASE WHEN s < e                        -- 오류 보정
                   THEN HOUR  (TIMEDIFF(e, s)) * 60 * 60
                      + MINUTE(TIMEDIFF(e, s)) * 60
                      + SECOND(TIMEDIFF(e, s))
                   ELSE 0 END
                   ) sec
          FROM (SELECT idx
                     , sdt
                     , edt
                     , GREATEST(sdt, DATE(sdt) + INTERVAL lv DAY + INTERVAL  6 HOUR) s
                     , LEAST   (edt, DATE(sdt) + INTERVAL lv DAY + INTERVAL 22 HOUR) e
                     , DAYOFWEEK(DATE(sdt) + INTERVAL lv DAY) dy
                  FROM t1 a
                 INNER JOIN copy_t b
                    ON lv <= DATEDIFF(DATE(edt), DATE(sdt))
                ) a
         WHERE dy NOT IN (1, 7)
         GROUP BY idx, sdt, edt
        ) a
;

 


by 도리비 [2020.02.06 18:10:06]

답변 감사합니다. 오류 수정까지..

몇가지 궁금한것이 있습니다.

1. 실제 테이블에 적용해서 계산해보니 다르게 나오는건 왜 그런걸까요?

WITH t 에 넣었을 때 문제되는 값이 정상적으로 계산되어지는데

실제 테이블로 여러개의 값에 적용해보면 WITH t 값과 계산된 값이 차이가 좀 많이 나는 경우가 있습니다

 

2. copy_t 데이터를 9까지 넣으신 이유가 무엇인가요?


3. ON lv <= DATEDIFF(DATE(edt), DATE(sdt)) 값에서 9 이상 값이 나올 경우 다중 행이 나오는데 

lv 값을 보면 0 아니면 1이 나옵니다 이값에 의미는 뭔지 알 수 있을까요?

4. WITH copy_t 를 없애려면 어떤 방식을 취하면 될까요?


by 마농 [2020.02.07 07:35:08]

1. 결과가 다른 이유는?
 - 글쎄요? 제 쿼리엔 문제가 없는데? 적용하신 쿼리에 문제가 있다면?
 - 스스로 찾아야죠.
2. copy_t 에 9까지 적용한 이유는?
 - 10일 정도 생각해서 9로 했습니다.
 - 그 이상 소요되는 자료가 있다면? 최대치 이상으로 잡아줘야죠.
3. copy_t 의 lv 는
 - 데이터 복제용으로 만든 것입니다.
 - 소요일만큼 레코드가 복제됩니다.
4. WITH copy_t 를 제거하는 방법은?
 - 실제 copy_t 테이블을 만드시면 됩니다.

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