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

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

When querying the time difference between two DATETIME values in MySQL, the TIMEDIFF function typically returns a value in the format of hours:minutes:seconds. However, if you desire a more comprehensive representation that includes days and seconds, you can utilize a custom formula.

To format the TIMEDIFF output as days, hours, minutes, and seconds, you can employ 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',
  SECOND(TIMEDIFF(end_time, start_time)), ' seconds'
)</code>

Replace end_time and start_time with the corresponding columns or values representing your specific time range.

For instance, if you have the following values:

start_time = '2010-01-01 12:30'
end_time = '2010-01-06 08:46'

Executing the query will produce the following output:

4 days 20 hours 12 minutes 10 seconds

It's important to note that this solution effectively handles date differences within a 35-day range. If you anticipate larger date differences exceeding 35 days, it's advisable to consider alternative solutions utilizing the TIMESTAMPDIFF function.

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