MySQL 시간 관련 function 정리(DATE_FORMAT, STR_TO_DATE, DATE_ADD)

Function

---

DATE_FORMAT()

WHERE date_format(sales_ym, '%Y%m') = date_format(date_add(now(), interval - 1 month), '%Y%m'), '%Y%m'

값을 주어진 형식에 맞춰 문자열로 변환하는 함수이다. 위처럼 sales_ym이 char(6)의 문자열일 때 이를 date_format으로 받을 수 없다. date_format은 단순 문자열이 아니라 날짜 또는 시간만 받을 수 있다.

WHERE sales_ym = date_format(date_add(now(), interval - 1 month), '%Y%m')

그렇기 때문에 가장 위처럼 하는 것은 이미 202403, 202404 등의 형식으로 저장된 문자열을 의미 없이 같은 타입의 데이터로 변환하는 것이다. 때문에 하지 않아도 되는 행동이다.

select
	date_format('2024-03-10 22:33:11', '%Y%m') AS d1. -- 성공 '202403'
	
select
	date_format('2024-03', '%Y%m') AS d1 -- 실패 NULL
	
select
	date_format('2024-03-10', '%Y%m') AS d1 -- 성공 '202403'
	
select
	date_format('20240310', '%Y%m') AS d1 -- 성공 '202403'

select
	date_format('202403', '%Y%m') AS d1 -- 실패 NULL

select
	date_format('2024-01-10 10:10:10', '%Y%m') as d1 -- 성공 '202401'

select 
	date_format('2024-01-10 10:10:10', '%Y%m') as d1 -- 실패 NULL

date_format은 위 날짜와 같은 형식으로 줘야 제대로 변형된다.

• 연-월-일 시-분-초 완전한 날짜 및 시간 YYYY-mm-DD HH:MM:SS
• 연-월-일
• 연월일

STR_TO_DATE()

	WHERE str_to_date(sales_ym, '%Y%m') = str_to_date(date_format(now(), '%Y%m'), '%Y%m')

문자열을 date 타입의 데이터로 변경한다. 그러나 이미 sales_ym은 %Y%m 형식의 문자열이고, date_format(now(), ‘%Y%m’)도 %Y%m 형식의 문자열로 변경하기 때문에 위 같은 경우에는 굳이 이를 다시 date로 변경할 필요가 없다.

만약 date가 string 보다 비교 속도가 월등하게 빠르지 않다면 의미 없는 행동이다.

• 문자열로 저장된 sales_ym을 date로 변경한다.
• now()를 YYYYMM로 가공하여 이를 date_format()으로 문자열로 만든다.

select str_to_date('201201', '%Y%m%D') as d1; -- '2012-01-00'
select str_to_date('20120110', '%Y%m%D') as d1; -- '2012-01-10'
select str_to_date('20120101', '%Y%m') as d1; -- '2012-01-00'
select str_to_date('201201', '%Y%m') as d1; -- '2012-01-00'

위처럼 str_to_date는 인자로 일자가 들어오지 않아도 무조건 연-월-일 형식으로 만들기 때문에 주의해야 한다. 심지어 %Y%m 형식 제어자를 사용해도 일자를 00으로 뱉어내는 것을 보고 사용에 굉장히 힘들 거라 생각했다.

DATE_ADD()

select
	date_add('20241010', interval -1 month) AS d1 -- 2024-09-10
	
select
	date_add('2024-10-10 10:10:10', interval -1 month) AS d1 -- '2024-09-10 10:10:10'

select
	date_add('20241010 10:10:10', interval -1 month) AS d1 -- 실패 NULL

시간, 분, 초를 포함하려면 YYYY-mm-dd 형식을 완전히 지켜야 한다.