mysql按日期年月日分组 mysql按日期分组查询

下面语句主要用于根据数据库字段进行按天、按月、按年分组统计时的查询，仅做个人收藏备用。

1、如果时间字段类型为date或datetime：

 

//按周查询
SELECT DATE_FORMAT(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks;
//按天查询
SELECT DATE_FORMAT(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;
//按月查询
SELECT DATE_FORMAT(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months

2、如果时间字段为时间戳（timestamp）或int表示的时间戳：

//按周查询
SELECT FROM_UNIXTIME(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks;
//按天查询
SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;
//按月查询
SELECT FROM_UNIXTIME(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months