[PostgreSQL] 주말, 심야, 공휴일 통계 쿼리 예제

insert_dtm 값이 문자열(YYYYMMDDHH24MISS) 형태인 20250926002144 라고 가정하고 PostgreSQL 기준으로 작성할게.
핵심은 timestamp로 변환 → 요일 / 시간 추출 → 조건 필터야.

특정 월(예: 2025년 2월) + 주말 또는 심야(22시 이후) 조회

SELECT *
FROM access_log
WHERE 
    TO_TIMESTAMP(insert_dtm, 'YYYYMMDDHH24MISS') 
        BETWEEN TIMESTAMP '2025-02-01 00:00:00'
        AND TIMESTAMP '2025-02-28 23:59:59'
AND (
        -- 주말
        EXTRACT(DOW FROM TO_TIMESTAMP(insert_dtm, 'YYYYMMDDHH24MISS')) IN (0,6)
        
        OR
        
        -- 심야 22시 이후
        EXTRACT(HOUR FROM TO_TIMESTAMP(insert_dtm, 'YYYYMMDDHH24MISS')) >= 22
);

설명

• TO_TIMESTAMP(insert_dtm,'YYYYMMDDHH24MISS')
  → 문자열을 timestamp로 변환
• EXTRACT(DOW)
  • 0 = 일요일
  • 6 = 토요일
• EXTRACT(HOUR)
  • 시간 추출
  • >= 22 → 22시 이후

---

성능 고려 (추천)

문자열을 매번 TO_TIMESTAMP 하면 인덱스를 못탐.
그래서 문자열 범위 조건 먼저 거는게 좋아.

SELECT *
FROM access_log
WHERE insert_dtm BETWEEN '20250201000000' AND '20250228235959'
AND (
        EXTRACT(DOW FROM TO_TIMESTAMP(insert_dtm, 'YYYYMMDDHH24MISS')) IN (0,6)
     OR EXTRACT(HOUR FROM TO_TIMESTAMP(insert_dtm, 'YYYYMMDDHH24MISS')) >= 22
);

---

월별 통계까지 같이 보고 싶다면

예를 들어 날짜 + 건수

SELECT 
    TO_CHAR(TO_TIMESTAMP(insert_dtm,'YYYYMMDDHH24MISS'),'YYYY-MM-DD') AS access_date,
    COUNT(*) AS cnt
FROM access_log
WHERE insert_dtm BETWEEN '20250201000000' AND '20250228235959'
AND (
        EXTRACT(DOW FROM TO_TIMESTAMP(insert_dtm,'YYYYMMDDHH24MISS')) IN (0,6)
     OR EXTRACT(HOUR FROM TO_TIMESTAMP(insert_dtm,'YYYYMMDDHH24MISS')) >= 22
)
GROUP BY access_date
ORDER BY access_date;

---

주말 + 22시 이후 심야 + 한국 법정공휴일을 포함

---

1️⃣ 공휴일 테이블 (예시)

예를 들어 이런 테이블이 있다고 가정

CREATE TABLE holiday (
    holi_dt date PRIMARY KEY,
    holi_nm varchar(100)
);

예시 데이터

INSERT INTO holiday VALUES ('2025-01-01','신정');
INSERT INTO holiday VALUES ('2025-03-01','삼일절');
INSERT INTO holiday VALUES ('2025-05-05','어린이날');

---

2️⃣ 주말 + 심야 + 공휴일 조회 쿼리

SELECT a.*
FROM access_log a
LEFT JOIN holiday h
       ON h.holi_dt = TO_TIMESTAMP(a.insert_dtm,'YYYYMMDDHH24MISS')::date
WHERE a.insert_dtm BETWEEN '20250201000000' AND '20250228235959'
AND (
        -- 주말
        EXTRACT(DOW FROM TO_TIMESTAMP(a.insert_dtm,'YYYYMMDDHH24MISS')) IN (0,6)        OR        -- 심야 22시 이후
        EXTRACT(HOUR FROM TO_TIMESTAMP(a.insert_dtm,'YYYYMMDDHH24MISS')) >= 22        OR        -- 법정 공휴일
        h.holi_dt IS NOT NULL
);

