오라클 데이터베이스를 사용하다 보면 날짜 데이터를 다루는 경우가 빈번합니다. 두 날짜 사이의 기간을 계산하거나, 특정 날짜에 일/월/년을 더하거나 빼는 연산, 심지어 시간대까지 고려해야 하는 상황이 발생하곤 합니다. 본 블로그 글에서는 오라클에서 제공하는 다양한 날짜 계산 방식을 상세한 예제와 함께 설명하여 여러분의 SQL 활용 능력을 한층 업그레이드해 드립니다. 이제 복잡한 날짜 계산도 쉽고 정확하게 처리하세요!
목차
- 1. 오라클 날짜 데이터 타입 이해
- 2. 두 날짜 사이의 기간 계산
- 3. 특정 날짜에 일/월/년 더하고 빼기
- 4. 시간 관련 연산
- 5. 시간대 (Time Zone) 처리
- 6. 날짜 관련 유용한 함수
- 7. 날짜 계산 시 주의사항 및 팁
- 8. 마무리
1. 오라클 날짜 데이터 타입 이해
오라클은 다양한 날짜 및 시간 관련 데이터 타입을 제공합니다. 계산의 정확성을 위해 각 타입의 특징을 이해하는 것이 중요합니다.
DATE: 날짜와 시간 정보를 모두 저장하지만, 초 미만의 정밀도는 없습니다.TIMESTAMP: 날짜와 시간 정보를 초 미만의 정밀도(최대 9자리)까지 저장합니다.TIMESTAMP WITH TIME ZONE:TIMESTAMP에 시간대 정보를 포함하여 저장합니다.TIMESTAMP WITH LOCAL TIME ZONE: 데이터베이스 시간대로 저장되지만, 클라이언트의 시간대에 맞춰 표시됩니다.
오라클은 경우에 따라 암시적으로 데이터 타입을 변환하지만, 명시적인 형변환 (TO_DATE, TO_TIMESTAMP, CAST)을 사용하는 것이 좋습니다.
-- 암시적 형변환 (문자열을 DATE 타입으로 자동 변환 시도)
SELECT * FROM 테이블 WHERE 날짜컬럼 > '20240101';
-- 명시적 형변환 (TO_DATE 함수 사용)
SELECT * FROM 테이블 WHERE 날짜컬럼 > TO_DATE('20240101', 'YYYYMMDD');
-- 명시적 형변환 (TO_TIMESTAMP 함수 사용)
SELECT * FROM 테이블 WHERE 타임스탬프컬럼 > TO_TIMESTAMP('2024-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS');
2. 두 날짜 사이의 기간 계산
- 단순 뺄셈 연산 (
date1 - date2):DATE또는TIMESTAMP타입끼리의 뺄셈은 일 단위의 차이를 숫자로 반환합니다.
SELECT DATE '2024-01-10' - DATE '2024-01-05' AS 기간_일; -- 결과: 5
SELECT TIMESTAMP '2024-01-10 12:00:00' - TIMESTAMP '2024-01-05 10:30:00' AS 기간_일; -- 결과: 5.0625 (5일 + 1.5시간)
MONTHS_BETWEEN함수: 두 날짜 사이의 월 수를 계산합니다.
SELECT MONTHS_BETWEEN(DATE '2024-03-15', DATE '2024-01-01') AS 기간_월; -- 결과: 2.4516129
SELECT MONTHS_BETWEEN(DATE '2024-03-01', DATE '2024-01-01') AS 기간_월; -- 결과: 2
TRUNC함수 활용: 날짜에서 특정 단위 이하를 절삭하여 기간을 정수로 계산하는 데 유용합니다.
SELECT TRUNC(DATE '2024-01-10') - TRUNC(DATE '2024-01-05') AS 기간_일; -- 결과: 5
3. 특정 날짜에 일/월/년 더하고 빼기
- 단순 더하기/빼기 연산 (일 단위):
DATE타입에 숫자를 더하거나 빼면 해당 숫자만큼의 날짜가 이동합니다.
SELECT DATE '2024-01-01' + 7 AS 칠일후; -- 결과: 2024-01-08
SELECT DATE '2024-01-15' - 3 AS 삼일전; -- 결과: 2024-01-12
ADD_MONTHS함수: 특정 날짜에 지정된 월 수를 더하거나 뺍니다.
SELECT ADD_MONTHS(DATE '2024-01-15', 2) AS 두달후; -- 결과: 2024-03-15
SELECT ADD_MONTHS(DATE '2024-03-31', -1) AS 한달전; -- 결과: 2024-02-29 (윤년 고려)
INTERVAL데이터 타입 활용: 년, 월, 일, 시간, 분, 초 단위까지 정밀하게 기간을 정의하고 연산할 수 있습니다.
-- 5일 후
SELECT DATE '2024-01-01' + INTERVAL '5' DAY AS 오일후; -- 결과: 2024-01-06
-- 2개월 후
SELECT DATE '2024-01-15' + INTERVAL '2' MONTH AS 두달후; -- 결과: 2024-03-15
-- 1년 3개월 후
SELECT DATE '2024-05-20' + INTERVAL '1-3' YEAR TO MONTH AS 일년삼개월후; -- 결과: 2025-08-20
-- 2시간 30분 후
SELECT TIMESTAMP '2024-02-10 10:00:00' + INTERVAL '2:30' HOUR TO MINUTE AS 두시간삼십분후; -- 결과: 2024-02-10 12:30:00
4. 시간 관련 연산
DATE타입에도 시간 정보가 포함되어 있으므로, 실수 형태의 값을 더하거나 빼서 시간을 조작할 수 있습니다.
SELECT DATE '2024-01-01 10:00:00' + (1/24) AS 한시간후; -- 결과: 2024-01-01 11:00:00
SELECT DATE '2024-01-01 10:00:00' + (30/(24*60)) AS 삼십분후; -- 결과: 2024-01-01 10:30:00
SYSDATEvsSYSTIMESTAMP:SYSDATE는 데이터베이스 서버의 현재 날짜와 시간을DATE타입으로 반환하고,SYSTIMESTAMP는TIMESTAMP WITH TIME ZONE타입으로 더 정밀한 시간 정보를 제공합니다.TRUNC함수를 이용한 시간 요소 제거:TRUNC함수에 날짜 포맷 마스크를 사용하여 특정 시간 요소를 제거할 수 있습니다.
SELECT TRUNC(SYSDATE) AS 오늘날짜; -- 시간 정보 제거
SELECT TRUNC(SYSDATE, 'HH') AS 현재시간_절삭; -- 분, 초 제거
5. 시간대 (Time Zone) 처리
DBTIMEZONE및SESSIONTIMEZONE확인: 데이터베이스 및 현재 세션의 시간대 정보를 확인할 수 있습니다.
SELECT DBTIMEZONE FROM DUAL;
SELECT SESSIONTIMEZONE FROM DUAL;
FROM_TZ,AT TIME ZONE함수 활용:FROM_TZ(timestamp, timezone):TIMESTAMP값에 시간대 정보를 추가하여TIMESTAMP WITH TIME ZONE타입으로 변환합니다.AT TIME ZONE timezone:TIMESTAMP WITH TIME ZONE값을 지정된 시간대로 변환합니다.
-- 특정 타임존을 갖는 TIMESTAMP 생성
SELECT FROM_TZ(TIMESTAMP '2025-04-09 15:00:00', 'Asia/Seoul') AS 서울시간;
-- 시간대 변환
SELECT FROM_TZ(TIMESTAMP '2025-04-09 15:00:00', 'America/Los_Angeles') AT TIME ZONE 'Asia/Seoul' AS 서울시간_변환;
6. 날짜 관련 유용한 함수
SYSDATE,CURRENT_DATE(세션 시간대 기준),SYSTIMESTAMP,CURRENT_TIMESTAMP(세션 시간대 기준)TRUNC(date, [format]): 날짜를 특정 형식으로 자릅니다. (예: 'YYYY', 'MM', 'DD', 'HH', 'MI', 'SS')ROUND(date, [format]): 날짜를 특정 형식으로 반올림합니다.EXTRACT(YEAR FROM date),EXTRACT(MONTH FROM date),EXTRACT(DAY FROM date),EXTRACT(HOUR FROM timestamp),EXTRACT(MINUTE FROM timestamp),EXTRACT(SECOND FROM timestamp): 날짜 또는 타임스탬프에서 특정 요소를 추출합니다.
7. 날짜 계산 시 주의사항 및 팁
- 데이터 타입 일치: 날짜 계산 시 데이터 타입을 일치시키는 것이 중요합니다.
- 날짜 형식(Format Mask) 이해: 문자열을 날짜 타입으로 변환하거나 날짜 타입을 문자열로 변환할 때 사용되는 형식 마스크를 정확히 이해해야 합니다.
- 성능 최적화: 대량의 날짜 데이터를 처리하는 쿼리에서는 날짜 컬럼에 적절한 인덱스를 생성하여 성능을 향상시킬 수 있습니다.