Mysql datetime、时间戳按月、周、日分组查询

但是这种方式，一想都觉得太麻烦了，应该是可以通过时间戳转换为月份吧！网络上搜索了资料，mysql确实支持时间戳按月分组查询数据。

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

按月查询

SELECT FROM_UNIXTIME(create_time,’%Y%m’) months,COUNT(id) count FROM  inform where smalltype=’12_1′ GROUP BY months
order by count desc

查询效果：

按周查询

SELECT FROM_UNIXTIME(create_time,’%Y%u’) weeks,COUNT(id) count FROM  where smalltype=’12_1′ GROUP BY weeks order by count desc

按天查询

SELECT FROM_UNIXTIME(create_time,’%Y%m%d’) days,COUNT(id) count FROM  where smalltype=’12_1′ GROUP BY days order by count desc

如果时间字段类型为date或datetime，该如何分组查询呢：

按周查询

SELECT DATE_FORMAT(create_time,’%Y%u’) weeks,COUNT(id) count FROM  inform where smalltype =’12_1′ GROUP BY weeks order by count desc

按天查询
SELECT DATE_FORMAT(created_at,’%Y-%m-%d’) days,COUNT(id) count FROM  inform where smalltype =’12_1′  GROUP BY days order by count desc

按月查询
SELECT DATE_FORMAT(create_time,’%Y%m’) months,COUNT(id) count FROM  inform where smalltype =’12_1′  GROUP BY months order by count desc