We help IT Professionals succeed at work.

Convert mysql date to days hours minutes seconds

revo1059
revo1059 asked
on
I have this query:

SELECT timediff(stop_time,start_time) as total from `work`;
which gives me this

239:53:36

where 239 is hours, 53 is minutes and 36 is seconds.

Instead, I want it to 9 days 5 hours, 53 minutes etc

I could either do this from mysql or php, though i would prefer to do it from mysql.

I have mysql version 5.0.11 running on suse sles 9
Comment
Watch Question

This is how you would do it in PHP...

<?php
$total="239:35:36";
list($hours,$minutes,$seconds)=explode(":",$total);
echo floor($hours/24)." days ".($hours%24)." minutes ".$seconds." seconds.";
?>
whoops, this should be right
<?php
$total="239:35:36";
list($hours,$minutes,$seconds)=explode(":",$total);
echo floor($hours/24)." days ".($hours%24)." hours ".$minutes." minutes ".$seconds." seconds.";
?>

Author

Commented:
Any idea how to do it in mysql?  I have played with all the date functions, but can never seem to get it quite right.
Hope this works for you:
SELECT concat( truncate( hour( timediff(stop_time,start_time) ) /24, 0 ) , " days ", hour( timediff(stop_time,start_time) ) %24, " hours ", minute( timediff(stop_time,start_time) ) , " minutes ", second( timediff(stop_time,start_time) ) , " seconds" ) as total from `work`;

Open in new window

Author

Commented:
That is exactly perfect.  Thank you so much.

Author

Commented:
sorry for such a long delay.  I didn't realize that I had not accepted the answer