[Oracle] 시간간격 구하기

1. 일반적인 경우(초)

SELECT 
ROUND (
    MOD
    (
        (
        TO_DATE('20120623152300', 'YYYYMMDDHH24MISS') - 
        TO_DATE('20120623152100', 'YYYYMMDDHH24MISS')
        ), 60
    ) * 24 * 60 * 60 
) SEC
FROM DUAL;

SEC    

------ 

   120

2. NUMTODSINTERVAL() 함수 사용

SELECT 
  TO_NUMBER (SUBSTR (DIFF, 2, 9)) DAY
, TO_NUMBER (SUBSTR (DIFF, 12, 2)) HOUR
, TO_NUMBER (SUBSTR (DIFF, 15, 2)) MINUTE
, TO_NUMBER (SUBSTR (DIFF, 18, 2)) SECOND
FROM    (
        SELECT NUMTODSINTERVAL (    TO_DATE (TIME2, 'YYYYMMDDHH24MISS')
                                  - TO_DATE (TIME1, 'YYYYMMDDHH24MISS'), 'DAY') DIFF
        FROM    (
                SELECT '20111125203138' TIME1, '20111203123557' TIME2
                FROM DUAL)
                );

DAY    HOUR     MINUTE       SECOND       

------ -------- ------------ ------------ 

     7       16            4           19