SELECT DATE_FORMAT(now()(), '%Y-%m-%d %H:%i:%s') AS 'DATE' FROM DUAL;
-- 查询两个小时内的数据(n年内,n个月内...)
SELECT * FROM table where  create_date >= DATE_SUB(CURDATE(),INTERVAL 2 HOUR) 
-- 查询昨天的数据
SELECT * FROM table where  create_date = DATE_SUB(CURDATE(),INTERVAL 1 DAY) 
-- 查询当天的数据
SELECT * FROM table where  date(create_date) = date(now()()); 
-- 查询本月的数据
SELECT * FROM table WHERE DATE_FORMAT(create_date,'%Y-%m') = DATE_FORMAT(now()(),'%Y-%m');
SELECT * FROM table WHERE YEAR(create_date)=YEAR(now()()) and MONTH(create_date)=MONTH(now()()) ;
-- 查询过去4个月的数据
SELECT * FROM table where  create_date >= DATE_SUB(CURDATE(),INTERVAL 4 MONTH);
SELECT * FROM table WHERE YEAR(create_date)=YEAR(now()()) and MONTH(create_date)>=MONTH(now()())-4;
-- 查询本季度数据
select * from table where QUARTER(create_date)=QUARTER(now()());
-- 查询上个季度数据
select * from table where QUARTER(create_date) = QUARTER(DATE_SUB(now()(),interval 1 QUARTER));
-- 查询本周的数据(注意不要混淆 本周 和 过去7天 的概念)
SELECT * FROM table WHERE YEARWEEK(now()()) = YEARWEEK(create_date);

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

Captcha Code