JNB
rss

skin by 이글루스

오라클

[Oracle/10g] numtodsinterval()을 이용하여 시간차이 계산하기 15.01.14 23:01
[Oracle/10g] numtodsinterval()을 이용하여 시간차이 계산하기

Posted on 4 of December, 2011 by simplism        in 오라클        


사용환경
•Database : Oracle 10g

01. 시작하기 전에…

사내전산팀을 유지보수하는 개발자로 이제 벌써 9개월이나 되었습니다. 이렇게 일하다보면 금세 3~4년은 지나갈 것 같더군요. 개발자로 일하면서 느낀 것 중에 하나는 RDBMS는 양날의 검이라는 것입니다. 잘 사용하면 나를 살리고 내 가족을 살리겠지만, 잘 못 사용하면 나 자신을 죽게만드는 무서운 도구라는 말입니다.

학교에서 오라클을 배울 때는 정말 간단한 것만 배워서… 처음 일을 시작할 때 엄청난 길이의 쿼리와 그 복잡도에 두려움이 앞섰었지만, 이제는 꽤나 익숙해져서 두려움을 느끼진 않게 되었네요… 그렇지만 여전히 기존에 운영 중인 쿼리를 변경하거나, 개발 당시에는 데이터의 양이 적어서 성능이슈가 없었던 쿼리를 튜닝하게 될 때는 그 복잡함에 치가 떨린 적도 한 두 번이 아닙니다.

다른 RDBMS를 사용해보지 않아서 잘은 모르겠지만, 오라클에는 정말 다양한 내장함수들이 있습니다. 그 중에서 이 포스트는 numtodsinterval()이라는 내장함수를 활용하여 두 시간의 차이를 계산하는 법을 설명해볼까 합니다.

02. 날짜 데이터형

오라클에서 날짜와 관련된 기본적인 데이터는 DATE형입니다. 그렇지만 여러 가지 이유(UI프레임워크에서 사용하기 편하게…)로 DATE형을 사용하지 않고 문자형 데이터인 VARCHAR2로 보관하는 경우가 있습니다. 여기에서도 VARCHAR2형으로 되어있는 날짜데이터를 활용하여 계산을 하려고 합니다.

VARCHAR2와 DATE형간의 형변환은 아래와 같습니다.

1) VARCHAR2 -> DATE 형변환
1.SELECT '20111125203138' time1, '20111203123557' time2
2.  FROM DUAL;



두 개의 날짜 데이터가 있다고 가정합니다. ‘YYYYMMDDhhmmss’의 형태입니다. 오라클의 방식으로는 YYYYMMDDHH24MISS이지만…
1.TIME1          TIME2
2.-------------- --------------
3.20111125203138 20111203123557

1.SELECT TO_DATE (time1, 'YYYYMMDDHH24MISS') time1_converted
2.      , TO_DATE (time2, 'YYYYMMDDHH24MISS') time2_converted
3.  FROM (SELECT '20111125203138' time1
4.              , '20111203123557' time2
5.          FROM DUAL);



VARCHAR2 -> DATE형으로의 변환은 위처럼 TO_DATE() 내장함수를 이용하면 됩니다.
1.TIME1_CONVERTED TIME2_CONVERTED
2.--------------- ---------------
3.11/11/25        11/12/03


변환이 완료되면, DATE형 간의 연산이 가능하여 날짜의 차이를 구할 수 있습니다.

2) DATE -> VARCHAR2 형변환

유사한 방법으로 반대로의 형변환도 가능합니다.
1.SELECT TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') time_converted
2. FROM DUAL;


1.TIME_CONVERTED
2.--------------
3.20111203205643


TO_CHAR() 내장함수를 이용하면, 현재 시간을 VARCHAR2형 문자열로 반환합니다.

03. 간단한 날짜차이 계산하기

두 날짜의 차이를 계산하는 간단한 방법은 DATE형으로 변환하여 서로 빼는 것입니다.
1.SELECT TO_DATE (time2, 'YYYYMMDDHH24MISS')
2.      - TO_DATE (time1, 'YYYYMMDDHH24MISS') as timediff
3.  FROM (SELECT '20111125203138' time1
4.               , '20111203123557' time2
5.           FROM DUAL);