---

3️⃣ 성능 개선 버전 (실무 추천)

TO_TIMESTAMP를 여러 번 쓰지 않게 한번만 변환

SELECT *
FROM (
    SELECT 
        a.*,
        TO_TIMESTAMP(insert_dtm,'YYYYMMDDHH24MISS') AS ts
    FROM access_log a
    WHERE insert_dtm BETWEEN '20250201000000' AND '20250228235959'
) t
LEFT JOIN holiday h
       ON h.holi_dt = t.ts::date
WHERE 
        EXTRACT(DOW FROM ts) IN (0,6)
     OR EXTRACT(HOUR FROM ts) >= 22
     OR h.holi_dt IS NOT NULL;

---

4️⃣ 결과 구분 컬럼까지 추가 (추천)

실무에서는 왜 조회됐는지 구분하는 컬럼도 많이 넣어.

SELECT 
    t.*,
    CASE
        WHEN h.holi_dt IS NOT NULL THEN '공휴일'
        WHEN EXTRACT(DOW FROM ts) IN (0,6) THEN '주말'
        WHEN EXTRACT(HOUR FROM ts) >= 22 THEN '심야'
    END AS access_type
FROM (
    SELECT 
        a.*,
        TO_TIMESTAMP(insert_dtm,'YYYYMMDDHH24MISS') AS ts
    FROM access_log a
    WHERE insert_dtm BETWEEN '20250201000000' AND '20250228235959'
) t
LEFT JOIN holiday h
       ON h.holi_dt = t.ts::date
WHERE 
        EXTRACT(DOW FROM ts) IN (0,6)
     OR EXTRACT(HOUR FROM ts) >= 22
     OR h.holi_dt IS NOT NULL;

---

매월 자동으로 실행할 수 있게 현재월 기준으로 조회하면서 주말 / 심야 / 공휴일 구분까지 포함한 PostgreSQL 쿼리

핵심 포인트

• date_trunc('month', now()) → 이번달 시작일
• + interval '1 month - 1 second' → 이번달 마지막
• timestamp 변환은 한번만 수행

---

1️⃣ 매월 자동 조회 쿼리 (주말 / 심야 / 공휴일 구분)

SELECT 
    t.*,
    CASE
        WHEN h.holi_dt IS NOT NULL THEN '공휴일'
        WHEN EXTRACT(DOW FROM t.ts) IN (0,6) THEN '주말'
        WHEN EXTRACT(HOUR FROM t.ts) >= 22 THEN '심야'
    END AS access_type
FROM (
    SELECT 
        a.*,
        TO_TIMESTAMP(a.insert_dtm,'YYYYMMDDHH24MISS') AS ts
    FROM access_log a
    WHERE a.insert_dtm BETWEEN 
          TO_CHAR(date_trunc('month', now()), 'YYYYMMDD') || '000000'
      AND TO_CHAR((date_trunc('month', now()) + interval '1 month - 1 second'), 'YYYYMMDDHH24MISS')
) t
LEFT JOIN holiday h
       ON h.holi_dt = t.ts::date
WHERE
        EXTRACT(DOW FROM t.ts) IN (0,6)   -- 주말
     OR EXTRACT(HOUR FROM t.ts) >= 22     -- 심야
     OR h.holi_dt IS NOT NULL;            -- 공휴일

---

2️⃣ 결과 예시

insert_dtm	ts	access_type
20250202103011	2025-02-02 10:30	주말
20250203101521	2025-02-03 23:15	심야
20250201110022	2025-02-01 11:00	공휴일

---

3️⃣ 통계용 쿼리 (추천)

월별 감사 로그에서 몇 건인지 보통 같이 뽑아.

SELECT 
    access_type,
    COUNT(*) cnt
