mysql按时间区间进行统计以及DATE_FORMAT的使用

#时间分组 小时

SELECT COUNT(1), HOUR(k.create_date ) FROM (SELECT * FROM `t_inspection_plan` p where '2019-12-18 02:15:46' < p.create_date And  p.create_date   < '2019-12-18 16:15:46' ORDER BY p.create_date DESc) k GROUP BY HOUR(k.create_date )

#按年月 分组

SELECT DATE_FORMAT(p.create_date,'%Y-%m') from  t_inspection_plan p  GROUP BY DATE_FORMAT(p.create_date,'%Y-%m')

#按天分组

SELECT  DATE_FORMAT(p.create_date,'%d') FROM t_inspection_plan p  
WHERE   '2019-12-18 02:15:46' <= p.create_date <= '2019-12-18 16:15:46'
GROUP BY DATE_FORMAT(p.create_date,'%d')