How to Convert TIMEDIFF Output into Day, Hour, Minute, and Second Format in MySQL?

Transforming TIMEDIFF Output to Human-Readable Day, Hour, Minute, and Second Format in MySQL

Facing the challenge of converting the output of TIMEDIFF into a more user-friendly format that displays the time difference in terms of days, hours, minutes, and seconds, numerous MySQL users have sought a solution. To address this issue, let's delve into the provided query:

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

This query effortlessly subtracts start_time from end_time, yielding the total time difference as the "total" column. However, the output is presented in a raw format, making it cumbersome to interpret at a glance.

To rectify this, the suggested approach leverages the CONCAT function to stitch together the constituent parts of the time difference into a human-readable string. The following SQL statement accomplishes this transformation:

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 the placeholder end_time and start_time values with the corresponding column names in your metrics table.

This solution adeptly extracts hours from the TIMEDIFF result and converts them to days if necessary. It then appends hours and minutes to form the desired output. Note that this approach is suitable for time differences within a span of 35 days. For scenarios involving more extended periods, alternative solutions using TIMESTAMPDIFF may be more appropriate.

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