mysql 按照天，小时，分钟等分组排序

按照季度统计

SELECT CONCAT(DATE_FORMAT(create_time, '%Y'),'-',FLOOR((DATE_FORMAT(create_time, '%m')+2)/3),'季度') AS QUARTER ,
COUNT(1) AS 总量 FROM 表 WHERE
create_time>'2021-01-01' AND create_time<'2022-01-01'
GROUP BY QUARTER
ORDER BY QUARTER;

 

按月统计

SELECT DATE_FORMAT(create_time,'%Y-%m') months,COUNT(1) COUNT
FROM 表
WHERE create_time>'2021-01-01' AND create_time<'2021-06-01'
GROUP BY months
ORDER BY months;

 

按周统计

SELECT DATE_FORMAT(create_time,'%Y%u') weeks,COUNT(1)COUNT FROM t_product_spu
WHERE create_time>'2021-01-01' AND create_time<'2021-03-26'
GROUP BY weeks
ORDER BY weeks;

 

按照天统计

SELECT DATE(create_time) AS DATE,COUNT(*)  FROM  表
WHERE create_time<'2021-01-10'
GROUP BY DATE
ORDER BY DATE;

按照小时统计

SELECT DATE_FORMAT(create_time,'%Y-%m-%d %H:00:00') AS TIME,COUNT(*) FROM 表
WHERE create_time>'2021-03-25' AND create_time<'2021-03-26'
GROUP BY TIME
ORDER BY TIME;

按照每半个小时统计分组

SELECT TIME, COUNT(*) AS num
FROM
(
SELECT id,
DATE_FORMAT(
CONCAT( DATE( create_time ), ' ', HOUR ( create_time ), ':', FLOOR( MINUTE ( create_time ) / 30 ) * 30 ),
'%Y-%m-%d %H:%i'
) AS TIME
FROM 表
WHERE create_time>'2021-03-25' AND create_time<'2021-03-26'
) a
GROUP BY TIME
ORDER BY TIME;

 

 

指定分钟内的统计分组

SET @min=20; -- 以每20分钟味分组的查询为例
SELECT TIME, COUNT( * ) AS num
FROM
(
SELECT
DATE_FORMAT(
CONCAT( DATE( create_time ), ' ', HOUR ( create_time ), ':', FLOOR( MINUTE ( create_time ) / @min ) * @min ),
'%Y-%m-%d %H:%i'
) AS TIME
FROM 表
WHERE create_time>'2021-05-12' AND create_time<'2021-05-13'
) a
GROUP BY TIME
ORDER BY TIME;

按照分钟统计

SELECT DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:00') AS TIME, COUNT(*) AS num
FROM 表
WHERE create_time>'2021-05-12 09:40' AND create_time<'2021-05-12 09:50'
GROUP BY TIME
ORDER BY TIME;