一、统计方法:
按月统计(一)
select date_format(create_time, '%Y-%m') mont, count(*) coun
from t_content
group by date_format(create_time, '%Y-%m');
按天统计(二)
select date_format(create_time, '%Y-%m-%d') dat, count(*) coun
from t_content
group by date_format(create_time, '%Y-%m-%d');
按天统计(三)
select from_unixtime(create_time / 1000, '%Y-%m-%d') dat, count(*) coun
from t_content
group by from_unixtime(create_time / 1000, '%Y-%m-%d')
格式转换:
select from_unixtime(create_time / 1000, '%Y-%m-%d %H:%i:%S') create_time
from t_content
二、示例
建表语句
CREATE TABLE `test_counts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`channel` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '' ,
`create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
`error_number` int(11) NOT NULL DEFAULT '0' COMMENT '错误次数',
`hour` int(11) NOT NULL DEFAULT '0' COMMENT '小时',
`request_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '请求时间',
`success_number` int(11) NOT NULL DEFAULT '0' COMMENT '成功次数',
`uuid` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '0' COMMENT 'uuid',
PRIMARY KEY (`id`),
KEY `ix_request_date` (`request_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='服务成功失败次数';
查询:
select channel,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s')create_time,error_number,hour,request_date,success_number
FROM test_counts
where channel='suggest'
and request_date BETWEEN '2021-04-22' and '2021-04-23';