3. Database/쿼리_Func_Proc

[Oracle] Oracle 달력 만들기 및 날짜 쿼리 활용하기

[Oracle] oracle 달력 만들기 및 날짜 쿼리 활용하기

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ 오라클 쿼리로 날짜 만들기 ] 입니다. : )

0. 달력을 만들기 앞서

가끔 오라클을 통해 달력을 만들어 활용해야하는 경우가 있다.

오라클 쿼리로 달력을 만들면서, 이때 사용한 쿼리와 관련된 날짜와 관련된 함수들도 알아볼 예정이다.

▶ 1. 해당월의 첫날 부터 마지막 일까지 가져오기

SELECT  TO_CHAR (FIRST_DAY + LEVEL - 1, 'D') DAYS -- 요일(숫자)
		, TO_CHAR (FIRST_DAY + LEVEL - 1, 'MM-DD') TODAY -- 해당일자
		, TO_CHAR (FIRST_DAY + LEVEL - 1, 'YYYYMMDD') TODAY_STR -- 해당일자str
		, TO_CHAR(FIRST_DAY + LEVEL - 1,'IW') WEEK_NUM -- 주차수
		, TO_CHAR(FIRST_DAY + LEVEL - 1,'DY') DAYS_K -- 요일(한글)
FROM    (SELECT TRUNC(SYSDATE, 'MM') FIRST_DAY FROM DUAL)
CONNECT BY FIRST_DAY + LEVEL - 1 <= TRUNC(LAST_DAY(SYSDATE))

▶ 2. 달력 모양으로 가공하기

SELECT  CAL.*                                       
FROM    
        (
            SELECT   COUNT (*) OVER (ORDER BY WEEK_NUM) WEEK_NUM
                    , TO_DATE (MIN (TODAY_STR) || '000000', 'YYYYMMDDHH24MISS') week_strt
                    , TO_DATE (MAX (TODAY_STR) || '235959', 'YYYYMMDDHH24MISS') week_end
                    , MIN (DECODE (DAYS, 2, TODAY)) MON
                    , MIN (DECODE (DAYS, 3, TODAY)) TUE
                    , MIN (DECODE (DAYS, 4, TODAY)) WED
                    , MIN (DECODE (DAYS, 5, TODAY)) THU
                    , MIN (DECODE (DAYS, 6, TODAY)) FRI
                    , MIN (DECODE (DAYS, 7, TODAY)) SAT
                    , MIN (DECODE (DAYS, 1, TODAY)) SUN
            FROM
            (
                SELECT  TO_CHAR (FIRST_DAY + LEVEL - 1, 'D') DAYS -- 요일(숫자)
                        , TO_CHAR (FIRST_DAY + LEVEL - 1, 'MM-DD') TODAY -- 해당일자
                        , TO_CHAR (FIRST_DAY + LEVEL - 1, 'YYYYMMDD') TODAY_STR -- 해당일자str
                        , TO_CHAR(FIRST_DAY + LEVEL - 1,'IW') WEEK_NUM -- 주차수
                        , TO_CHAR(FIRST_DAY + LEVEL - 1,'DY') DAYS_K -- 요일(한글)
                FROM    (SELECT TRUNC(SYSDATE, 'MM') FIRST_DAY FROM DUAL)
                CONNECT BY FIRST_DAY + LEVEL - 1 <= TRUNC(LAST_DAY(SYSDATE))
            )
            GROUP BY WEEK_NUM
        ) CAL
;

해당 쿼리를 실행하면 현재 기준인 2020년 4월 달력의 모습을 가져왔다.

급한경우엔 해당 쿼리를 응용하여 바로 사용하고,

이 쿼리를 만들면서 사용한 관련 쿼리 내용들도 살펴보자.

▶ 3. 오라클 날짜 함수 관련 내용

1) Date Format Elements

Format	설명
YYYY	4자리 연도
YY	두자리 연도
D	Day of week (1-7)
DAY	요일(월,화..)
DD	Day of month (1-31)
DDD	Day of year (1-366)
MM	해당월을 01~12로 표시
MONTH	Name of month
WW	Week of year (1-53)
IW	Week of year (1-53) 국제 표준
W	Week of month (1-5)
HH24	Hour of day (0-23)
HH	Hour of day (1-12)
MI	Minute (0-59)
SS	Second (0-59)

- "WW"와 "IW" 모두 1년의 몇 주차(1~53)로 조회하는 포맷 이다.

- "WW" : 1일에서 7일까지가 1주차로 시작

- "IW" : 실제 달력에 맞게 주차가 계산

2) TO_DATE

- TO_DATE 함수는 CHAR, VARCHAR2형을 DATE 타입으로 변환한다.
- TO_DATE(char, format) 형식으로 사용 할 수 있다.
- Date Format Elements 에서 'W', 'WW' Format을 제외한 나머지는 TO_DATE 함수의 format으로 사용 할 수 있다.

3) TO_CHAR

- TO_CHAR 함수는 DATE형, NUMBER형을 문자 타입으로 변환하는 함수이다.

