MySQL查询今天、昨天、本月、上月等数据

注：数据表中需要一个存储日期时间的字段，本文中create_time存储的数据均为时间戳

查询今天的数据

select * from table_name where TO_DAYS(FROM_UNIXTIME(create_time)) = TO_DAYS(NOW())

查询昨天的数据

SELECT * FROM table_name WHERE TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(create_time)) = 1

查询近7天的数据（包含今天）

SELECT * FROM table_name WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= FROM_UNIXTIME(create_time)

查询本周的数据

SELECT * FROM table_name WHERE YEARWEEK(FROM_UNIXTIME(create_time,'%Y-%m-%d')) = YEARWEEK(NOW());

查询上周的数据

SELECT * FROM table_name WHERE YEARWEEK(FROM_UNIXTIME(create_time,'%Y-%m-%d')) = YEARWEEK(NOW())-1;

查询近30天的数据（包含今天）

SELECT * FROM table_name where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= FROM_UNIXTIME(create_time)

查询本月的数据

SELECT * FROM table_name WHERE FROM_UNIXTIME(create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')

查询上月的数据

SELECT * FROM table_name WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), FROM_UNIXTIME(create_time,'%Y%m')) = 1

查询本季度的数据

SELECT * FROM table_name WHERE QUARTER(FROM_UNIXTIME(create_time))=QUARTER(NOW()) AND YEAR(FROM_UNIXTIME(create_time))=YEAR(NOW())

查询上季度的数据

SELECT * FROM table_name WHERE QUARTER(FROM_UNIXTIME(create_time)) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) AND YEAR(FROM_UNIXTIME(create_time))=YEAR(NOW())

查询今年的数据

SELECT * FROM table_name WHERE YEAR(FROM_UNIXTIME(create_time))=YEAR(NOW())

查询去年的数据

SELECT * FROM table_name WHERE YEAR(FROM_UNIXTIME(create_time)) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))