FROM (
    SELECT 
        CASE
            WHEN h.holi_dt IS NOT NULL THEN '공휴일'
            WHEN EXTRACT(DOW FROM ts) IN (0,6) THEN '주말'
            WHEN EXTRACT(HOUR FROM ts) >= 22 THEN '심야'
        END AS access_type
    FROM (
        SELECT 
            TO_TIMESTAMP(insert_dtm,'YYYYMMDDHH24MISS') ts
        FROM access_log
        WHERE insert_dtm BETWEEN 
              TO_CHAR(date_trunc('month', now()), 'YYYYMMDD') || '000000'
          AND TO_CHAR((date_trunc('month', now()) + interval '1 month - 1 second'), 'YYYYMMDDHH24MISS')
    ) t
    LEFT JOIN holiday h
           ON h.holi_dt = t.ts::date
) x
GROUP BY access_type
ORDER BY access_type;

결과 예

access_type	cnt
공휴일	15
주말	84
심야	22

---

4️⃣ 성능 팁 (중요)

insert_dtm이 문자열이면 index 필수

CREATE INDEX idx_access_log_insert_dtm
ON access_log(insert_dtm);

이러면

• 월 범위 검색 → index 사용
• timestamp 변환 → 결과 subset만 수행

holiday 테이블에 2026년 한국 법정공휴일 + 대체공휴일을 넣는 SQL
(설날, 추석, 대체공휴일 포함)

한국 공휴일 INSERT (2026년~2035년)

INSERT INTO holiday (holi_dt, holi_nm) VALUES
('2026-01-01','신정'),('2026-02-16','설날 연휴'),
('2026-02-17','설날'),
('2026-02-18','설날 연휴'),('2026-03-01','삼일절'),
('2026-03-02','삼일절 대체공휴일'),('2026-05-05','어린이날'),('2026-05-24','부처님오신날'),
('2026-05-25','부처님오신날 대체공휴일'),('2026-06-03','전국동시지방선거일'),('2026-06-06','현충일'),('2026-08-15','광복절'),
('2026-08-17','광복절 대체공휴일'),('2026-09-24','추석 연휴'),
('2026-09-25','추석'),
('2026-09-26','추석 연휴'),('2026-10-03','개천절'),
('2026-10-05','개천절 대체공휴일'),('2026-10-09','한글날'),('2026-12-25','성탄절');

INSERT INTO holiday VALUES
('2027-01-01','신정'),
('2027-02-06','설날 연휴'),
('2027-02-07','설날'),
('2027-02-08','설날 연휴'),
('2027-03-01','삼일절'),
('2027-05-05','어린이날'),
('2027-05-13','부처님오신날'),
('2027-06-06','현충일'),
('2027-08-15','광복절'),
('2027-09-14','추석 연휴'),
('2027-09-15','추석'),
('2027-09-16','추석 연휴'),
('2027-10-03','개천절'),
('2027-10-09','한글날'),
('2027-12-25','성탄절');

INSERT INTO holiday VALUES
('2028-01-01','신정'),
('2028-01-25','설날 연휴'),
('2028-01-26','설날'),
('2028-01-27','설날 연휴'),
('2028-03-01','삼일절'),
('2028-05-02','부처님오신날'),
('2028-05-05','어린이날'),
('2028-06-06','현충일'),
('2028-08-15','광복절'),
('2028-10-02','추석 연휴'),
('2028-10-03','추석'),
('2028-10-04','추석 연휴'),
('2028-10-03','개천절'),
('2028-10-09','한글날'),
('2028-12-25','성탄절');

INSERT INTO holiday VALUES
('2029-01-01','신정'),
('2029-02-13','설날 연휴'),
('2029-02-14','설날'),
('2029-02-15','설날 연휴'),
('2029-03-01','삼일절'),
('2029-05-20','부처님오신날'),
('2029-05-05','어린이날'),
('2029-06-06','현충일'),
('2029-08-15','광복절'),
('2029-09-21','추석 연휴'),
('2029-09-22','추석'),
('2029-09-23','추석 연휴'),
('2029-10-03','개천절'),
('2029-10-09','한글날'),
('2029-12-25','성탄절');

