오라클 두 날짜 사이의 시간 분 계산

각설 하고 바로 예시.

 

WITH T AS ( SELECT to_date('20190116' || '1830', 'yyyyMMddHH24mi') AS ed,  to_date('20190116' || '1700', 'yyyyMMddHH24mi') AS bd 

FROM dual
)

SELECT FLOOR((ed - bd)*24) hh, 

( (ed - bd)*24 - FLOOR((ed - bd)*24) )*60 mm 

FROM t

;

 

select day
     , hour - (day * 24)   as hour
     , min - (hour * 60)   as min
     , second - (min * 60) as second

from (select START_DATE
           , END_DATE
           , trunc((END_DATE - START_DATE), 0)                as day
           , trunc((END_DATE - START_DATE) * 24, 0)           as hour
           , trunc((END_DATE - START_DATE) * 24 * 60, 0)      as min
           , trunc((END_DATE - START_DATE) * 24 * 60 * 60, 0) as second
      from time_gap)
;