ex) 1. 주차 계산

-- 해당일의 주차
SELECT TO_CHAR(SYSDATE,'IW') FROM DUAL;

-- 해당 주의 첫번째 일자 [주초 일자]
SELECT
        SYSDATE AS 오늘 
        ,  SYSDATE - 7 AS "7일전"
        , TRUNC(SYSDATE, 'W') AS 해당월1일_요일_기준 --해당월1일 요일을 기준한 주초일자
        , TRUNC(SYSDATE, 'IW') AS 월요일_기준
        , TRUNC(SYSDATE, 'DY') AS 일요일_기준
FROM DUAL;
/* 
오늘                       7일전                      해당월1일_요일_기준   월요일_기준   일요일_기준
2020/04/14 오후 4:59:59    2020/04/07 오후 4:59:59    2020/04/08            2020/04/13    2020/04/12
*/

ex) 2. 요일

SELECT SYSDATE FROM DUAL; -- 2020/04/14 오후 4:59:33

SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; -- 3

SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL; -- 화

SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL; -- 화요일

SELECT TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') FROM DUAL; -- TUE

SELECT TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH') FROM DUAL; -- TUESDAY

ex) 3. 요일로 날짜 계산

-- NEXT_DAY(날짜, '요일' OR 숫자)
SELECT NEXT_DAY((TRUNC(SYSDATE,'MM')-1),'월') FROM DUAL; --2020/04/06

SELECT NEXT_DAY((TRUNC(SYSDATE,'MM')-1),'화요일') FROM DUAL; -- 2020/04/07

SELECT NEXT_DAY((TRUNC(SYSDATE,'MM')-1),3) FROM DUAL; -- 2020/04/07

-- 당월 마지막 주 특정 요일
SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),'월') FROM DUAL; -- 2020/04/27 오후 5:07:03

SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),'화요일') FROM DUAL; -- 2020/04/28 오후 5:07:12

SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),3) FROM DUAL; -- 2020/04/28 오후 5:07:16

-- 월요일기준
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') 오늘날짜
        , TO_CHAR(TRUNC(SYSDATE,'IW')  ,'YYYY-MM-DD DAY') 월
        , TO_CHAR(TRUNC(SYSDATE,'IW')+1,'YYYY-MM-DD DAY') 화
        , TO_CHAR(TRUNC(SYSDATE,'IW')+2,'YYYY-MM-DD DAY') 수
        , TO_CHAR(TRUNC(SYSDATE,'IW')+3,'YYYY-MM-DD DAY') 목
        , TO_CHAR(TRUNC(SYSDATE,'IW')+4,'YYYY-MM-DD DAY') 금
        , TO_CHAR(TRUNC(SYSDATE,'IW')+5,'YYYY-MM-DD DAY') 토
        , TO_CHAR(TRUNC(SYSDATE,'IW')+6,'YYYY-MM-DD DAY') 일
FROM DUAL;

-- 일요일기준
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') 오늘날짜
        , TO_CHAR(TRUNC(SYSDATE,'D')  ,'YYYY-MM-DD DAY') 일
        , TO_CHAR(TRUNC(SYSDATE,'D')+1,'YYYY-MM-DD DAY') 월
        , TO_CHAR(TRUNC(SYSDATE,'D')+2,'YYYY-MM-DD DAY') 화
        , TO_CHAR(TRUNC(SYSDATE,'D')+3,'YYYY-MM-DD DAY') 수
        , TO_CHAR(TRUNC(SYSDATE,'D')+4,'YYYY-MM-DD DAY') 목
        , TO_CHAR(TRUNC(SYSDATE,'D')+5,'YYYY-MM-DD DAY') 금
        , TO_CHAR(TRUNC(SYSDATE,'D')+6,'YYYY-MM-DD DAY') 토
FROM DUAL;

ex) 4. etc

-- 당월 마지막 날짜
SELECT LAST_DAY(SYSDATE) FROM DUAL;

-- 당월 첫째 날
SELECT TRUNC(SYSDATE,'MM') FROM DUAL;

-- 다음달 첫째 날
SELECT ADD_MONTHS( TRUNC(SYSDATE,'MM'), 1) FROM DUAL;

-- 해당일 포함 된 주의 목요일 날짜
SELECT  CASE WHEN TO_CHAR(TO_DATE('20100629','YYYYMMDD'), 'D') < 5 THEN TRUNC(TO_DATE('20100629','YYYYMMDD')-5, 'IW')+3
        ELSE TRUNC(TO_DATE('20100629','YYYYMMDD'), 'IW')+3 END RESULT
FROM DUAL;

-- 당월 마지막 날의 요일 구하기
SELECT TO_CHAR(LAST_DAY(SYSDATE),'D') FROM DUAL;

-- 마지막주 월요일
SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),2)-1 FROM DUAL;

-- 마지막주 목요일
SELECT NEXT_DAY((LAST_DAY(SYSDATE)-7),5) FROM DUAL;