MySQL常用SQL时间查询语句

本篇文章向大家介绍《MySQL常用SQL时间查询语句》，主要包括MySQL，具有一定的参考价值，需要的朋友可以参考一下。

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) 

4.查询最近30天

SELECT * FROM `article` WHERE date_sub(curdate(), INTERVAL 30 DAY) 

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%'

本篇关于《MySQL常用SQL时间查询语句》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于数据库的相关知识，请关注golang学习网公众号！