mysql last_day 사용법

by

1. last_day 함수란?

MySQL의 LAST_DAY함수는 주어진 날짜 또는 날짜/시간의 월 마지막 날을 파악하는 데 사용할 수 있다.
LAST_DAY()함수는 날짜 값을 인수로 받아 해당 날짜의 월 마지막 날을 반환합니다.

2. last_day 사용할 때 주의점

  1. LAST_DAY()는 날짜에만 작동하므로 날짜 값의 형식이 올바른지 확인하는 것이 중요합니다.
  2. 날짜 값의 형식이 올바르게 지정되지 않은 경우 LAST_DAY() 함수를 WHERE 절에 사용하면 잘못된 결과를 반환할 수 있습니다.
  3. MySQL 5.7 이전 버전에서는 LAST_DAY()함수를 사용할 수 없다. 이전 버전의 MySQL 을 사용하는 경우 다른 방법을 사용하여 월의 마지막 날을 확인해야 합니다. ex) DAYOFMONTH()

last_day 함수의 input vaule와 retunn type 을 제대로 알고 쓰지 않으면 잘못된 값(NULL)을 반환할 수 있다.

3. last_day 사용 예제

ex) 

SELECT LAST_DAY('2023-02-01') from dual;
+------------------------+
| LAST_DAY('2023-02-01') |
+------------------------+
| 2023-02-28             |
+------------------------+

SELECT LAST_DAY('2023-02-01 00:00:00') from dual;
+------------------------+
| LAST_DAY('2023-02-01') |
+------------------------+
| 2023-02-28             |
+------------------------+

SELECT LAST_DAY('2023-02-32') from dual;
+------------------------+
| LAST_DAY('2023-02-32') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Incorrect datetime value: '2023-02-32'

last_day(값) 함수의 input 은 date or datetime 이고 return 타입은 date 다.

4. last_day 함수 SQL 튜닝 활용법

가정)
TB_TEMP_JOIN 테이블에는
datetime 형식의 USER_JOIN_DTTM 컬럼에
secondary index(IDX_TEMP_JOIN_01)가 있다.

그런데 테이블 TB_TEMP_JOIN 을 조회할 때
web에서 input 값을
datetime을 받지 않고 ‘202301’ 이라는 년월 을 변수 타입으로 받는다고

where 조건절을 아래처럼 구현하게 되면

select count(*) from TB_TEMP_JOIN T1 
where 1=1
DATE_FORMAT(T1.USER_JOIN_DTTM, '%Y%m') = '202301'
;
실행계획) 
1	SIMPLE	T1	index		IDX_TEMP_JOIN_01	6		686519	Using where; Using index

IDX_TEMP_JOIN_01 인덱스를 사용할 수 없다.

DATE_FORMAT(T1.USER_JOIN_DTTM, ‘%Y%m’) 의 의도는
1달을 range 로 담고 싶은 것이고 구현 의도에 맞게
2023년 1월 1일~ 말일까지를 datetime 형식으로

where 조건절로 담을 수만 있다면 USER_JOIN_DTTM 인덱스를 사용할 수 있다.

다만 매달 말일이 다르기 때문에 문제가 되는 것인데
이것을 해결할 함수가 last_day() 이다^^
아래처럼 수정해보자.

mysql> select date_format(last_day(date_format(CONCAT('202301','01'),'%Y-%m-%d')),'%Y-%m-%d 23:59:59') from dual;

+------------------------------------------------------------------------------------------+
| date_format(last_day(date_format(CONCAT('202301','01'),'%Y-%m-%d')),'%Y-%m-%d 23:59:59') |
+------------------------------------------------------------------------------------------+
| 2023-01-31 23:59:59                                                                      |
+------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

이제는 2023년 1월달의 말일을 datetime 형식으로 알 수 있게 되었다.

그럼 실제 SQL을 튜닝해서 적용해보자.

select count(*) from TB_TEMP_JOIN T1 
where 1=1
#DATE_FORMAT(T1.USER_JOIN_DTTM, '%Y%m') = '202301'
AND T1.USER_JOIN_DTTM between date_format(CONCAT('202301','01'),'%Y-%m-%d 00:00:00') and date_format(last_day(date_format(CONCAT('202301','01'),'%Y-%m-%d')),'%Y-%m-%d 23:59:59')
;
실행계획 결과)
1	SIMPLE	T1	range	IDX_TEMP_JOIN_01	IDX_TEMP_JOIN_01	6		57536	Using where; Using index

IDX_TEMP_JOIN_01 인덱스를 range로 사용할 수 있게 되었고 검색량도 686519 -> 57536 확 줄었다.

#여기서 잠깐!

mysql 5.7이상 버전에서만 last_day()를 사용할 수 있다고 하는데
그럼 mysql 5.6 이하 하위버전은 어떻게 수정해야 하나?

위에 언급한 DAYOFMONTH()를 사용해도 되고

조금 돌아가지만 직접 구현하는 방법도 있다.^^

5. last_day 함수 만들기

user defined 함수를 만드는 건 아니고.. 성능이 느리니까 mysql에서 기본으로 지원 함수만 사용한다

2. last_day() 함수 만들기 단계

1) DATE_ADD 로 1 Mon을 더한다
2) DATE_SUB 로 1 day를 뺀다.
3) date_format 최종 말일의 datetime 형식을 맞춘다.

mysql> select date_format(DATE_SUB(DATE_ADD(date_format(CONCAT('202301','01'),'%Y-%m-%d'), interval 1 MONTH), interval 1 DAY), '%Y-%m-%d 23:59:59') from dual;

+---------------------------------------------------------------------------------------------------------------------------------------+
| date_format(DATE_SUB(DATE_ADD(date_format(CONCAT('202301','01'),'%Y-%m-%d'), interval 1 MONTH), interval 1 DAY), '%Y-%m-%d 23:59:59') |
+---------------------------------------------------------------------------------------------------------------------------------------+
| 2023-01-31 23:59:59                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

조금 돌아가지만 last_day()를 구현했다.

이제 실제 적용해보자.

select count(*) from TB_TEMP_JOIN T1 
where 1=1
#DATE_FORMAT(T1.USER_JOIN_DTTM, '%Y%m') = '202301'
AND T1.USER_JOIN_DTTM between date_format(CONCAT('202301','01'),'%Y-%m-%d 00:00:00') and date_format(DATE_SUB(DATE_ADD(date_format(CONCAT('202301','01'),'%Y-%m-%d'), interval 1 MONTH), interval 1 DAY), '%Y-%m-%d 23:59:59')
;

실행계획 결과)
1	SIMPLE	T1	range	IDX_TEMP_JOIN_01	IDX_TEMP_JOIN_01	6		57536	Using where; Using index

동일하게 IDX_TEMP_JOIN_01 인덱스를 사용할 수 있음을 확인했다.

끝.

Leave a Comment