1.  TIMEDIFF
2.----------
3.7.66966435


위 처럼 DATE형을 (-) 연산자로 단순하게 연산을 하면, 두 시간의 날짜차이를 간단하게 구할 수 있습니다.

문제는 날짜의 차이 뿐만 아니라 시간, 분, 초 단위까지의 차이를 알고 싶다면 상당히 복잡하게 계산을 해야만 합니다. 아래처럼 말이죠…
1.SELECT trunc (TO_DATE (time2, 'YYYYMMDDHH24MISS')
2.               - TO_DATE (time1, 'YYYYMMDDHH24MISS'), 0) day
3.      , trunc ((TO_DATE (time2, 'YYYYMMDDHH24MISS')
4.                - TO_DATE (time1, 'YYYYMMDDHH24MISS'))*24, 0)
5.        - trunc (TO_DATE (time2, 'YYYYMMDDHH24MISS')
6.                 - TO_DATE (time1, 'YYYYMMDDHH24MISS'), 0) * 24 hour
7.      , round (MOD ((TO_DATE (time2, 'YYYYMMDDHH24MISS')
8.                      - TO_DATE (time1, 'YYYYMMDDHH24MISS'))*24*60, 60)) minute
9.  FROM (SELECT '20111125203138' time1, '20111203123557' time2
10.          FROM DUAL);


1.       DAY       HOUR     MINUTE
2.---------- ---------- ----------
3.         7         16          4


쿼리를 보면 상당히 복잡합니다. 더군다나 만약에 초단위까지 계산하려면 더 복잡해집니다.

04. NUMTODSINTERVAL() 내장함수를 이용하기

이제는 보다 간단한 방법으로 시간차이를 구해보도록 하겠습니다.
1.SELECT NUMTODSINTERVAL (TO_DATE (time2, 'YYYYMMDDHH24MISS')
2. - TO_DATE (time1, 'YYYYMMDDHH24MISS'), 'DAY') diff
3. FROM (SELECT '20111125203138' time1, '20111203123557' time2
4. FROM DUAL);


1.DIFF
2.------------------------------
3.+000000007 16:04:18.999999999


출력의 형태는 조금 다르지만, 결과적으로는 위와 동일한 수치가 나왔습니다. 7일 16시간 4분이죠. 이제는 단지 원하는 형태로 출력형태만 바꿔주면 끝입니다. 위와 동일한 형태로 바꿔보면…
1.SELECT TO_NUMBER (SUBSTR (diff, 2, 9)) day, TO_NUMBER (SUBSTR (diff, 12, 2)) hour
2.      , TO_NUMBER (SUBSTR (diff, 15, 2)) minute, TO_NUMBER (SUBSTR (diff, 18, 2)) second
3.  FROM (SELECT NUMTODSINTERVAL (TO_DATE (time2, 'YYYYMMDDHH24MISS')
4.                                 - TO_DATE (time1, 'YYYYMMDDHH24MISS'), 'DAY') diff
5.           FROM (SELECT '20111125203138' time1, '20111203123557' time2
6.                    FROM DUAL));


1.       DAY       HOUR     MINUTE     SECOND
2.---------- ---------- ---------- ----------
3.         7         16          4         18


이전의 쿼리보다는 보다 간결해졌다는 것을 알 수 있을 것입니다. 위 처럼 substr을 이용해서 강제로 글자를 잘라내도 관계없고, 정규표현을 이용해도 무관합니다.

05. 마치면서…

여전히 포스팅 하나를 하기가 쉽지가 않네요… 혼자서만 알고 있는 것은 어렵지 않은데, 그것을 남에게 전달하는 것은 차원이 다른 일인 것 같습니다. 매번 포스팅할 때마다 느끼는 것이지만… 분야와 질과 양을 막론하고 어찌되었든 자신의 이름으로 책을 쓴다는 것은 정말 너무나도 대단한 업적을 한 것이라고…

시간이 날 때마다 포스팅을 하려고 노력은 하지만… 정말 쉽지가 않네요;; 이만 글을 줄이도록 하겠습니다.

06. 참고문서
1.http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions103.htm

        

    
Copyright 1999-2018 Zeroboard / skin by JY