오라클DBMS로 데이터를 다루다 보면 시간관련해서 연산을 하거나 시간의 차이를 저장하거나 할 필요가 있을때가 있다. 이 경우 사용할 수 있는 데이터 타입이 두가지가 있다. 바로 INTERVAL YEAR TO MONTH(년월로 표현되는 기간) 와 INTERVAL DAY TO SECOND(일시분초의 표현되는 기간) 두가지가 존재한다. 오라클9i부터 사용 가능해진 데이터형이다.
INTERVAL YEAR TO MONTH 타입의 경우 INTERVAL YEAR [(년 정밀도 지정)] TO MONTH 처럼 년도에 정밀도를 지정할 수 있다. 지정할 수 있는 범위는 0-9로 디폴트로 2가 지정된다. 최대 999999999의 년도차를 지정할 수 있는데 이정도의 년도차를 지정할 일을 아마 없을듯하다. 기본치인 2자리로도 왠만한 경우는 다 커버할 수 있지 않을까? 차이를 나타내므로 당연히 음수도 지정할 수 있으며 고정5바이트 타입이다.
INTERVAL DAY TO SECOND 타입은 INTERVAL DAY [(일 정밀도 지정)] TO SECOND [(소수점 이하 초 정밀도 지정)] 처럼 0-9범위의 일 정밀도(디폴트 2) 0-9범위의 소수점 이하의 초 정밀도(디폴트 6)를 지정할 수 있다. 소수점 9자리 까지의 초의 정밀도는 쓸데가 있을까 싶지만 과학적인 계산결과 등에 필요할지도... 역시 차이를 나타내므로 음수지정이 가능하며 고정11바이트 타입니다.
위에서 언급한 두가지의 데이터 타입은 각각 ± 년도-월(±YY[지정한 정밀도 범위]-MM), ±일 시:분:초(±DD[지정한 정밀도 범위] HH:MM:SS.SSSSSS[지정한 정밀도 범위])와 같은 문자열 포맷으로 표현될 수 있으며 단독으로 사용될 경우에는 문자열 그대로도 SQL문에 사용될 수 있다. 하지만 다른 날짜 타입과 연산을 해야할 경우에는 명시적으로 형을 변환 시켜주어야 할 필요가 있다. 이때 아래에서 소개하는 펑션을 이용하여 형변환을 진행한다.
INTERVAL YEAR TO MONTH 형변환 함수
TO_YMINTERVAL ( string ) - 오라클 9i
NUMTOYMINTERVAL ( number , ym_interval_unit ) - 오라클 8i
TO_YMINTERVAL 함수의 경우는 위에서 소개한 포맷의 문자열을 그대로 넘겨주면 INTERVAL YEAR TO MONTH형을 반환 해준다. NUMTOYMINTERVAL함수는 DB의 데이터 타입으로 추가된 9i이전부터 존재하는 것으로 보아 타입 자체는 8i에 추가 되었지만 저장할 수 있는 타입으로는 9i에서부터 추가된 것으로 보인다. TO_YMINTERVAL와는 다르게 인자가 2개 필요한데 첫번째 인자는 말 그대로 숫자이며 두번째 인자로 년/월[YEAR/MONTH]을 지정해 준다. 자세한 것을 아래의 사용예를 보면 이해할 수 있을듯 하다.
쿼리예(TO_YMINTERVAL)
SELECT SYSDATE - TO_YMINTERVAL('+10-11') FROM DUAL;결과
쿼리예(NUMTOYMINTERVAL)
SELECT SYSDATE - NUMTOYMINTERVAL(13, 'MONTH') FROM DUAL;
결과
NUMTOYMINTERVAL함수의 경우는 첫번째 인자에 소수점이하도 지정할 수 있기때문에 다양한 숫자로 지정할 수 있을 듯하나 가독성 측면에서 볼때 날짜 형식으로 읽기 쉬운 인자를 지정할 수 있는 TO_YMINTERVAL함수가 아무래도 사용하기 편리할 듯 하다.
INTERVAL DAY TO SECOND 형변환 함수
TO_DSINTERVAL ( string, nls_param ) - 오라클 9i
NUMTODSINTERVAL ( number , ds_interval_unit ) - 오라클 8i
TO_DSINTERVAL 함수는 TO_YMINTERVAL함수와 마찬가지로 위에서 소개한 포맷의 문자열을 그대로 넘겨주면 INTERVAL DAY TO SECOND형을 반환 해준다. NUMTODSINTERVAL함수도 마찬가지로 NUMTOYMINTERVAL함수와 같이 DB의 데이터 타입으로 추가된 9i이전부터 존재하는 것으로 보아 타입 자체는 8i에 추가 되었지만 저장할 수 있는 타입으로는 9i에서부터 추가된 것으로 보인다.
TO_YMINTERVAL함수의 두번째 인자는 'NLS_NUMERIC_CHARACTERS'와 관련이 있는듯 한데(소수점이나 자릿수 표시문자) 로케일이 한국이라 혹은 영미권 국가로 지정되 있는 경우는 크게 의식할 필요가 없을 듯 하다. NUMTODSINTERVAL함수의 두번째 인자는 일/시/분/초[DAY/HOUR/MINUTE/SECOND]를 지정할 수 있으며 자세한 사용예는 아래를 참조하도록 하자.
쿼리예(TO_YMINTERVAL)
SELECT SYSTIMESTAMP - TO_DSINTERVAL('+01 01:01:01.000000') FROM DUAL;
결과
쿼리예(NUMTODSINTERVAL)
SELECT SYSTIMESTAMP - NUMTODSINTERVAL('100', 'MINUTE') FROM DUAL;
결과
INTERVAL DAY TO SECOND의 경우도 가독성측면에서 TO_YMINTERVAL을 사용하는 편이 유지보수 측면에서 유리할 듯 하다.
리터럴 표기
위에서 소개한 함수를 이용한 방법이외에도 리터럴을 이용해서 직접 타입을 표현하는 방법이 있다. 아래의 예시를 보자.
쿼리예
SELECT SYSTIMESTAMP - INTERVAL '10-11' YEAR TO MONTH FROM DUAL; SELECT SYSTIMESTAMP - INTERVAL '01:01' HOUR TO MINUTE FROM DUAL;
위의 예를 보면 다양한 활용이 가능할 것으로 생각된다 문자열 포맷으로 지정한 후 뒤에 범위를 지정해 주면 INTERVAL타입을 함수를 통하지 않고도 직접 리터럴로 표현할 수 있다.
마치며
이 아티클에서 소개한 내용을 가지고 활용하면 다양한 시간차를 구할 수 있을것으로 생각된다. 또 기회가 된다면 시간에 관련된 다양함 함수들을 소개해 보도록 하겠습니다.
