【MySQL】用时间戳按时间（年月日）分组统计

小溪小西

于 2022-09-14 16:56:44 发布

阅读量1.8k

点赞数

文章标签：时间戳时间格式 SQL查询精确搜索按月统计

本文链接：https://blog.csdn.net/Yusian_/article/details/126854786

版权

时间戳格式与时间格式的转化
（转为时间戳格式时，单位默认为秒，若需转为以毫秒单位，则结果*1000）

SELECT UNIX_TIMESTAMP('2022-09-01 00:00:00'); 
SELECT FROM_UNIXTIME('1661961600','%Y-%m-%d %H:%i:%s');

精确时间范围的搜索

SELECT * FROM table_name WHERE 
source_date>=UNIX_TIMESTAMP('2022-08-01 08:02:43')*1000 
and source_date<UNIX_TIMESTAMP('2022-08-02 00:00:30')*1000;

按月统计数量

SELECT FROM_UNIXTIME(stamp_time/1000,'%Y-%m') months,COUNT(*) FROM
table_name GROUP BY months;

mouths	COUNT(*)
2022-09	123
2022-08	467
2022-07	566

按月拼统计数量+拼接表

SELECT t1.months,t1.count1,t2.count2 FROM 
(SELECT FROM_UNIXTIME(stamp_time/1000,'%Y-%m') months,count(*) as count1 FROM table_name WHERE col_name!=0 GROUP BY months) t1
 inner join
(SELECT FROM_UNIXTIME(stamp_time/1000,'%Y-%m') months,count(*) as count2 FROM table_name WHERE col_name=1 GROUP BY months) t2
 on t1.months=t2.months;