MYSQL 달력 만들기

 

# TABLE 달력 생성

 

create table t (n int);

insert into t values (1);

insert into t select * from t; -- 이걸 13번 반복하면 4096행이 생성됨. 10년치 데이터라면 대략 3650일이므로 이정도면 충분

create table date_t (d date, ds char(8)); -- 날짜를 저장할 테이블

 

INSERT INTO date_t
  SELECT d, DATE_FORMAT(d, '%Y%m%d'), WEEK FROM (
    SELECT @rnum:=@rnum+1 AS rownum, DATE(ADDDATE('2023-01-01', INTERVAL @rnum DAY)) AS d, 
    CASE DAYOFWEEK(DATE(ADDDATE('2023-01-01', INTERVAL @rnum DAY)))
      WHEN '1' THEN '일요일'
      WHEN '2' THEN '월요일'
      WHEN '3' THEN '화요일'
      WHEN '4' THEN '수요일'
      WHEN '5' THEN '목요일'
      WHEN '6' THEN '금요일'
      WHEN '7' THEN '토요일'
END AS WEEK
  FROM (SELECT @rnum:=-1) r, t
  ) t
WHERE YEAR(d) < 2033;

 

# 쿼리로 월별 일자 생성

 

: 단순히 테이블 저장이 아닌 SELECT 구문을 통해 월별 달력일자를 추출해보자

 

SELECT dt + INTERVAL lv-1 DAY dt

FROM (-- connect by level 대신 시스템정보 테이블 columns 를 이용

SELECT ordinal_position lv

, CONCAT('202212', '01') dt

FROM information_schema.columns

WHERE table_schema = 'mysql'

AND table_name = 'user'

) a

WHERE lv <= DAY(LAST_DAY(dt))

;

 

# 쿼리로 달력만들기

: SELECT 문을 통해 달력을 만들어보자

SELECT ym

, MIN(CASE dw WHEN 1 THEN d END) Sun

, MIN(CASE dw WHEN 2 THEN d END) Mon

, MIN(CASE dw WHEN 3 THEN d END) Tue

, MIN(CASE dw WHEN 4 THEN d END) Wed

, MIN(CASE dw WHEN 5 THEN d END) Thu

, MIN(CASE dw WHEN 6 THEN d END) Fri

, MIN(CASE dw WHEN 7 THEN d END) Sat

FROM (SELECT DATE_FORMAT(dt,'%Y%m') ym

, WEEK(dt) w

, DAY(dt) d

, DAYOFWEEK(dt) dw

FROM (SELECT CONCAT(Y, '0101') + INTERVAL a*100 + b*10 + c DAY dt

FROM (SELECT 0 a

UNION ALL SELECT 1

UNION ALL SELECT 2

UNION ALL SELECT 3

) a

, (SELECT 0 b

UNION ALL SELECT 1

UNION ALL SELECT 2

UNION ALL SELECT 3

UNION ALL SELECT 4

UNION ALL SELECT 5

UNION ALL SELECT 6

UNION ALL SELECT 7

UNION ALL SELECT 8

UNION ALL SELECT 9

) b

, (SELECT 0 c

UNION ALL SELECT 1

UNION ALL SELECT 2

UNION ALL SELECT 3

UNION ALL SELECT 4

UNION ALL SELECT 5

UNION ALL SELECT 6

UNION ALL SELECT 7

UNION ALL SELECT 8

UNION ALL SELECT 9

) c

, (SELECT '2023' Y) d

WHERE a*100 + b*10 + c < DAYOFYEAR(CONCAT(Y, '1231'))

) a

) a

GROUP BY ym, w

;