[Postgresql] 영업일(business day)구하기

1. 입력한 기간 및 날짜에서 가장 가까운 이전 영업일 찾기

SELECT MAX(S.DAYS) AS WORK_DAY
FROM (
    SELECT S.DAYS::DATE
    FROM GENERATE_SERIES('20230101'::DATE, '20501231', '1 DAY') S(DAYS)
    WHERE EXTRACT(DOW FROM S.DAYS) BETWEEN 1 AND 5
    EXCEPT
    SELECT HOLIDAY_DATE::DATE
    FROM TB_HOLIDAY
    ) S
WHERE S.DAYS < '20231001'

 

2. 입력한 기간 중 영업일 리스트 조회

 WITH DAYS AS
(
    SELECT TO_CHAR(DD, 'YYYYMMDD') DD, EXTRACT(ISODOW FROM DD) DW
    FROM GENERATE_SERIES('20230101'::DATE, '20501231'::DATE, '1 DAY'::INTERVAL) DD
)
SELECT DD, DATE_PART('DAY', date_trunc('day', now()) - TO_DATE(DD, 'YYYYMMDD'))
FROM   DAYS
WHERE  DW NOT IN (6,7);
	   AND NOT EXISTS (SELECT 1 FROM TB_HOLIDAY H WHERE H.HOLIDAY_DATE = DAYS.DD)

 

3. 입력한 날짜가 공휴일인지 조회

SELECT CASE
    WHEN EXISTS (
	        SELECT 1
	        FROM TB_HOLIDAY
	        WHERE HOLIDAY_DATE = '20231003'
    ) OR EXTRACT(ISODOW FROM TO_DATE('20231003' , 'YYYYMMDD')) IN (6, 7) THEN '공휴일'
    ELSE '평일'
	END AS HOLIDAY_STATUS

 

TB_RESERVE_INFO의 RESERVE_DT가 영업일 기준 n일 이후/이전 날짜와 일치하면 STATUS를 바꿔주는 쿼리문을 구현해야 한다.

• 국가공휴일은 쿼리문에서 받아올 방법이 없으므로 API를 사용해 TB_HOLIDAY 테이블에 데이터로 저장한다.
• 요일을 추출해내는 'EXTRACT(ISODOW from ...)' 함수를 사용하여 6(토요일) 혹은 7(일요일)이면 공휴일로 판단한다.

영업일 기준 n일 이전 날짜를 구하는 부분을 위의 두번째 방법을 응용하여 아래와 같이 구현했다.

SELECT *
  FROM TB_RESERVE_INFO RI
 WHERE RI.RESERVE_DT in(
                    WITH DAYS AS
                    (
                        SELECT TO_CHAR(DD, 'YYYYMMDD') DD, EXTRACT(ISODOW FROM DD) DW
                        FROM generate_series('20230101'::DATE, '20241231'::DATE, '1 DAY'::INTERVAL) DD
                    )
                    SELECT DD
                    FROM   DAYS
                    WHERE  DW not in (6,7)
                           and NOT EXISTS (SELECT 1 FROM TB_HOLIDAY H WHERE H.HOLIDAY_DATE = DAYS.DD)
                           and date_part('DAY', date_trunc('day', now()) - TO_DATE(DD, 'YYYYMMDD'))>(SELECT CRITERIA_DAYS 
                                                                                                      FROM TB_CRITERIA_INFO)::INT
                )

postgresql의 'with', 'ISODOW', 'generate_series', 'recursive' 키워드를 알게 되었다.

 

공휴일 데이터 API 조회하는 방법은 아래 포스팅을 참고

2023.11.03 - [WEB/JAVASCRIPT] - 공휴일 API - 공공데이터 포털에서 공휴일 데이터 조회 -1. API신청

2023.11.09 - [WEB/JAVASCRIPT] - 공휴일 API - 공공데이터 포털에서 공휴일 데이터 조회 -2. API사용