[Oracle] 날짜(년,월,일), 시간(시,분,초) 더하고 빼기

이번 포스팅은 그냥 날짜와 시간을 Oracle에서 연산하는 관련된걸 가능한 다 적어보려고 합니다.

 

우선적으로 

오라클은 기본적으로 시간에 +1을 할 경우엔 하루를 더하게 됩니다.

관련 실험은 포스팅을 한적이 있습니다. [날짜 마지막 시간 적용에 0.99999 쓰는 이유]

 

그래서  간단하게 시간을 더하기로 진행하면 일, 시간(시,분,초)에 대한 연산을 진행할 수 있습니다.

 

더하기 예시 

WITH ADDTIME AS (
   SELECT TO_DATE('2024-11-28 18:30:10', 'YYYY-MM-DD HH24:MI:SS') TEST_TIME
     FROM DUAL
)

SELECT TEST_TIME                  AS  기준시간
     , TEST_TIME +1               AS  하루더하기
     , TEST_TIME + 1/24           AS  한시간더하기
     , TEST_TIME - 30/(24*60)     AS  삽십분뺴기
     , TEST_TIME - 10/(24*60*60)  AS  십초빼기
  FROM ADDTIME;

 

결과는 다음과 같습니다

 

 

하지만 더하기의 단점은

Month(월), Year(년)은 각 월마다 년마다 날짜가 다른 경우가 있기 때문에 정확한 계산이 불가능하는 점입니다.

 

그래서 이러한 문제점을 보안하기 위해서 월별 계산을 위한 함수가 있다

ADD_MONTHS  (※ ADD_YEARS 는 없다)

-- ADD_MONTHS(날짜, 더하는월)

WITH ADDTIME AS (
     SELECT TO_DATE('2024-11-28 18:30:10', 'YYYY-MM-DD HH24:MI:SS') TEST_TIME
       FROM DUAL
       )
SELECT ADD_MONTHS(TEST_TIME ,'1')  as 한달추가
     , ADD_MONTHS(TEST_TIME ,'-1') as 한달빼기
  FROM ADDTIME;

 

결과는 다음과 같다

 

 

 

해당 함수는 말일을 조정을 해주는데

연산을 한 결과가 그 달을 초과한 날일 경우에 자동으로 말일으로 잡아준다.

 

예시

WITH ADDTIME AS (
     SELECT TO_DATE('2024-03-31') TEST_TIME
       FROM DUAL
    )
SELECT ADD_MONTHS(TEST_TIME ,'1')  as 한달더하기
     , ADD_MONTHS(TEST_TIME ,'-1') as 한달빼기
     , ADD_MONTHS(TEST_TIME ,'11') as 내년2월
  FROM ADDTIME;

 

결과

말일이 자동으로 계산이 되는걸 볼수있는데

 

2월에서 ->3월로 말일을 구하고 싶으나 

28일에서 -> 31일은 되지 않는다

이럴때는  LAST_DAY를 사용하자

그달의 마지막날로 변환 해준다

 

예시

SELECT LAST_DAY(TO_DATE('2024-11-18 18:30:10', 'YYYY-MM-DD HH24:MI:SS') ) as 막날
FROM dual;

 

반대로 첫날로 이렇게 응용이 가능하다

SELECT ADD_MONTHS(LAST_DAY(TO_DATE('2024-11-18 18:30:10', 'YYYY-MM-DD HH24:MI:SS')),-1) +1 as 첫날
 FROM dual;

시간을 버리고 간단하게 그냥 날짜만 구하고자 한다면 TRUNC함수를 이용할 수 있다.

SELECT TRUNC(SYSDATE, 'MM')     as 첫날
    , LAST_DAY(TRUNC(SYSDATE))  as 말일
 FROM dual;

 

 

 

위에 함수들은 다 월에 관련된건데 

년도도 명시적으로 계산하고 월도 시간도 명시적으로 계산가능한 방법이 있다.

 

INTERVAL

 

INTERVAL 은

YEAR TO MONTH 또는 DAY TO SECOND 형식을 사용해야 하는데

잘 지키면 제약이 많이 없다

 

아래는 형식을 지킨 경우의 수를 적어 놓았다.

• INTERVAL 'Y-M' YEAR TO MONTH: 년 월 
  
  • INTERVAL 'N' YEAR :년 
  • INTERVAL 'N' MONTH:월 
• INTERVAL 'D HH:MI:SS' DAY TO SECOND: 일, 시간, 분, 초
  
  • INTERVAL 'N' DAY   : 일
  • INTERVAL 'N' HOUR  : 시간
  • INTERVAL 'N' MINUTE: 분
  • INTERVAL 'N' SECOND: 초
  • INTERVAL 'D HH:MI'  DAY TO MINUTE: 일, 시간, 분
  • INTERVAL 'D HH'     DAY TO HOUR: 일, 시간
  • INTERVAL 'HH:MI:SS' HOUR TO SECOND: 시간, 분, 초
  • INTERVAL 'HH:MI'    HOUR TO MINUTE: 시간, 분
  • INTERVAL 'MI:SS' MINUTE TO SECOND:  분, 초

 

사용 예시

   WITH ADDTIME AS (
       SELECT TO_DATE('2024-11-28 18:30:10', 'YYYY-MM-DD HH24:MI:SS') TEST_TIME
         FROM DUAL
       )
   SELECT TEST_TIME - (interval '1' DAY) as 하루빼기
        , TEST_TIME - (INTERVAL '1 12:01:30' DAY TO SECOND) - (INTERVAL '01 01' DAY TO HOUR) as 두번째
        , TEST_TIME - (INTERVAL '1-1' YEAR TO MONTH) - (INTERVAL '1 1' DAY TO HOUR)          as 세번째
     FROM ADDTIME;

 

하지만  좋아보이는 INTERVAL에도 문제점이 하나있다

제약이 많이 없다했었지 없는건 아니다.

ADD_MONTHS에는 있는 말일이 계산이 안된다

한번 아래를 봐보자

WITH ADDTIME AS (
     SELECT TO_DATE('2024-11-28 18:30:10', 'YYYY-MM-DD HH24:MI:SS') TEST_TIME
       FROM DUAL
     )
 SELECT  TEST_TIME - (interval '9' MONTH) + (interval '2' DAY)  as  첫번째
     ,  TEST_TIME + (interval '2' DAY)   - (interval '9' MONTH)as  두번째
   FROM ADDTIME;

 

파란색 부분에서 지정된 월에 대한 날짜가 부적합합니다.라고 오류 메세지를 보낸다.

주석을 하고 진행하면 잘되는 것을 볼 수 있다.

 

연산의 개념이라 순서에 상관도 있지만

첫번째는 9개월을 먼저 뺴고 (2월28일) 2일을 더하는 거라 (윤년이라 29-> 3/1) 되지만 

두번째를 보면 2일을 먼저 더하고(11월30) 9개월을 뺐다 11월30일에서 2월 30일로 계산이 되었기 때문에

2월 30일은 없는날이므로오류를 보내는 것을 알 수 있다.

 

정말 ADD_MONTHS랑 다르게 말일 계산이 안되는 것을 알 수 있다.

 

사용용도에 맞게 다 잘해야한다.