MySQL中将小时数转换为hh:mm格式并去除前导零的问题

问题说明 #

现有用户工作时长数据，存储为小时格式，统计后需要转换为hh:mm格式。原转换方法出现了多余的前导零00:和秒数，无法得到预期的简洁格式。

原统计查询（获取小时数） #

select d.email,TRUNCATE(sum(e.hours),2) as "working_hours"
from users d inner join proj_time e on d.id=e.user_id
WHERE start_time BETWEEN CURDATE() - INTERVAL 1 DAY
AND CURDATE() - INTERVAL 1 SECOND
group by d.id,d.email

错误转换尝试及结果 #

误用hours * 60转换为秒数（实际应乘以3600），导致SEC_TO_TIME返回00:HH:MM.SS格式，不符合需求：

select d.email,SEC_TO_TIME(TRUNCATE(sum(e.hours * 60),2)) as "working_hours"
from users d inner join proj_time e on d.id=e.user_id
WHERE start_time BETWEEN CURDATE() - INTERVAL 1 DAY
AND CURDATE() - INTERVAL 1 SECOND
group by d.id,d.email

两种可行解决方案 #

方案1：直接计算小时+分钟拼接 #

通过数学运算拆分小时和分钟，格式化后拼接成目标格式：

SELECT 
    d.email,
    CONCAT(
        LPAD(FLOOR(SUM(e.hours)), 2, '0'),
        ':',
        LPAD(FLOOR((SUM(e.hours) - FLOOR(SUM(e.hours))) * 60), 2, '0')
    ) AS working_hours
FROM users d 
INNER JOIN proj_time e ON d.id = e.user_id
WHERE start_time BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND
GROUP BY d.id, d.email;

• FLOOR(SUM(e.hours))：提取总时长的整数小时部分
• (SUM(e.hours) - FLOOR(SUM(e.hours))) * 60：计算小数部分对应的分钟数并取整
• LPAD(..., 2, '0')：确保小时、分钟为两位数，不足补零
• CONCAT：拼接成hh:mm格式

方案2：时间函数处理后截取有效部分 #

先将小时数转换为秒数，生成标准时间格式后截取所需部分：

SELECT 
    d.email,
    SUBSTRING(SEC_TO_TIME(TRUNCATE(SUM(e.hours) * 3600, 0)), 1, 5) AS working_hours
FROM users d 
INNER JOIN proj_time e ON d.id = e.user_id
WHERE start_time BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND
GROUP BY d.id, d.email;

• SUM(e.hours) * 3600：将总小时数转换为秒数（1小时=3600秒）
• TRUNCATE(..., 0)：截断秒数的小数部分，避免生成秒的小数位
• SEC_TO_TIME：将秒数转换为HH:MM:SS格式
• SUBSTRING(..., 1, 5)：截取前5个字符，得到hh:mm格式

两种方案均可输出符合预期的hh:mm格式，无多余前导零和秒数。

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