INSERT INTO holiday VALUES
('2030-01-01','신정'),
('2030-02-03','설날 연휴'),
('2030-02-04','설날'),
('2030-02-05','설날 연휴'),
('2030-03-01','삼일절'),
('2030-05-09','부처님오신날'),
('2030-05-05','어린이날'),
('2030-06-06','현충일'),
('2030-08-15','광복절'),
('2030-09-11','추석 연휴'),
('2030-09-12','추석'),
('2030-09-13','추석 연휴'),
('2030-10-03','개천절'),
('2030-10-09','한글날'),
('2030-12-25','성탄절');

INSERT INTO holiday VALUES
('2031-01-01','신정'),
('2031-01-22','설날 연휴'),
('2031-01-23','설날'),
('2031-01-24','설날 연휴'),
('2031-03-01','삼일절'),
('2031-05-28','부처님오신날'),
('2031-05-05','어린이날'),
('2031-06-06','현충일'),
('2031-08-15','광복절'),
('2031-09-29','추석 연휴'),
('2031-09-30','추석'),
('2031-10-01','추석 연휴'),
('2031-10-03','개천절'),
('2031-10-09','한글날'),
('2031-12-25','성탄절');

INSERT INTO holiday VALUES
('2032-01-01','신정'),
('2032-02-10','설날 연휴'),
('2032-02-11','설날'),
('2032-02-12','설날 연휴'),
('2032-03-01','삼일절'),
('2032-05-16','부처님오신날'),
('2032-05-05','어린이날'),
('2032-06-06','현충일'),
('2032-08-15','광복절'),
('2032-09-18','추석 연휴'),
('2032-09-19','추석'),
('2032-09-20','추석 연휴'),
('2032-10-03','개천절'),
('2032-10-09','한글날'),
('2032-12-25','성탄절');

INSERT INTO holiday VALUES
('2033-01-01','신정'),
('2033-01-30','설날 연휴'),
('2033-01-31','설날'),
('2033-02-01','설날 연휴'),
('2033-03-01','삼일절'),
('2033-05-06','부처님오신날'),
('2033-05-05','어린이날'),
('2033-06-06','현충일'),
('2033-08-15','광복절'),
('2033-09-07','추석 연휴'),
('2033-09-08','추석'),
('2033-09-09','추석 연휴'),
('2033-10-03','개천절'),
('2033-10-09','한글날'),
('2033-12-25','성탄절');

INSERT INTO holiday VALUES
('2034-01-01','신정'),
('2034-02-18','설날 연휴'),
('2034-02-19','설날'),
('2034-02-20','설날 연휴'),
('2034-03-01','삼일절'),
('2034-05-25','부처님오신날'),
('2034-05-05','어린이날'),
('2034-06-06','현충일'),
('2034-08-15','광복절'),
('2034-09-26','추석 연휴'),
('2034-09-27','추석'),
('2034-09-28','추석 연휴'),
('2034-10-03','개천절'),
('2034-10-09','한글날'),
('2034-12-25','성탄절');

INSERT INTO holiday VALUES
('2035-01-01','신정'),
('2035-02-07','설날 연휴'),
('2035-02-08','설날'),
('2035-02-09','설날 연휴'),
('2035-03-01','삼일절'),
('2035-05-15','부처님오신날'),
('2035-05-05','어린이날'),
('2035-06-06','현충일'),
('2035-08-15','광복절'),
('2035-09-15','추석 연휴'),
('2035-09-16','추석'),
('2035-09-17','추석 연휴'),
('2035-10-03','개천절'),
('2035-10-09','한글날'),
('2035-12-25','성탄절');

---

💡 실무 팁

지금 만든 테이블을 앞으로 계속 쓰려면 보통 이렇게 관리해.

CREATE INDEX idx_holiday_dt ON holiday(holi_dt);

그리고 조회할 때는

ts::date IN (SELECT holi_dt FROM holiday)

또는

LEFT JOIN holiday