MySQL查询当天、本周、本月、本季度、本年的数据

查询今天的数据

SELECT check_type,create_time FROM attendance WHERE to_days(create_time) = to_days(now());

查询昨天的数据

SELECT check_type,create_time FROM attendance WHERE to_days(now()) - to_days(create_time) = 1;

查询昨天和今天的数据

SELECT check_type,create_time FROM attendance WHERE to_days(now()) - to_days(create_time) <= 1;

查询本周的数据（本周默认从周日开始）

SELECT check_type,create_time FROM attendance WHERE YEARWEEK(create_time) = YEARWEEK(now()) ;

查询本周的数据（本周从周一开始）

SELECT check_type,create_time FROM attendance WHERE YEARWEEK(create_time,1) = YEARWEEK(now(),1) ;

查询上周的数据

SELECT check_type,create_time FROM attendance WHERE YEARWEEK(create_time) = YEARWEEK(now()) - 1 ;

查询近7天的数据

SELECT check_type,create_time FROM attendance WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time);

查询本月的数据

SELECT check_type,create_time FROM attendance WHERE DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );

查询上个月的数据

SELECT check_type,create_time FROM attendance WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( create_time, '%Y%m' ) ) = 1;

查询近30天的数据

SELECT check_type,create_time FROM attendance WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time);

查询本季度的数据

SELECT check_type,user_check_time FROM attendance WHERE QUARTER(user_check_time)=QUARTER(now());

查询上季度的数据

SELECT check_type,user_check_time FROM attendance WHERE QUARTER(user_check_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

查询本年的数据

SELECT check_type,user_check_time FROM attendance WHERE YEAR(user_check_time)=YEAR(NOW());

查询上年的数据

SELECT check_type,user_check_time FROM attendance WHERE year(user_check_time)=year(date_sub(now(),interval 1 year));

查询距离现在6个月的数据

SELECT check_type,user_check_time FROM attendance WHERE user_check_time between date_sub(now(),interval 6 month) and now();