DevDatabase

Oracle - Date

⏱ 22 min.

오라클 DBMS에선 날짜함수를 사용할 수 있다. 현재 날짜(OS 기준 날짜 출력)를 출력하고 싶다면, 아래의 명령어로 조회할 수 있다. dual 테이블에 대한 설명은 이 글을 참고바란다.

1
2
SELECT sysdate
FROM dual;

목록


날짜 연산

날짜 데이터는 연산도 가능하다. 현재 날짜에서 1,000일 뒤의 날짜를 조회해보았다.

1
2
SELECT sysdate, sysdate + 1000 as "After 1000 days"
FROM dual;

이 때 연산되는 숫자는 일(day)를 기준으로 연산처리된다.

날짜와 날짜 역시 연산할 수 있다.

1
2
SELECT hiredate, sysdate-hiredate as "long service"
FROM emp;

그러나 연산 결과가 이상하게 출력된다. 날짜인데 왜 소숫점이 찍히는걸까. 알고보니 정수 영역은 day를 의미하며, 소숫점은 시간을 의미한다고 한다. 따라서 시간은 제외하고 순수 근속일만 계산하고자 한다면 숫자함수중 버림역할을 수행하는 trunc() 를 사용하면 된다.

1
2
SELECT hiredate, trunc(sysdate-hiredate) as "long service"
FROM emp;

이를 응용해서 현재의 날짜를 기준으로 내가 얼마나 산지를 조회해보았다.

1
2
3
SELECT '1991/03/23' AS "내 생일",
      trunc(sysdate - to_date('19910323', 'YYYYMMDD')) || '일' AS "내가 산 날"
FROM dual;

날짜간 월(months)수 조회

날짜와 날짜를 비교하여 몇개월의 차이가 발생하는지 조회할때 사용하는 날짜 함수이다.

1
2
SELECT months_between(날짜1, 날짜2)
FROM dual;

파라미터로 2개의 날짜를 입력받으며, 날짜1-날짜2의 결과를 월(months)로 조회한다. 아래 퀴즈에 대한 쿼리를 보면서 확인하자.

1
emp 테이블에서 입사일(hiredate)로부터 현재(sysdate)까지 몇개월 근무했는지 조회하시오.
1
2
3
SELECT empno, ename, trunc(months_between(sysdate, hiredate)) as "근속 월 수"
FROM emp
ORDER BY months_between(sysdate, hiredate);

월(months) 연산

1
2
SELECT add_months(날짜, 연산하고자하는 월(months))
FROM dual;

기존에 날짜에 연산을 할 경우 기본으로 일(days)이 연산이되는데, add_months()는 월(months)을 기준으로 연산하도록 하는 날짜함수이다.

emp 테이블에서 hiredate에 36개월을 더했다. 연산이 잘 된다면, 연도만 3년 추가될 것이다.

1
2
SELECT ename, hiredate, add_months(hiredate, 36) as "3년후"
FROM emp;

날짜를 문자열로 변환하기

dual 테이블에서 sysdate를 조회하면, 년/월/일 포맷으로만 조회가 된다. 그러나 변환함수를 사용하면 sysdate안에 포함된 시/분/초까지 출력할 수 있다. 날짜와 관련한 변환함수가 2개인데, 먼저 소개할 변환함수는 to_char()이다.

to_char() 의 형식은 다음과 같다.

1
SELECT to_char(날짜, 형식) FROM dual;

위의 형식대로 작성하면, 형식에 맞춰 날짜가 조회된다. sysdate를 원하는 형식대로 조회해보겠다.

1
2
SELECT to_char(sysdate, 'YYYY:MM:DD:(MON, DY):HH:MI:SS') as "Today"
FROM dual;

맥OS 시간과는 차이가 있어보인다. 왜 이런 차이가 발생하는지는 모르겠다…

date 포맷에 대해 알아보자.

