MySQL时间转换操作

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);