MySQL 将 timediff 输出转换为日、时、分、秒格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5279079/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL convert timediff output to day, hour, minute, second format
提问by user656079
This is my query:
这是我的查询：
SELECT TIMEDIFF(end_time,start_time) AS "total" FROM `metrics`;
which gives me:
这给了我：
116:12:10
meaning 116 hours, 12 minutes and 10 seconds.
表示 116 小时 12 分 10 秒。
Instead, I want it to say 4 days 20 hours, 12 minutes etc
相反，我希望它说 4 天 20 小时 12 分钟等
回答by RichardTheKiwi
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')
Use your end_time and start_time for the fixed datetime values in my example
在我的示例中使用 end_time 和 start_time 作为固定日期时间值
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.
根据下面的两条评论，此解决方案仅适用于 35 天内的日期差异。如果您知道开始和结束之间的时间超过 35 天，即超过一个月的差异，请不要使用它。此处使用 TIMESTAMPDIFF 的其他答案将起作用。
回答by borre
SELECT
CONCAT(
TIMESTAMPDIFF(day,'2001-01-01 00:00:00','2001-01-02 23:10:00') , ' dagen ',
MOD( TIMESTAMPDIFF(hour,'2001-01-01 00:00:00','2001-01-02 23:10:00'), 24), ' uren ',
MOD( TIMESTAMPDIFF(minute,'2001-01-01 00:00:00','2001-01-02 23:10:00'), 60), ' minuten '
)
回答by McAngujo
Try
尝试
SELECT @s:='2016-03-01' started, @e:= '2018-04-21' ended,
FLOOR((@ms:=TIMESTAMPDIFF(MONTH,@s,@e))/12) yrs, (@ms%12) mns,
@d:=TIMESTAMPDIFF(DAY,@s:=DATE_ADD(@s,INTERVAL @ms MONTH), @e) dys,
@hr:=TIMESTAMPDIFF(HOUR,@s:=DATE_ADD(@s, INTERVAL @d DAY),@e) hrs,
TIMESTAMPDIFF(MINUTE,TIMESTAMPADD(HOUR,@hr,@s),@e) mins;
This will give you a breakdown from year, month, day, hour and minutes between two dates.
这将为您提供两个日期之间的年、月、日、小时和分钟细分。
Hope it assists someone.
希望它可以帮助某人。
回答by Bogdans
The easiest way to do this is
最简单的方法是
CONCAT(
FLOOR(TIMESTAMPDIFF(SECOND, startDate, endDate) / 86400), ' days ',
FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600), ' hours ',
FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60), ' minutes ',
(TIMESTAMPDIFF(SECOND, startDate, endDate) % 60), ' seconds'
)
To show only relevant information you'd need to do a more complex version
要仅显示相关信息，您需要做一个更复杂的版本
IF(
FLOOR(TIMESTAMPDIFF(SECOND, startDate, endDate) / 86400) = 0,
IF(
FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600) = 0,
IF(
FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60) = 0,
CONCAT((TIMESTAMPDIFF(SECOND, startDate, endDate) % 60), ' seconds'),
CONCAT(
FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60), ' minutes ',
(TIMESTAMPDIFF(SECOND, startDate, endDate) % 60), ' seconds'
)
),
CONCAT(
FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600), ' hours ',
FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60), ' minutes ',
(TIMESTAMPDIFF(SECOND, startDate, endDate) % 60), ' seconds'
)
),
CONCAT(
FLOOR(TIMESTAMPDIFF(SECOND, startDate, endDate) / 86400), ' days ',
FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600), ' hours ',
FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60), ' minutes ',
(TIMESTAMPDIFF(SECOND, startDate, endDate) % 60), ' seconds'
)
)
回答by Lalo Zavala
I used the answer given by Bodgans, but I added a condition to insert a 0 before quantities less than 10.
我使用了Bodgans给出的答案，但我添加了一个条件，在数量小于 10 之前插入 0。
CONCAT(
IF(FLOOR(TIMESTAMPDIFF(SECOND, startDate, endDate) / 86400) < 10, '0', ''), FLOOR(TIMESTAMPDIFF(SECOND, startDate, endDate) / 86400), ' days ',
IF(FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600) < 10, '0', ''), FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600), ' hours ',
IF(FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60) < 10, '0', ''), FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60), ' minutes ',
IF((TIMESTAMPDIFF(SECOND, startDate, endDate) % 60) < 10, '0', ''), (TIMESTAMPDIFF(SECOND, startDate, endDate) % 60), ' seconds'
)
In that way, we can get a result as follows
这样，我们可以得到如下结果
09 days 10 hours 06 minutes 30 seconds
09天10小时06分30秒
