[mysql] 오늘, 이번주, 이번달, 올해

 

-- 오늘
with
T as (
    select CURDATE() as startDate
)
select *
from T
;

-- 이번주 (일요일 ~ 토요일)
with recursive
T as (
    select (ADDDATE( CURDATE(), - WEEKDAY(CURDATE()) - 1 )) as startDate
    union all
    select startDate + interval 1 day 
    from T 
    where CURDATE() > startDate
)
select *
from T
;

-- 이번달 (1일 ~ 31일)
with recursive
T as (
    select last_day(now() - interval 1 month) + interval 1 day as startDate
    union all
    select startDate + interval 1 day 
    from T 
    where date_add(now(), interval -1 day) > startDate
)
select *
from T
;

-- 올해 (1월 ~ 12월)
with recursive
T as (
    select MAKEDATE(YEAR(NOW()),1) as startDate
    union all
    select startDate + interval 1 month 
    from T 
    where date_add(now(), interval -1 month) > startDate
)
select *
from T
;

 

-- 시작일과 종료일 사이에 날짜 구하기
with recursive
T as (
    select str_to_date('20240211', '%Y%m%d') as startDate
    union all
    select startDate + interval 1 day 
    from T 
    where str_to_date('20240219', '%Y%m%d') > startDate
)
select *
from T
;

 

 

set @start_dt = '20240201';
set @end_dt = '20240229';

with recursive
T as (
    -- 대상 날짜를 구한다
    select str_to_date(@start_dt, '%Y%m%d') as startDate
    union all
    select startDate + interval 1 day 
    from T 
    where str_to_date(@end_dt, '%Y%m%d') > startDate
)
, M as (
    -- 일자별 데이터를 집계한다
    select date_format(request_dt, '%Y%m%d') as request_dt, response_status, count(1) cnt
    from tb_log tl
    where 1=1
    and request_dt >= str_to_date(@start_dt, '%Y%m%d')
    and request_dt < date_add(str_to_date(@end_dt, '%Y%m%d'), interval 1 day)
    and response_status = 500
    group by date_format(request_dt, '%Y%m%d'), response_status
)
, A as (
    -- 대상 날짜와 집계 데이터를 조인한다
    select T.startDate, ifnull(M.cnt, 0) cnt
    from T
    left join M on T.startDate = M.request_dt
)
select *
from A
order by 1
;