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

昨天

SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=-1

今天

select * from 表名 where to_days(时间字段名) = to_days(now());

本周

SELECT * FROM 表名 WHERE YEARWEEK(date_format(字段名,'%Y-%m-%d')) = YEARWEEK(now());

上一周

SELECT * FROM 表名 WHERE YEARWEEK(date_format(字段名,'%Y-%m-%d')) = YEARWEEK(now())-1;

本月

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

本季度

SELECT * FROM 表名 WHERE QUARTER(字段名)=QUARTER(now());

上季度

SELECT * FROM 表名 WHERE QUARTER(字段名)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

本年

SELECT * FROM 表名 WHERE YEAR(字段名)=YEAR(NOW());

上一年

SELECT * FROM 表名 WHERE year(字段名)=year(date_sub(now(),interval 1 year));

最近7天

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

最近30天

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

最近半年

SELECT * FROM 表名 WHERE 字段名 between date_sub(now(),interval 6 month) and now();

具体时间段查询

 SELECT * FROM 表名 WHERE 字段名 between CONCAT(#{beginTime},' 00:00:00') and CONCAT(#{endTime},' 23:59:59')

每天以三个小时为一个时段统计一天中数据

SELECT NAME, IFNULL(count + count1, 0) AS `count`
FROM (
	SELECT t1.NAME, t.count, t1.count1
	FROM (
		SELECT CASE
				WHEN HOUR(时间字段名) BETWEEN 0 AND 3 THEN '00'
				WHEN HOUR(时间字段名) BETWEEN 3 AND 6 THEN '03'
				WHEN HOUR(时间字段名）BETWEEN 6 AND 9 THEN '06' 
				WHEN HOUR(时间字段名) BETWEEN 9 AND 12 THEN '09'
				WHEN HOUR(时间字段名) BETWEEN 12 AND 15 THEN '12'
				WHEN HOUR(时间字段名) BETWEEN 15 AND 18 THEN '15'
				WHEN HOUR(时间字段名) BETWEEN 18 AND 21 THEN '18'
				WHEN HOUR(时间字段名) BETWEEN 21 AND 23 THEN '21'
			END AS `name`, IFNULL(COUNT(1), 0) AS `count`
		FROM 表名
		WHERE 1 = 1
			-- 这里可以添加其他条件 比如 and flag=1 and type = 2 and isdelete = 0
			-- AND to_Days(时间字段名) = to_days(now())  这里不建议这么写，有to_days()这种运算的逻辑可以放到程序中去处理，没必要在数据库中去做运算，我们在开发过程中尽量避免这一点，可以参考下面这种写法，这个时间的处理可以参考我的另外一篇博客 http://www.fujiatian.com/post/24365.html
			and	时间字段名 >= '2020-05-07 00:00:00' 时间字段名 < '2020-05-08 00:00:00'
		GROUP BY CASE
				WHEN HOUR(时间字段名) BETWEEN 0 AND 3 THEN 1
				WHEN HOUR(时间字段名) BETWEEN 3 AND 6 THEN 2
				WHEN HOUR(时间字段名) BETWEEN 6 AND 9 THEN 3
				WHEN HOUR(时间字段名) BETWEEN 9 AND 12 THEN 4
				WHEN HOUR(时间字段名) BETWEEN 12 AND 15 THEN 5
				WHEN HOUR(时间字段名) BETWEEN 15 AND 18 THEN 6
				WHEN HOUR(时间字段名) BETWEEN 18 AND 21 THEN 7
				WHEN HOUR(时间字段名) BETWEEN 21 AND 23 THEN 8
			END
	) t
		RIGHT JOIN (
			SELECT '00' AS NAME, 0 AS count1
			UNION
			SELECT '03' AS NAME, 0 AS count1
			UNION
			SELECT '06' AS NAME, 0 AS count1
			UNION
			SELECT '09' AS NAME, 0 AS count1
			UNION
			SELECT '12' AS NAME, 0 AS count1
			UNION
			SELECT '15' AS NAME, 0 AS count1
			UNION
			SELECT '18' AS NAME, 0 AS count1
			UNION
			SELECT '21' AS NAME, 0 AS count1
		) t1
		ON t.NAME = t1.NAME
) t