Mysql 기준일 부터 N달치 일자 리스트 가져오기

기준일부터 이후 2달 치 날짜 리스트를 가져와야 하는 경우가 생겼습니다.

 

1. 조회 방법

SELECT DATE_ADD('기준일', INTERVAL n-1 DAY) AS '날짜'
FROM (
    SELECT n
    FROM (
        SELECT row_number() over(order by t1.n1, t2.n2) as n
        FROM (SELECT 0 as n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
             (SELECT 0 as n2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
    ) days
    WHERE n <= DATEDIFF(DATE_ADD('기준일', INTERVAL 기간 MONTH), '기준일')
) date_list
ORDER BY '날짜';


8월1일부터 2달 후 예제
SELECT DATE_ADD('2023-08-01', INTERVAL n-1 DAY) AS '날짜'
FROM (
    SELECT n
    FROM (
        SELECT row_number() over(order by t1.n1, t2.n2) as n
        FROM (SELECT 0 as n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
             (SELECT 0 as n2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
    ) days
    WHERE n <= DATEDIFF(DATE_ADD('2023-08-01', INTERVAL 2 MONTH), '2023-08-01')
) date_list
ORDER BY '날짜';

 

2. 조회 결과

2023-08-01
2023-08-02
2023-08-03
2023-08-04
2023-08-05
.
.
.
.
2023-09-24
2023-09-25
2023-09-26
2023-09-27
2023-09-28
2023-09-29
2023-09-30