如何使用SQL计算h:mm:ss格式时长的平均值？

核心逻辑 #

直接对时间格式做算术平均会出现逻辑错误，正确思路是先把字符串格式的时长转换成数值型总秒数，计算平均值后再转回h:mm:ss格式。

MySQL实现示例 #

假设数据存储在表time_records中，字段duration为h:mm:ss格式的字符串：

1. 单步完成计算

   SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(duration))) AS average_duration
   FROM time_records;
   

   针对你给出的示例数据，该语句会直接返回1:30:00。

2. 分步拆解理解

   • 转成秒数：用TIME_TO_SEC()将每个时长转为总秒数，比如TIME_TO_SEC('3:00:00')得到10800
   • 计算平均：对秒数取平均值，示例中(10800+1800+3600)/3 = 5400秒
   • 转回时间格式：用SEC_TO_TIME(5400)得到1:30:00

SQL Server实现示例 #

SQL Server使用不同函数，但逻辑一致：

SELECT CONVERT(VARCHAR, DATEADD(SECOND, AVG(DATEDIFF(SECOND, '00:00:00', duration)), '00:00:00'), 108) AS average_duration
FROM time_records;

• DATEDIFF(SECOND, '00:00:00', duration)：将时长转为总秒数
• AVG()计算平均秒数
• DATEADD+CONVERT：把平均秒数转回h:mm:ss格式

特殊场景处理 #

如果时长超过24小时，MySQL的SEC_TO_TIME()会返回25:30:00这类格式；若需强制仅显示小时数（不拆分天），可手动拼接格式：

SELECT 
  CONCAT(
    FLOOR(avg_seconds / 3600), ':',
    LPAD(FLOOR((avg_seconds % 3600) / 60), 2, '0'), ':',
    LPAD(avg_seconds % 60, 2, '0')
  ) AS average_duration
FROM (
  SELECT AVG(TIME_TO_SEC(duration)) AS avg_seconds
  FROM time_records
) AS temp;

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