How to Convert MySQL TIMEDIFF Results to Day, Hour, Minute, and Second Format?

Converting MySQL TIMEDIFF Output to Day, Hour, Minute, and Second Format

Problem:

When calculating the time difference between two datetimes using TIMEDIFF(end_time, start_time) in MySQL, it returns the result in a format like "116:12:10", indicating hours, minutes, and seconds. However, the desired format is "4 days 20 hours, 12 minutes, etc."

Solution:

To achieve the desired format, you can use the following query:

<code class="sql">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')</code>

Example Usage:

Replace end_time and start_time with your specific datetimes:

<code class="sql">SELECT CONCAT(
FLOOR(HOUR(TIMEDIFF('2023-03-08 12:34', '2023-03-01 16:45')) / 24), ' days ',
MOD(HOUR(TIMEDIFF('2023-03-08 12:34', '2023-03-01 16:45')), 24), ' hours ',
MINUTE(TIMEDIFF('2023-03-08 12:34', '2023-03-01 16:45')), ' minutes')</code>

Note:

This solution is only suitable for time differences within 35 days. If you anticipate time differences greater than 35 days, consider using TIMESTAMPDIFF instead.

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