MySQL常用SQL时间查询语句

1.查询当天SELECT * FROM article WHERE to_days(add_time) = to_days(now());2.查询昨天SELECT * FROM article WHERE to_days(now()) – to_days(add_time) = 1;3.查询最近7天SELECT * FROM article WHERE date_sub(curdate(), INTERVAL 7 DAY) <= DATE(add_time);//ORSELECT * FROM article WHERE curdate()- INTERVAL 7 DAY <= DATE(add_time);4.查询最近30天SELECT * FROM article WHERE date_sub(curdate(), INTERVAL 30 DAY) <= DATE(add_time);//ORSELECT * FROM article WHERE curdate()-INTERVAL 30 DAY <= DATE(add_time);5.查询截止到当前本周SELECT * FROM article WHERE YEARWEEK(date_format(add_time,’%Y-%m-%d’)) = YEARWEEK(now());#默认从周日开始到周六SELECT * FROM article WHERE YEARWEEK(date_format(add_time,’%Y-%m-%d’),1) = YEARWEEK(now(),1);#设置为从周一开始到周日6.查询上周的数据SELECT * FROM article WHERE YEARWEEK(date_format(add_time,’%Y-%m-%d’)) = YEARWEEK(now())-1;7.查询截止到当前本月SELECT * FROM article WHERE date_format(add_time, ‘%Y%m’) = date_format(curdate() , ‘%Y%m’);8.查询上一月SELECT * FROM article WHERE period_diff(date_format(now() , ‘%Y%m’) , date_format(add_time, ‘%Y%m’)) =1;SELECT * FROM ke_order_list WHERE add_time BETWEEN ‘2019-03-01’ AND ‘2019-04-01’;SELECT * FROM ke_order_list WHERE add_time LIKE ‘2019-03%’