[mysql] 날짜 연산

 

now(), curdate() 차이

select
    now(),     /* 2023-11-13 22:26:51.000 */
    curdate()  /* 2023-11-13 */
;

 

 

이번주 첫째날, 마지막 날

이번달 첫째날, 마지막 날

올해  첫째날, 마지막 날

-- day, 오늘 또는 특정일 하루
select
	curdate(),
	date_add(curdate(), interval 1 day)
;

select
	str_to_date('20240201', '%Y%m%d'),
	date_add(str_to_date('20240201', '%Y%m%d'), interval 1 day)
;


-- week, 이번주 첫째날, 마지막 
select
    DATE_ADD(curdate(), interval (1 - DAYOFWEEK(curdate())) day) as this_week_first, 
    DATE_ADD(curdate(), interval (7 - DAYOFWEEK(curdate())) day) as this_week_last
;

-- month, 이번달 첫째날, 마지막날
select
    last_day(date_add(curdate(), interval -1 month)) + interval 1 day as this_month_first,
    last_day(curdate()) as this_month_last
;

-- year, 올해 첫째날, 마지막날
select
    str_to_date(concat(year(curdate()), '0101'), '%Y%m%d') as this_year_first,
    str_to_date(concat(year(curdate()), '1231'), '%Y%m%d') as this_year_last
;

 

 

날짜 포멧팅

select
    now() as col_now,
    STR_TO_DATE('2024-01-31 12:34:56.123456', '%Y-%m-%d %H:%i:%s.%f') as col_STR_TO_DATE,
    DATE_FORMAT(STR_TO_DATE('2024-01-31 12:34:56.123456', '%Y-%m-%d %H:%i:%s.%f'), '%Y-%m-%d %H:%i:%s.%f') as col_DATE_FORMAT 
;

%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%m	Month, numeric (00..12)
%d	Day of the month, numeric (00..31)

%H	Hour (00..23)
%i	Minutes, numeric (00..59)
%s	Seconds (00..59)
%f	Microseconds (000000..999999)

 

 

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions