MySQL常⽤SQL时间查询语句
⽐较实⽤的时期查询SQL语句。假设数据库表中时间字段为add_time，类型为datetime。
1.查询当天常用的sql查询语句有哪些
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`);
//OR
SELECT * 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`);
//OR
SELECT * 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');
5.查询上⼀⽉
//经过测试，下⾯第⼀种情况查询速度最快，explain之后filtered为100%
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%'

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。