MySQL 将 timediff 输出转换为日、时、分、秒格式

2021-11-20 00:00:00 mysql

这是我的查询:

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 作为固定日期时间值

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.

相关文章