• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7886
  • Last Modified:

Convert mysql date to days hours minutes seconds

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
0
revo1059
Asked:
revo1059
  • 3
  • 3
1 Solution
 
darron_chapmanCommented:
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.";
?>
0
 
darron_chapmanCommented:
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.";
?>
0
 
revo1059Author 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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
darron_chapmanCommented:
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

0
 
revo1059Author Commented:
That is exactly perfect.  Thank you so much.
0
 
revo1059Author Commented:
sorry for such a long delay.  I didn't realize that I had not accepted the answer
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now