MySQL TIME stamp to days, hours

Question

In looking at the MySQL documentation, I am not finding an option under DATE_FORMAT (referenced by TIME_FORMAT) for converting a MySQL TIME value (ex: 172:04:11) to something like, "7 days, 4 hours".

Is there a way (in MySQL) to format time in this manner? Or should I just operate on the returned TIME value in PHP?

Answer 1

I hate having to answer my own question, but to clear things up MySQL's TIME() function truncates values over 838:59:59 - obviously not desirable.

The solution I found was to use this:

TIMESTAMPDIFF(SECOND,date_old,date_newer) 

Which does not have the same truncation problem. Once you have the seconds use some server side scripting to format the time as in my original request. Reply if you want to see an example in PHP, or write your own. :)

Hope this is helpful?