mysql 日期格式化、近三天、上一个月、近三年

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')  //2023-02-17 09:29:36

近三天

BETWEEN DATE_SUB(NOW(), INTERVAL 3 DAY) AND NOW();

上一个月

date_format( PAY_TIME, '%Y-%m' ) = date_format( SUBDATE(#{data}, INTERVAL 1 MONTH ), '%Y-%m' )

近三年

DATE_FORMAT( INSERT_TIME, '%Y' ) BETWEEN YEAR (NOW()) - 2 AND YEAR (NOW())

SELECT NOW()

SELECT DAYOFWEEK(NOW()) //6  从周日开始为1

SELECT date_format( SUBDATE(NOW(), INTERVAL 1 MONTH ), '%Y-%m' )  //2023-01

SELECT CURRENT_DATE // 2023-02-17

SELECT DATE_ADD(CURRENT_DATE,INTERVAL 2 DAY) AS 后天;  //2023-02-19
SELECT DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY) AS 昨天; //2023-02-16
SELECT DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY) AS 昨天;  //2023-02-16


SELECT DATEDIFF(CURRENT_DATE,'2023-02-15') AS 天数; //2

​SELECT TIMESTAMPDIFF(DAY, CURRENT_DATE,CURRENT_DATE) AS 天数;
SELECT TIMESTAMPDIFF(MONTH, '2023-01-05', CURRENT_DATE) AS 月份;

SELECT TIMESTAMPDIFF( SECOND, '2023-02-17 10:01:09', NOW());  //108秒

　TIMESTAMPDIFF   第一个参数，单位unit

 

 

相信上面的已经可以举一反三解决问题了