mysql日期相关函数使用

最近要经常用到mysql的日期相关函数，但以前用的少，索性总结下

1 DATE_FORMAT(date,format)

published_at字段以datetime类型存储

SELECT title,DATE_FORMAT(published_at,'%Y-%m-%d') FROM blog_note WHERE DATE_FORMAT(published_at,'%Y-%m')='2023-09' 

  2 STR_TO_DATE(str,format)

SELECT title,STR_TO_DATE(published_at,'%Y-%m-%d')  FROM blog_note 

 3 UNIX_TIMESTAMP(date)

SELECT title,UNIX_TIMESTAMP(published_at) ,published_at FROM blog_note 

 4 FROM_UNIXTIME(timestamp,format)

SELECT title,FROM_UNIXTIME(UNIX_TIMESTAMP(published_at) ,'%Y-%m-%d %H:%i:%s'),published_at FROM blog_note 

 5 CURRENT_DATE()

SELECT CURRENT_DATE(),DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY) ,DATE_ADD(CURRENT_DATE(),INTERVAL -1 DAY) ,DATE_SUB(CURRENT_DATE(),INTERVAL -1 DAY)

SELECT DATE_ADD(CURRENT_DATE(),INTERVAL 1 MONTH) ,DATE_ADD('2023-12-01',INTERVAL 1 DAY) 
,DATE_ADD(NOW(),INTERVAL '1::20:30' HOUR_SECOND),DATE_ADD(NOW(),INTERVAL '1::20' YEAR_MONTH)