Oracle :: 날짜 연산 (ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ROUND, TRUNC)

- 날짜 연산의 기본 단위는 DAY 이다!!

 

■ 시간 단위 연산

--※ 날짜 연산의 기본 단위는 일수(DAY)이다~!!
SELECT SYSDATE "COL1"       -- 2023-10-22 21:47:38
     , SYSDATE +1 "COL2"    -- 2023-10-23 21:47:38
     , SYSDATE +2 "COL2"    -- 2023-10-24 21:47:38
     , SYSDATE +30 "COL3"   -- 2023-11-21 21:47:38
FROM DUAL;

--○ 시간 단위 연산
SELECT SYSDATE "COL1"           -- 2023-10-22 21:49:20
     , SYSDATE + 1/24 "COL2"    -- 2023-10-22 22:49:20
     , SYSDATE + 2/24 "COL3"    -- 2023-10-22 23:49:20
FROM DUAL;

--○ 현재 시간과 현재 시간 기준 1일 2시간 3분 4초 후를 조회한다.
SELECT SYSDATE "현재시간"
     , SYSDATE + 1 + 2/(24) + 3/(24*60) + 4/(24*60*60) "연산 후 시간"
FROM DUAL;

/*
--------------------- ----------------------
현재 시간              연산 후 시간
--------------------- ----------------------
2023-10-22 21:51:17    2023-10-23 23:54:21
--------------------- ----------------------
*/


-- 방법 1.
SELECT SYSDATE "현재 시간"
     , SYSDATE + 1 + (2/24) + (3/(24*60)) + (4/(24*60*60)) "연산 후 시간"
FROM DUAL;
--==>> 2023-10-22 21:54:14	
--     2023-10-23 23:57:18

-- 방법 2.
SELECT SYSDATE "현재 시간"
     , SYSDATE + ((1*24*60*60) + (2*60*60) + (3*60) + 4)  / (24*60*60)"연산 후 시간"
FROM DUAL;
--==>> 2023-10-22 21:54:24	
--     2023-10-23 23:57:28

--○ 날짜 - 날짜 → 일수
SELECT TO_DATE('2024-03-19', 'YYYY-MM-DD') - TO_DATE('2023-08-22', 'YYYY-MM-DD') "COL1"
FROM DUAL;
--==>> 210

--○ 데이터 타입의 변환
SELECT TO_DATE('2023-10-20', 'YYYY-MM-DD') "COL1"
FROM DUAL;
--==>> 2023-10-20 00:00:00

SELECT TO_DATE('2023-10-32', 'YYYY-MM-DD') "COL2"
FROM DUAL;
--==>> 에러 발생
--     (ORA-01847: day of month must be between 1 and last day of month)

SELECT TO_DATE('2023-02-29', 'YYYY-MM-DD') "COL3"
FROM DUAL;
--==>> 에러 발생
--     (ORA-01839: date not valid for month specified)

SELECT TO_DATE('2023-13-20', 'YYYY-MM-DD') "COL4"
FROM DUAL;
--==>> 에러 발생
--     (ORA-01843: not a valid month)

--※ TO_DATE() 함수를 통해 문자 타입을 날짜 타입으로 변환을 수행하는 과정에서
--   내부적으로 해당 날짜에 대한 유효성 검사가 이루어진다.

 

■ ADD_MONTHS()

- 개월 수를 더하거나 빼주는 함수

--○ ADD_MONTHS() 개월 수를 더하거나 빼주는 함수
SELECT SYSDATE "COL1"
     , ADD_MONTHS(SYSDATE, 2) "COL2"
     , ADD_MONTHS(SYSDATE, 3) "COL3"
     , ADD_MONTHS(SYSDATE, -2) "COL4"
     , ADD_MONTHS(SYSDATE, -3) "COL5"
FROM DUAL;
--==>> 2023-10-20 16:19:11  → 현재
--     2023-12-20 16:19:11  → 2개월 후
--     2024-01-20 16:19:11  → 3개월 후
--     2023-08-20 16:19:11  → 2개월 전
--     2023-07-20 16:19:11  → 3개월 전

 

■ MONTHS_BETWEEN()

- 첫 번째 인자값에서 두 번째 인자값을 뺀 개월 수 반환

--> 첫 번째 인자값에서 두 번째 인자값을 뺀 개월 수를 반환한다.
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2002-05-31', 'YYYY-MM-DD')) "COL1"
FROM DUAL;
--==>> 256.739175627240143369175627240143369176

--> 개월 수의 차이를 반환하는 함수
--  결과값의 부호가 『-』 (음수)로 반환되었을 경우에는
--  첫 번째 인자값에 해당하는 날짜보다
--  두 번째 인자값에 해당하는 날짜가 『미래』 라는 의미로 확인할 수 있다.

 

■ NEXT_DAY()

- 첫 번째 파라미터를 기준으로 두 번째 파라미터인 요일이 오는 일자를 반환

--○ NEXT_DAY()
SELECT NEXT_DAY(SYSDATE, '토') "COL1"
     , NEXT_DAY(SYSDATE, '월') "COL2"
FROM DUAL;
--==>> 2023-10-21 16:25:07   
--     2023-10-23 16:25:07

 

■ LAST_DAY()

- 해당 날짜가 포함되어 있는 그 달의 마지막 날 반환

--○ LAST_DAY()
--> 해당 날짜가 포함되어 있는 그 달의 마지막 날을 반환한다.
SELECT SYSDATE "COL1"                                           -- 2023-10-20
     , LAST_DAY(SYSDATE) "COL2"                                 -- 2023-10-31
     , LAST_DAY(TO_DATE('2023-02-12', 'YYYY-MM-DD')) "COL3"     -- 2023-02-28
     , LAST_DAY(TO_DATE('2020-02-12', 'YYYY-MM-DD')) "COL4"     -- 2020-02-29
FROM  DUAL;

 

■ 날짜 연산 실습 1

--○ 오늘부로 정환이가 다시 입대를 한다.
--   복무 기간은 22개월로 한다.

-- 1. 전역 일자를 구한다.
SELECT ADD_MONTHS(SYSDATE, 22) "전역 일자"
FROM DUAL;
--==>> 2025-08-20

-- 2. 하루 꼬박꼬박 3끼를 식사를 해야 한다고 가정하면
--    정환이가 몇 끼를 먹어야 집에 보내줄까...

--    복무기간 * 3
--    -------
--   (전역일자 - 현재일자)
--   (전역일자 - 현재일자) * 3

SELECT (ADD_MONTHS(SYSDATE, 22) - SYSDATE)*3
FROM DUAL;
--==>> 2010

SELECT (TO_DATE((ADD_MONTHS(SYSDATE, 22)), 'YYYY-MM-DD') - TO_DATE(SYSDATE)) * 3
FROM DUAL;
--==>> 2010

 

■ 날짜 연산 실습 2

▼ 내가 한 풀이

SELECT USER
FROM DUAL;

--※ 날짜 관련 세션 설정 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.


--○ 현재 날짜 및 시간으로부터...
--   수료일(2024-03-19 18:00:00) 까지
--   남은 기간을 다음과 같은 형태로 조회할 수 있도록 쿼리문을 구성한다.
/*
-------------------- --------------------- ------- ------ ------ ---------
현재 시간              수료일                 일     시간    분    초
-------------------- --------------------- ------- ------ ----- -----------
2023-10-20 17:09:10   2024-03-19 18:00:00   140       0     49   50
-------------------- --------------------- ------- ------ ----- -----------
*/

-- 내가 푼 풀이 : 현재 시간에 수료일과의 차이를 일 → 시간 → 분 순으로 차례로 더한 후 비교
SELECT SYSDATE "현재 시간"
     , TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') "수료일"
     , TRUNC(TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) "일"
     , TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - (SYSDATE + TRUNC(TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE))) *24) "시간"
     , TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - ((SYSDATE + TRUNC(TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE)) 
       + TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - (SYSDATE + TRUNC(TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE))) *24)/24)) * (24*60)) "분"
     , TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') 
       - ((SYSDATE + TRUNC(TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE)) 
       + (TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - (SYSDATE + TRUNC(TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24: MI:SS') - SYSDATE))) *24)/24) 
       + (TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - ((SYSDATE + TRUNC(TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE)) 
       + TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - (SYSDATE + TRUNC(TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE))) *24)/24)) * (24*60))/(24*60)))) * (24*60*60))"초"   
FROM DUAL;
--==>> 2023-10-23 23:03:35	2024-03-19 18:00:00	147	18	56	25

 

▼ 같이 한 풀이

· 우선, 문제를 풀기 전 기초 잡기

-- 풀기 위해 기초 다지기
-- 『1일 2시간 3분 4초』를 『초』로 환산하면
SELECT (1*24*60*60) + (2*60*60) + (3*60) + 4
FROM DUAL;
--==>> 93784

-- 『93784초』를 다시 『일, 시간, 분, 초』로 환산하면
SELECT
TRUNC(TRUNC(TRUNC(93784/60)/60)/24) "일"
,MOD(TRUNC(TRUNC(93784/60)/60), 24) "시간"
,MOD(TRUNC(93784/60), 60) "분"
,MOD(93784, 60) "초"
FROM DUAL;
--==>> 1	2	3	4

· 위 내용 적용해서 문제 풀어보자!

--※ 날짜 관련 세션 설정 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.


--○ 현재 날짜 및 시간으로부터...
--   수료일(2024-03-19 18:00:00) 까지
--   남은 기간을 다음과 같은 형태로 조회할 수 있도록 쿼리문을 구성한다.
/*
-------------------- --------------------- ------- ------ ------ ---------
현재 시간              수료일                 일     시간    분    초
-------------------- --------------------- ------- ------ ----- -----------
2023-10-20 17:09:10   2024-03-19 18:00:00   140       0     49   50
-------------------- --------------------- ------- ------ ----- -----------
*/
-- 같이 한 풀이 : 초를 기준으로 연산을 해보자!

