1. Oracle 기본 날짜 함수
Oracle에서 날짜를 다루는 기본 함수들부터 알아봅시다.
현재 날짜 및 시간 구하기
-- 현재 날짜와 시간 가져오기
SELECT SYSDATE FROM DUAL; -- 19-MAR-25 (기본 형식)
-- 현재 날짜와 시간 (타임존 포함)
SELECT SYSTIMESTAMP FROM DUAL; -- 19-MAR-25 14.30.25.123456 +09:00
-- 현재 날짜만 가져오기 (시간 제외)
SELECT TRUNC(SYSDATE) FROM DUAL; -- 19-MAR-25 (시간 부분은 00:00:00)
활용 팁: SYSDATE는 Oracle에서 가장 자주 사용하는 날짜 함수입니다. MS-SQL의 GETDATE()와 유사하지만, Oracle은 날짜와 시간을 DATE 타입 하나로 처리합니다.
SYSDATE와 SYSTIMESTAMP 차이
-- 일반적인 정밀도의 현재 시간 (초 단위까지)
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM DUAL; -- 19-MAR-2025 14:30:25
-- 고정밀 현재 시간 (마이크로초, 타임존 포함)
SELECT TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH24:MI:SS.FF6 TZH:TZM') FROM DUAL; -- 19-MAR-2025 14:30:25.123456 +09:00
언제 사용할까? 대부분의 경우 SYSDATE로 충분하지만, 매우 정밀한 시간 기록이 필요하거나 타임존을 고려해야 할 때는 SYSTIMESTAMP를 사용하세요.
2. Oracle 날짜 추출 및 변환
날짜 구성 요소 추출하기 (EXTRACT)
-- 연, 월, 일 추출
SELECT
EXTRACT(YEAR FROM SYSDATE) AS "연도", -- 2025
EXTRACT(MONTH FROM SYSDATE) AS "월", -- 3
EXTRACT(DAY FROM SYSDATE) AS "일" -- 19
FROM DUAL;
-- 시간 요소 추출
SELECT
EXTRACT(HOUR FROM SYSTIMESTAMP) AS "시", -- 14
EXTRACT(MINUTE FROM SYSTIMESTAMP) AS "분", -- 30
EXTRACT(SECOND FROM SYSTIMESTAMP) AS "초" -- 25.123456
FROM DUAL;
실무 활용: EXTRACT 함수는 날짜의 특정 부분을 숫자로 추출할 때 사용합니다. 월별 리포트, 연도별 통계 등을 작성할 때 유용합니다.
요일 및 주차 정보 가져오기
-- 요일 정보 가져오기
SELECT
TO_CHAR(SYSDATE, 'D') AS "요일숫자", -- 4 (수요일, 일요일=1)
TO_CHAR(SYSDATE, 'DY') AS "요일약자", -- 수
TO_CHAR(SYSDATE, 'DAY') AS "요일이름" -- 수요일
FROM DUAL;
-- 주차 및 분기 정보
SELECT
TO_CHAR(SYSDATE, 'WW') AS "연중주차", -- 12
TO_CHAR(SYSDATE, 'IW') AS "ISO주차", -- 12
TO_CHAR(SYSDATE, 'Q') AS "분기" -- 1
FROM DUAL;
EXTRACT vs TO_CHAR: EXTRACT는 숫자값만 반환하지만, TO_CHAR는 형식을 지정하여 다양한 형태로 변환할 수 있습니다. 요일 정보는 TO_CHAR를 사용하는 것이 편리합니다.
문자열을 날짜로 변환하기 (TO_DATE)
-- 문자열을 날짜로 변환
SELECT TO_DATE('2025-03-19', 'YYYY-MM-DD') FROM DUAL; -- 19-MAR-25
-- 다양한 형식의 문자열을 날짜로 변환
SELECT
TO_DATE('19/03/2025', 'DD/MM/YYYY') AS "영국식날짜",
TO_DATE('03/19/2025', 'MM/DD/YYYY') AS "미국식날짜",
TO_DATE('2025년 3월 19일', 'YYYY"년" MM"월" DD"일"') AS "한글날짜"
FROM DUAL;
활용 사례: 사용자 입력이나 외부 데이터에서 날짜를 받아올 때, 적절한 형식을 지정하여 변환해야 합니다. 특히 국가별로 날짜 표기법이 다르므로 주의가 필요합니다.
3. Oracle 날짜 연산
날짜 더하기/빼기
-- 날짜에 일수 더하기/빼기
SELECT
SYSDATE AS "오늘",
SYSDATE + 1 AS "내일", -- 20-MAR-25
SYSDATE - 1 AS "어제", -- 18-MAR-25
SYSDATE + 7 AS "일주일후", -- 26-MAR-25
SYSDATE - 7 AS "일주일전" -- 12-MAR-25
FROM DUAL;
-- 개월 수 더하기/빼기 (ADD_MONTHS)
SELECT
ADD_MONTHS(SYSDATE, 1) AS "한달후", -- 19-APR-25
ADD_MONTHS(SYSDATE, -1) AS "한달전", -- 19-FEB-25
ADD_MONTHS(SYSDATE, 12) AS "일년후" -- 19-MAR-26
FROM DUAL;
실무 활용: 납기일, 결제 예정일, 멤버십 만료일 등을 계산할 때 자주 사용합니다. Oracle에서는 날짜에 직접 숫자를 더하면 일수가 더해집니다.
날짜 간 차이 계산
-- 두 날짜 간의 일수 차이
SELECT
TO_DATE('2025-12-31', 'YYYY-MM-DD') - SYSDATE AS "남은일수" -- 287 (예시)
FROM DUAL;
-- MONTHS_BETWEEN: 두 날짜 간의 월수 차이
SELECT
MONTHS_BETWEEN(
TO_DATE('2025-12-31', 'YYYY-MM-DD'),
SYSDATE
) AS "남은개월수" -- 9.4 (소수점 포함)
FROM DUAL;
주의사항: MONTHS_BETWEEN은 소수점을 반환합니다. 정확한 개월 수가 필요하면 FLOOR나 ROUND 함수와 함께 사용해야 합니다.
나이 계산하기
-- 생년월일로 정확한 나이 계산
SELECT
FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE('1990-05-15', 'YYYY-MM-DD')) / 12) AS "만나이" -- 34
FROM DUAL;
-- 정확한 만 나이 계산 (생일 지남 여부 고려)
SELECT
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE('1990-05-15', 'YYYY-MM-DD')) -
CASE
WHEN TO_CHAR(SYSDATE, 'MMDD') < TO_CHAR(TO_DATE('1990-05-15', 'YYYY-MM-DD'), 'MMDD')
THEN 1
ELSE 0
END AS "정확한만나이" -- 생일 지남 여부에 따라 34 또는 33
FROM DUAL;
한국식 나이: 한국식 나이(세는 나이)는 위 계산결과에 1을 더하면 됩니다.
4. Oracle 날짜 형식 지정
TO_CHAR로 날짜 형식 지정
-- 다양한 날짜 형식
SELECT
TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS "ISO형식", -- 2025-03-19
TO_CHAR(SYSDATE, 'DD/MM/YYYY') AS "영국식", -- 19/03/2025
TO_CHAR(SYSDATE, 'MM/DD/YYYY') AS "미국식", -- 03/19/2025
TO_CHAR(SYSDATE, 'YYYY년 MM월 DD일') AS "한글식", -- 2025년 03월 19일
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS "ISO날짜시간" -- 2025-03-19 14:30:25
FROM DUAL;
활용 사례: 리포트, 화면 출력, 데이터 내보내기 등에서 원하는 형식으로 날짜를 표시할 때 사용됩니다.
자주 사용되는 날짜 형식 코드
-- 날짜 형식 코드 예제
SELECT
TO_CHAR(SYSDATE, 'YYYY') AS "4자리연도", -- 2025
TO_CHAR(SYSDATE, 'YY') AS "2자리연도", -- 25
TO_CHAR(SYSDATE, 'MM') AS "월숫자", -- 03
TO_CHAR(SYSDATE, 'MON') AS "월약자", -- 3월
TO_CHAR(SYSDATE, 'MONTH') AS "월이름", -- 3월
TO_CHAR(SYSDATE, 'DD') AS "일", -- 19
TO_CHAR(SYSDATE, 'DY') AS "요일약자", -- 수
TO_CHAR(SYSDATE, 'DAY') AS "요일이름" -- 수요일
FROM DUAL;
-- 시간 형식 코드 예제
SELECT
TO_CHAR(SYSDATE, 'HH24') AS "24시간", -- 14
TO_CHAR(SYSDATE, 'HH') AS "12시간", -- 02
TO_CHAR(SYSDATE, 'MI') AS "분", -- 30
TO_CHAR(SYSDATE, 'SS') AS "초", -- 25
TO_CHAR(SYSDATE, 'AM') AS "오전/오후", -- 오후
TO_CHAR(SYSDATE, 'HH:MI:SS AM') AS "시간" -- 02:30:25 오후
FROM DUAL;
알아두세요: Oracle의 날짜 형식은 대소문자를 구분합니다. ‘MM’은 월을, ‘mm’은 분을 의미합니다.
5. Oracle 날짜 범위 다루기
월의 첫날/마지막날 구하기
-- 해당 월의 첫날
SELECT TRUNC(SYSDATE, 'MM') AS "월의첫날" FROM DUAL; -- 01-MAR-25
-- 해당 월의 마지막날
SELECT LAST_DAY(SYSDATE) AS "월의마지막날" FROM DUAL; -- 31-MAR-25
-- 다음 달의 첫날
SELECT TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') AS "다음달첫날" FROM DUAL; -- 01-APR-25
-- 이전 달의 마지막날
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) AS "이전달마지막날" FROM DUAL; -- 28-FEB-25 또는 29-FEB-25(윤년)
실무 활용: 월별 보고서 기간 설정, 월 단위 결제 주기 계산 등에 활용될 수 있습니다.
연도 및 분기의 시작/끝 구하기
-- 해당 연도의 첫날/마지막날
SELECT
TRUNC(SYSDATE, 'YYYY') AS "연도첫날", -- 01-JAN-25
ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - 1 AS "연도마지막날" -- 31-DEC-25
FROM DUAL;
-- 해당 분기의 첫날
SELECT TRUNC(SYSDATE, 'Q') AS "분기첫날" FROM DUAL; -- 01-JAN-25
-- 해당 분기의 마지막날
SELECT
ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 3) - 1 AS "분기마지막날" -- 31-MAR-25
FROM DUAL;
분기별 계산: 회계 보고서나 분기별 목표 달성 보고서 작성 시 유용합니다.
일주일의 시작/끝 구하기
-- 현재 주의 일요일(한 주의 시작)
SELECT TRUNC(SYSDATE, 'IW') - 1 AS "이번주일요일" FROM DUAL; -- 16-MAR-25 (일요일)
-- 현재 주의 토요일(한 주의 끝)
SELECT TRUNC(SYSDATE, 'IW') + 5 AS "이번주토요일" FROM DUAL; -- 22-MAR-25 (토요일)
-- 현재 주의 월요일(업무 주의 시작)
SELECT TRUNC(SYSDATE, 'IW') AS "이번주월요일" FROM DUAL; -- 17-MAR-25 (월요일)
주의: Oracle의 기본 설정은 일요일(1)이 한 주의 첫 날이지만, ‘IW’ 형식은 ISO 표준으로 월요일부터 시작합니다.
6. Oracle 실무 사례: 자주 사용되는 쿼리
오늘/어제/이번주/이번달 데이터 조회
-- 오늘 데이터 조회
SELECT * FROM orders
WHERE TRUNC(order_date) = TRUNC(SYSDATE);
-- 어제 데이터 조회
SELECT * FROM orders
WHERE TRUNC(order_date) = TRUNC(SYSDATE) - 1;
-- 이번 달 데이터 조회
SELECT * FROM orders
WHERE TRUNC(order_date, 'MM') = TRUNC(SYSDATE, 'MM');
-- 지난 7일간 데이터 조회
SELECT * FROM orders
WHERE order_date >= SYSDATE - 7;
팁: TRUNC 함수를 사용하여 시간 부분을 제거하면 날짜 비교가 더 정확해집니다.
일별 트렌드 분석 (간결한 버전)
-- 최근 7일간 일별 주문 집계
SELECT
TRUNC(order_date) AS order_day,
COUNT(*) AS order_count,
SUM(total_amount) AS daily_sales
FROM
orders
WHERE
order_date >= SYSDATE - 7
GROUP BY
TRUNC(order_date)
ORDER BY
order_day;
팁: 이 쿼리로 요일별 주문 패턴을 파악할 수 있습니다. 주말에 주문이 늘어나는지, 특정 요일에 매출이 집중되는지 확인 가능합니다.
월별 매출 비교 (작년 vs 올해)
-- 월별 매출 비교 (작년 vs 올해)
SELECT
TO_CHAR(order_date, 'MM') AS order_month,
SUM(CASE WHEN TO_CHAR(order_date, 'YYYY') = TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYY')
THEN total_amount ELSE 0 END) AS last_year_sales,
SUM(CASE WHEN TO_CHAR(order_date, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY')
THEN total_amount ELSE 0 END) AS this_year_sales
FROM
orders
WHERE
order_date >= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -12)
GROUP BY
TO_CHAR(order_date, 'MM')
ORDER BY
order_month;
성장률 분석: 이 쿼리로 작년 대비 올해 매출 성장을 쉽게 비교할 수 있습니다.
7. Oracle 시간 관련 함수
Oracle에서 시간만 처리하는 방법을 알아봅시다. Oracle은 별도의 TIME 데이터 타입이 없고 DATE 타입이 날짜와 시간을 모두 저장합니다.
시간 부분만 추출하기
-- 현재 시간 부분만 추출
SELECT
TO_CHAR(SYSDATE, 'HH24:MI:SS') AS "현재시간" -- 14:30:25
FROM DUAL;
-- 날짜는 제외하고 시간 부분만 비교
SELECT * FROM orders
WHERE TO_CHAR(order_time, 'HH24:MI:SS') BETWEEN '09:00:00' AND '17:00:00';
활용 팁: Oracle에서는 별도의 TIME 타입이 없으므로 TO_CHAR를 사용하여 시간 부분만 처리합니다.
시간 계산
-- 시간 더하기/빼기 (시)
SELECT
SYSDATE AS "현재",
SYSDATE + (1/24) AS "1시간후", -- 날짜에 1/24를 더하면 1시간 추가
SYSDATE - (1/24) AS "1시간전" -- 날짜에 1/24를 빼면 1시간 전
FROM DUAL;
-- 시간 더하기/빼기 (분, 초)
SELECT
SYSDATE AS "현재",
SYSDATE + (30/1440) AS "30분후", -- 1440 = 24시간 * 60분
SYSDATE + (15/86400) AS "15초후" -- 86400 = 24시간 * 60분 * 60초
FROM DUAL;
업무 활용: 근무 시간 계산, 작업 소요 시간 측정 등에 활용할 수 있습니다.
시간 간격 계산
-- 두 시간 사이의 차이 (시간 단위)
SELECT
(TO_DATE('17:30:00', 'HH24:MI:SS') - TO_DATE('09:00:00', 'HH24:MI:SS')) * 24 AS "근무시간(시)" -- 8.5
FROM DUAL;
-- 두 시간 사이의 차이 (분 단위)
SELECT
(TO_DATE('17:30:00', 'HH24:MI:SS') - TO_DATE('09:00:00', 'HH24:MI:SS')) * 24 * 60 AS "근무시간(분)" -- 510
FROM DUAL;
참고: Oracle에서는 날짜 간의 차이가 일수로 반환되므로, 시간으로 변환하려면 24를, 분으로 변환하려면 24*60을 곱해야 합니다.
실무 사례: 시간 활용
-- 시간대별 주문 분석
SELECT
TO_CHAR(order_time, 'HH24') AS hour_of_day,
TO_CHAR(TO_DATE(TO_CHAR(order_time, 'HH24'), 'HH24'), 'HH24:MI') || '-' ||
TO_CHAR(TO_DATE(TO_CHAR(order_time, 'HH24'), 'HH24') + 1/24, 'HH24:MI') AS time_slot,
COUNT(*) AS order_count
FROM
orders
WHERE
order_date = TRUNC(SYSDATE) -- 오늘 주문만
GROUP BY
TO_CHAR(order_time, 'HH24')
ORDER BY
hour_of_day;
팁: 이 쿼리로 하루 중 주문이 가장 많은 시간대를 파악할 수 있습니다.
8. Oracle 날짜 관련 문제 해결
날짜 비교 주의사항
-- 시간 부분 포함된 날짜 비교 시 주의
SELECT
CASE
WHEN TO_DATE('2025-03-19 15:30:00', 'YYYY-MM-DD HH24:MI:SS') =
TO_DATE('2025-03-19', 'YYYY-MM-DD')
THEN '일치'
ELSE '불일치'
END AS result
FROM DUAL; -- '불일치' 반환 (시간 부분이 다름)
-- 올바른 방식 (날짜 부분만 비교)
SELECT
CASE
WHEN TRUNC(TO_DATE('2025-03-19 15:30:00', 'YYYY-MM-DD HH24:MI:SS')) =
TO_DATE('2025-03-19', 'YYYY-MM-DD')
THEN '일치'
ELSE '불일치'
END AS result
FROM DUAL; -- '일치' 반환
흔한 실수: 시간이 포함된 DATE와 날짜만 있는 값을 비교할 때 발생하는 가장 흔한 오류입니다. TRUNC 함수를 사용하여 시간 부분을 제거한 후 비교 하는 습관을 들일 필요가 있습니다.
월말 처리 문제
-- 월말 날짜 이슈
SELECT
TO_CHAR(TO_DATE('2025-01-31', 'YYYY-MM-DD'), 'DD-MON-YYYY') AS "1월말",
TO_CHAR(ADD_MONTHS(TO_DATE('2025-01-31', 'YYYY-MM-DD'), 1), 'DD-MON-YYYY') AS "1개월후"
FROM DUAL; -- 1개월 후: 28-FEB-2025 (2월은 28일까지)
-- LAST_DAY 함수 활용
SELECT
TO_CHAR(LAST_DAY(TO_DATE('2025-01-31', 'YYYY-MM-DD')), 'DD-MON-YYYY') AS "1월마지막날",
TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE('2025-01-31', 'YYYY-MM-DD'), 1)), 'DD-MON-YYYY') AS "2월마지막날"
FROM DUAL; -- 2월 마지막날: 28-FEB-2025
날짜 계산 주의: 월마다 일수가 다르기 때문에 월 단위 계산 시 예상과 다른 결과가 나올 수 있습니다. 특히 월말 결제일 계산 시 LAST_DAY 함수를 활용할 수 있습니다.
윤년 처리
-- 윤년 여부 확인
CREATE OR REPLACE FUNCTION is_leap_year(p_year IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
RETURN CASE
WHEN MOD(p_year, 400) = 0 OR (MOD(p_year, 4) = 0 AND MOD(p_year, 100) <> 0)
THEN 'Y'
ELSE 'N'
END;
END;
/
-- 사용 예
SELECT
is_leap_year(2024) AS "2024년", -- Y (윤년)
is_leap_year(2025) AS "2025년" -- N (평년)
FROM DUAL;
-- 2월 마지막 날 확인으로 윤년 확인
SELECT
TO_CHAR(LAST_DAY(TO_DATE('2024-02-01', 'YYYY-MM-DD')), 'DD') AS "2024년2월마지막날", -- 29
TO_CHAR(LAST_DAY(TO_DATE('2025-02-01', 'YYYY-MM-DD')), 'DD') AS "2025년2월마지막날" -- 28
FROM DUAL;
특정 날짜 유효성 검사: 사용자 입력 날짜의 유효성을 검사할 때 윤년 여부를 확인해야 합니다. LAST_DAY 함수를 사용하면 쉽게 확인할 수 있습니다.
이 글에서는 Oracle 데이터베이스의 날짜 및 시간 함수에 대해 알아보았습니다. Oracle의 날짜 처리는 MS-SQL과는 차이가 있지만, 기본 개념을 이해하고 자주 사용하는 패턴을 익히면 쉽게 활용할 수 있습니다. 특히 TO_CHAR, TO_DATE, TRUNC, ADD_MONTHS, LAST_DAY 함수는 Oracle에서 날짜 작업 시 가장 많이 사용되는 함수들이니 반드시 익혀 둘 필요가 있겠지요. 더 자세한 내용이나 특정 날짜 문제에 대한 질문이 있으시면 댓글로 남겨주시면 답변 드릴게요! ~~