symbol description
YYYY 연도
MM 월
DD 일
MON Month
DY 축약된 요일
DAY 완전한 요일
HH hour
HH24 24시를 기준으로한 hour
PM/AM PM 또는 AM을 쿼리에 작성하면, AM/PM이 계산되어 조회
MI minute
SS second

문자를 날짜로 변환

to_char() 함수가 날짜 데이터를 문자열로 변환하는 변환함수였다면, to_date() 함수는 문자열 데이터를 날짜 데이터로 변환하는 변환함수이다. 

1
SELECT to_date(문자열, 날짜형식) FROM dual;

첫번째 파라미터로 문자열 데이터를 받아서 두번째 파라미터로 들어오는 날짜형식에 맞춰서 문자열 데이터를 날짜로 변환 조회한다. 아래 예제 쿼리를 확인해보자.

1
2
SELECT to_date('2020/04/02 11:45:20 AM', 'YYYY/MM/DD HH:MI:SS PM')
FROM dual;

데이터 타입이 바뀌는 것일뿐 기본적으로 to_date() 와 사용법이 같다. 첫번째 파라미터로 받는 문자열의 형식에 맞게끔 date 포맷을 작성하면 문자열을 날짜 데이터로 변환한다.


날짜에서 년/월/일 추출하기

날짜에서 원하는 포맷만 출력하고 싶을땐 EXTRACT() 함수를 사용한다.

현재 날짜에서 월(month)만 추출하고 싶다면 아래의 쿼리를 입력해보면 된다.

1
2
SELECT EXTRACT(month FROM sysdate)
FROM dual;

위와 같은 방식으로 년도를 출력하고 싶다면, 

1
2
SELECT EXTRACT(year FROM sysdate)
FROM dual;

일을 출력하고 싶다면,

1
2
SELECT EXTRACT(day FROM sysdate)
FROM dual;

날짜에서 시/분/초 추출하기

1
2
SELECT extract(hour from sysdate)
FROM dual;

EXTRACT() 함수를 사용하면 년/월/일은 추출이 가능한데, 시간/분/초는 추출이 되지 않았다. 왜냐하면 해당 날짜 데이터에 시간데이터, 즉 timestamp가 존재하지 않을수 있기 때문이다. 따라서 이 경우엔 프로그래머가 임의로 날짜 데이터를 시간 데이터로 형변환(cast)해줘야 한다.

형변환시 사용하는 함수는 cast()이며, 형식은 아래와 같다.

1
cast(날짜 AS timestamp)

이를 적용하여, 다시 쿼리를 수정하면,

1
2
3
SELECT extract(hour from cast(sysdate AS timestamp))
FROM dual
GROUP BY extract(hour from cast(sysdate AS timestamp));

sysdate와 비교하기 위하여 쿼리를 다시 수정하고 조회해보았다.

현재 오후 2시인데, 시간추출이 14로 나왔으니 제대로 출력된걸 알 수 있다!


날짜 반올림

날짜는 각각(연도, 월, 일, ..)을 기준으로 round() 함수로 반올림이 가능하다.

1
2
3
4
5
6
7
8
9
SELECT sysdate,
   round(sysdate, 'CC') as "year 두자리 기준",
   round(sysdate, 'YYYY') as "month 기준",
   round(sysdate, 'DAY') as "week 기준",
   round(sysdate, 'MM') as "day 기준",
   round(sysdate, 'DDD') as "hour 기준",
   round(sysdate, 'HH') as "minute 기준",
   round(sysdate, 'MI') as "second 기준",
FROM dual;

round() 함수 조건으로 삽입하는 포맷보다 하나 뒤의 date 포맷을 기준으로 반올림하여 조회되는 것을 알 수 있다.

일(day)을 기준으로 반올림하고 싶다면, 일(DDD)이 아닌 월(MM)을 기준으로 반올림해야 한다.

1
2
SELECT sysdate AS "오늘", round(sysdate, 'MM') AS "일 기준 반올림"
FROM dual;

현재 날짜가 4월보다 5월에 가까운 26일이므로 5월로 반올림되어 조회된 것을 확인할 수 있다.