MySQL convert timediff output to day, hour, minute, second format

by 
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

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.

More Related Contents:

  1. How to create a MySQL hierarchical recursive query?
  2. MySQL Error 1215: Cannot add foreign key constraint
  3. Authentication plugin ‘caching_sha2_password’ cannot be loaded
  4. How to grant remote access permissions to mysql server for user?
  5. mySQL convert varchar to date
  6. Why is the estimated rows count very different in phpmyadmin results?
  7. Slow query when using ORDER BY
  8. How to import XML file into MySQL database table using XML_LOAD(); function
  9. MySQL stored procedure vs function, which would I use when?
  10. How to convert time to the time zone of the iPhone device?
  11. MySQL: Sort GROUP_CONCAT values
  12. Having both a Created and Last Updated timestamp columns in MySQL 4.0
  13. How can I set a maximum number of rows in MySQL table?
  14. Dynamic table names in stored procedure function
  15. How to alter a column and change the default value?
  16. Which is faster — INSTR or LIKE?
  17. Alter MySQL table to add comments on columns
  18. Remove duplicate rows leaving oldest row Only?
  19. Show constraints on tables command
  20. How do I show the schema of a table in a MySQL database?
  21. How big can a MySQL database get before performance starts to degrade
  22. Using index, using temporary, using filesort – how to fix this?
  23. How to get the max of two values in MySQL?
  24. How to make a primary key start from 1000?
  25. MySQL – SELECT * INTO OUTFILE LOCAL ?
  26. SQL (MySQL) vs NoSQL (CouchDB) [closed]
  27. MySQL GROUP BY age range including null ranges
  28. Best tool for synchronizing MySQL databases [closed]
  29. Failed building wheel for mysql-python
  30. How to convert a varchar column type to date type without losing the dates

Leave a Comment