데이터베이스 질문과 답변
WITH T AS
(
SELECT TO_CHAR(TO_DATE('08:35', 'HH24:MI'), 'HH24:MI') STIME FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('08:50', 'HH24:MI'), 'HH24:MI') FROM DUAL
UNION ALL
SELECT NULL FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('07:20', 'HH24:MI'), 'HH24:MI') FROM DUAL
)
SELECT STIME FROM T
에서 STIME의 합계를 구하고 싶은데 어떤식으로 하면 될까요~?
부탁 드립니다;;;
시간을 분으로 환산하셔서 . sum하시고 다시 60으로 나누시면 시간 + 나머지 분 이렇게 나오지 않나요?
WITH T AS
(
SELECT TO_CHAR(TO_DATE('08:35', 'HH24:MI'), 'HH24MI') STIME FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('08:50', 'HH24:MI'), 'HH24MI') FROM DUAL
UNION ALL
SELECT NULL FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('07:20', 'HH24:MI'), 'HH24MI') FROM DUAL
)
select trunc(sum(nvl(to_number(substr(stime, 1, 2))*60 + to_number(substr(stime, 3, 2)), 0))/60)||':'||
mod(sum(nvl(to_number(substr(stime, 1, 2))*60 + to_number(substr(stime, 3, 2)), 0)), 60)
from t;
WITH t AS
(
SELECT TO_CHAR(TO_DATE('08:35', 'HH24:MI'), 'HH24:MI') stime FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('08:50', 'HH24:MI'), 'HH24:MI') FROM DUAL
UNION ALL
SELECT NULL FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('07:20', 'HH24:MI'), 'HH24:MI') FROM DUAL
)
SELECT TRUNC ( time ) hour
, MOD ( time , 1 ) * 60 time
FROM (
SELECT SUM ( TO_NUMBER ( TO_CHAR ( TO_DATE ( stime, 'HH24:MI' ), 'SSSSS' ) ) ) / 60 / 60 time
FROM t
)
) 버튼을 클릭하여 작성 하시면 됩니다.