-- 수료일까지 남은 기간을 초로 확인하기
SELECT 남은일수 * (24*60*60)
FROM DUAL;

SELECT (수료일자-현재일자) * (24*60*60)
FROM DUAL;

SELECT (TO_DATE('2024-03-19 18:00:00' ,'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)
FROM DUAL;
--==>> 12765266.99999999999999999999999999999997

SELECT
SYSDATE "현재 시간"
,TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') "수료일"
,TRUNC(TRUNC(TRUNC((TO_DATE('2024-03-19 18:00:00' ,'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)/60)/60)/24) "일"
,MOD(TRUNC(TRUNC((TO_DATE('2024-03-19 18:00:00' ,'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)/60)/60), 24) "시간"
,MOD(TRUNC((TO_DATE('2024-03-19 18:00:00' ,'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)/60), 60) "분"
,TRUNC(MOD((TO_DATE('2024-03-19 18:00:00' ,'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60), 60)) "초"
FROM DUAL;
--==>>
/*
-------------------- --------------------- ------- ------ ------ --------
현재 시간              수료일                 일     시간    분    초
-------------------- --------------------- ------- ------ ----- ---------
2023-10-24 00:07:58   2024-03-19 18:00:00   147       17    52    2
-------------------- --------------------- ------- ------ ----- ---------
*/


--○ 각자 태어난 날짜 및 시각으로부터 현재까지 얼마만큼의 시간을 살고 있는지
--   다음과 같은 형태로 조회할 수 있도록 쿼리문을 구성한다.
/*
-------------------- --------------------- ------- ------ ------ ---------
현재 시간              생년월일                일     시간    분    초
-------------------- --------------------- ------- ------ ----- -----------
2023-10-24 00:09:19   1997-08-11 16:30:00   148       8     16    21
-------------------- --------------------- ------- ------ ----- -----------
*/


-- 내가 살아 온 일을 초로 환산해보자!
SELECT (SYSDATE - TO_DATE('1997-08-11 16:30:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)
FROM DUAL;
--==>> 826789244

-- 구한 초를 가지고 일, 시간, 분, 초 형태로 만들기!
SELECT
TRUNC(TRUNC(TRUNC((SYSDATE - TO_DATE('1997-08-11 16:30:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)/60)/60)/24) "일"
,MOD(TRUNC(TRUNC((SYSDATE - TO_DATE('1997-08-11 16:30:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)/60)/60), 24) "시간"
,MOD(TRUNC((SYSDATE - TO_DATE('1997-08-11 16:30:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)/60), 60) "분"
,MOD((SYSDATE - TO_DATE('1997-08-11 16:30:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60), 60) "초"
FROM DUAL;
--==>>
/*
-------------------- --------------------- ------- ------ ------ --------
현재 시간              생년월일                일     시간    분    초
-------------------- --------------------- ------- ------ ----- ---------
2023-10-24 00:09:19   1997-08-11 16:30:00   9569      7     41    28
-------------------- --------------------- ------- ------ ----- ---------
*/

 

■ ROUND 

- 날짜 반올림

--○ 날짜 반올림
SELECT SYSDATE "COL1"                    -- 2023-10-24   → 기본 현재 날짜
     , ROUND(SYSDATE, 'YEAR') "COL2"     -- 2024-01-01   → 연도까지 유효한 데이터 (상반기/하반기 기준으로 반올림 함!)
     , ROUND(SYSDATE, 'MONTH') "COL3"    -- 2023-11-01   → 월까지 유효한 데이터 (15일 기준)
     , ROUND(SYSDATE, 'DD') "COL4"       -- 2023-10-24   → 일까지 유효한 데이터 (정오 기준)
     , ROUND(SYSDATE, 'DAY') "COL5"      -- 2023-10-22   → 일까지 유효한 데이터 (수요일 정오 기준) 수요일 정오가 넘지 않으면 전 주에 해당하는 일요일 반환
FROM DUAL;

 

■ TRUNC

- 날짜 절삭

--○ 날짜 절삭
SELECT SYSDATE "COL1"                   -- 2023-10-24   → 기본 현재 날짜
     , TRUNC(SYSDATE, 'YEAR') "COL2"    -- 2023-01-01   → 연도까지 유효한 데이터
     , TRUNC(SYSDATE, 'MONTH') "COL3"   -- 2023-10-01   → 월까지 유효한 데이터
     , TRUNC(SYSDATE, 'DD') "COL4"      -- 2023-10-24   → 일까지 유효한 데이터
     , TRUNC(SYSDATE, 'DAY') "COL5"     -- 2023-10-22   → 그 전 주에 해당하는 일요일
FROM DUAL;