How to Convert Time Difference Output to Day, Hour, Minute, and Second Format in MySQL?

Converting Time Difference Output to Day, Hour, Minute, and Second Format in MySQL

When working with time differences in MySQL, it's often useful to display the result in a more human-readable format, such as days, hours, minutes, and seconds. This can be challenging if the time difference is large.

Suppose you have a query like:

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

which returns a value like:

116:12:10

representing 116 hours, 12 minutes, and 10 seconds. To convert this to a more readable format, use the following query:

SELECT CONCAT(
FLOOR(HOUR(TIMEDIFF(end_time,start_time)) / 24), ' days ',
MOD(HOUR(TIMEDIFF(end_time,start_time)), 24), ' hours ',
MINUTE(TIMEDIFF(end_time,start_time)), ' minutes')

Replace end_time and start_time with your actual column names.

For example, let's say end_time is '2010-01-06 08:46' and start_time is '2010-01-01 12:30'. The above query would return:

4 days 20 hours 12 minutes

Note that this solution only works for date differences within 35 days. If you know there are more than 35 days between start_time and end_time, consider using TIMESTAMPDIFF instead.

The above is the detailed content of How to Convert Time Difference Output to Day, Hour, Minute, and Second Format in MySQL?. For more information, please follow other related articles on the PHP Chinese website!