MYSQL 常用时间语句

查询 【今天】
SELECT * FROM table WHERE TO_DAYS(NOW()) = TO_DAYS(create_time);


查询 【昨天】
SELECT * FROM table WHERE TO_DAYS(NOW( )) - TO_DAYS(create_time) <= 1;


查询 【上一周】 YEARWEEK第二个参数可选，默认是0:即从上周天开始
SELECT * FROM table WHERE YEARWEEK(create_time) = YEARWEEK(CURDATE()) - 1;
查询 【上一周】 规定从上周一开始
SELECT * FROM table WHERE YEARWEEK(create_time, 1) = YEARWEEK(CURDATE(), 1) - 1;


查询 【本周】
SELECT * FROM table WHERE YEARWEEK(create_time) = YEARWEEK(CURDATE());


查询 【7天内】
SELECT * FROM table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time);


查询 【近30天】
SELECT * FROM table where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time);



查询 【本月】
SELECT * FROM table WHERE DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')


查询 【上一月】
SELECT * FROM table
WHERE YEAR(create_time) = YEAR(CURDATE() - INTERVAL 1 MONTH)
  AND MONTH(create_time) = MONTH(CURDATE() - INTERVAL 1 MONTH);


SELECT * FROM table
WHERE create_time BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND LAST_DAY(CURDATE() - INTERVAL 1 MONTH);

SELECT * FROM table
WHERE PERIOD_DIFF(DATE_FORMAT(CURDATE(), '%Y%m'), DATE_FORMAT(create_time, '%Y%m')) = 1



  



#查询本季度数据
SELECT * FROM table WHERE QUARTER(create_time) = QUARTER(NOW());

#查询上季度数据
SELECT * FROM table WHERE QUARTER(create_time) = QUARTER(DATE_SUB(NOW(), INTERVAL 1 QUARTER));

#查询本年数据
SELECT * FROM table WHERE YEAR(create_time) = YEAR(NOW());

#查询上年数据
SELECT * FROM table WHERE YEAR(create_time) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR));