如何统计MySQL中每日8点至次日8点的计数器读数总和？

要解决这个跨天时间段的统计问题，关键是把每条记录的时间戳映射到对应的统计日期——也就是当日8:00到次日8:00这个周期对应的“日期标识”。我们可以通过调整时间戳的方式来实现这个分组逻辑，具体方案如下：

首先，先确认测试表结构和数据（你提供的建表与插入语句）：

create table Tbl (counterID int, `timestamp` datetime, `counter` int); 
insert into Tbl values (1, '2017-09-18 07:07:03', 22); 
insert into Tbl values (1, '2017-09-18 08:08:03', 25); 
insert into Tbl values (1, '2017-09-18 19:08:33', 30); 
insert into Tbl values (1, '2017-09-19 10:14:54', 8); 
insert into Tbl values (1, '2017-09-19 10:15:24', 13); 
insert into Tbl values (1, '2017-10-20 04:21:08', 5); 
insert into Tbl values (1, '2017-10-23 14:21:38', 24); 
insert into Tbl values (1, '2017-10-23 14:22:08', 72); 
insert into Tbl values (1, '2017-10-23 14:22:38', 86); 
insert into Tbl values (1, '2017-10-24 03:23:09', 100); 
insert into Tbl values (1, '2017-10-24 04:23:38', 120); 
insert into Tbl values (1, '2017-10-24 04:24:08', 125); 
insert into Tbl values (1, '2017-10-25 14:56:52', 2); 
insert into Tbl values (1, '2017-10-25 14:57:22', 8); 
insert into Tbl values (1, '2017-10-25 16:39:22', 21); 
insert into Tbl values (1, '2017-10-25 16:41:52', 22); 
insert into Tbl values (1, '2017-10-25 16:42:22', 23); 
insert into Tbl values (1, '2017-10-25 17:18:13', 26); 
insert into Tbl values (1, '2017-10-25 17:21:15', 17); 
insert into Tbl values (1, '2017-10-25 17:21:46', 19); 
insert into Tbl values (1, '2017-10-25 17:22:46', 41); 
insert into Tbl values (1, '2017-10-26 08:41:58', 2); 
insert into Tbl values (1, '2017-10-26 14:02:28', 5); 
insert into Tbl values (1, '2017-10-30 13:39:20', 1); 
insert into Tbl values (1, '2017-10-30 13:40:19', 4); 

解决方案SQL #

SELECT
    counterID,
    DATE(DATE_SUB(`timestamp`, INTERVAL 8 HOUR)) AS stat_date,
    SUM(`counter`) AS total_counter
FROM Tbl
GROUP BY counterID, stat_date
ORDER BY stat_date;

逻辑解释 #

• 时间映射：DATE_SUB(timestamp, INTERVAL 8 HOUR) 把每条记录的时间戳往前推8小时。这样：
  • 原时间在8:00及之后的记录，调整后日期不变，对应统计周期为「当日8:00到次日8:00」；
  • 原时间在8:00之前的记录，调整后日期变为前一天，对应统计周期为「前一天8:00到当日8:00」。
• 分组求和：按counterID和调整后的日期（stat_date）分组，对counter字段求和，得到每个计数器在对应统计周期内的总读数。
• 排序：最后按stat_date排序，方便查看连续日期的统计结果。

测试结果验证 #

比如测试数据中的几条关键记录：

• 2017-09-18 07:07:03 调整后日期为2017-09-17，属于17日8:00到18日8:00的周期，总和为22；
• 2017-09-18 08:08:03和2017-09-18 19:08:33调整后日期为2017-09-18，总和为25+30=55；
• 2017-10-24 03:23:09调整后日期为2017-10-23，和10-23的三条记录总和为24+72+86+100+120+125=527。

内容的提问来源于stack exchange，提问作者Taha