MySQL中查询年、季度、月、周、天等数据的SQL

表名为log表，时间字段为AddTime 

1、查询当天的数据

SELECT
	* 
FROM
	log 
WHERE
	TO_DAYS( AddTime ) = TO_DAYS(
	NOW());

2、查询本周数据(上周日到本周六)

SELECT
	* 
FROM
	log 
WHERE
	YEARWEEK(
		DATE_FORMAT( AddTime, '%Y-%m-%d' )) = YEARWEEK(
	NOW());

3、 查询近7天的数据,不包括当天

SELECT
	* 
FROM
	log 
WHERE
	TO_DAYS ( NOW( ) ) - TO_DAYS( AddTime ) <= '7' AND TO_DAYS ( NOW( ) ) - TO_DAYS( AddTime ) > 0;

4、 查询上周的数据

SELECT
	* 
FROM
	log 
WHERE
	YEARWEEK(
		date_format( AddTime, '%Y-%m-%d' )) = YEARWEEK(
	now())- 1;
-- 或
SELECT
	* 
FROM
	log 
WHERE
	date_format( AddTime, '%Y-%m' )= date_format( DATE_SUB( curdate(), INTERVAL 1 WEEK ), '%Y-%m' );

5、查询本月的数据

SELECT
	* 
FROM
	log 
WHERE
	DATE_FORMAT( AddTime, '%Y%m' ) = DATE_FORMAT( CURDATE(), '%Y%m' );

6、查询上个月的数据

SELECT
	* 
FROM
	log 
WHERE
	date_format( AddTime, '%Y-%m' )= date_format( DATE_SUB( curdate(), INTERVAL 1 MONTH ), '%Y-%m' );

7、查询近30天的数据

SELECT
	* 
FROM
	log 
WHERE
	DATE_SUB( CURDATE(), INTERVAL 30 DAY ) <= DATE( AddTime );

8、查询本季度的数据

SELECT
	* 
FROM
	log 
WHERE
	QUARTER ( AddTime ) = QUARTER (
	NOW());

9、查询上季度的数据

SELECT
	* 
FROM
	log 
WHERE
	QUARTER ( AddTime ) = QUARTER (
	DATE_SUB( NOW(), INTERVAL 1 QUARTER ));

10、查询距现在6个月的数据

SELECT
	* 
FROM
	log 
WHERE
	AddTime BETWEEN DATE_SUB( NOW(), INTERVAL 6 MONTH ) 
	AND NOW();

11、查询本年的数据

SELECT
	* 
FROM
	log 
WHERE
	YEAR ( AddTime ) = YEAR (
	NOW());

12、查询上年的数据

SELECT
  * 
FROM
  log 
WHERE
  YEAR ( AddTime ) = YEAR (
  DATE_SUB( NOW(), INTERVAL 1 YEAR ));