Another MySQL Date Question...

[nigeledge]

I've been trying to figure this out for some time... but I'm not even sure it's possible...

I have two DATETIME columns. I would like to get the difference between the two and then format the result in the following format: H:M P.

Is this possible to do all in a SELECT statement? Many thanks in advance.

[xblue]

if your dates are not before 1970, I'd convert to unix timestamp to get the difference in seconds:

SELECT UNIX_TIMESTAMP(date1)-UNIX_TIMESTAMP(date2) AS time_diff FROM table

and use php to convert to hours, minutes and seconds:

$hours = floor($row['time_diff']/3600);
$minutes = floor(($row['time_diff']%3600)/60);
$seconds = $row['time_diff']%60;

echo $hours.":".$minutes.":".$seconds;

this isn't tested, but hopefully should give you the idea.