MySQL将timediff输出转换为日，时，分，秒格式 [英] MySQL convert timediff output to day, hour, minute, second format

问题描述

这是我的查询:

SELECT TIMEDIFF(end_time,start_time) AS "total" FROM `metrics`;

这给了我

116:12:10

表示116小时12分10秒.

meaning 116 hours, 12 minutes and 10 seconds.

我想说的是4天20小时12分钟等

Instead, I want it to say 4 days 20 hours, 12 minutes etc

推荐答案

SELECT CONCAT(
FLOOR(HOUR(TIMEDIFF('2010-01-06 08:46', '2010-01-01 12:30')) / 24), ' days ',
MOD(HOUR(TIMEDIFF('2010-01-06 08:46', '2010-01-01 12:30')), 24), ' hours ',
MINUTE(TIMEDIFF('2010-01-06 08:46', '2010-01-01 12:30')), ' minutes')

在我的示例中，将end_time和start_time用于固定的datetime值

Use your end_time and start_time for the fixed datetime values in my example

根据以下两条评论，该解决方案仅适用于35天内的日期差异.如果您知道开始到结束之间有35天以上的时间，即一个月内有差异，请不要使用它.在这里使用TIMESTAMPDIFF的其他答案将起作用.

As per the two comments below, this solution only works for date differences within 35 days. If you know there are more than 35 days between start and end, i.e. differences over a month, don't use it. Other answers here using TIMESTAMPDIFF will work.

这篇关于MySQL将timediff输出转换为日，时，分，秒格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！