mysql timediff to hours

Question

I'm Trying to get the timediff from my table and convert it to hours (it's for an hourly billed service)

SELECT TIME_TO_SEC(TIMEDIFF(endDate,startDate))/3600 FROM tasks >

where endDate and startDate are in datetime format

is there another way (more efficient) to do this task? Thanks !

Answer 1

TIMEDIFF(endDate, startDate) outputs in DateTime format, so flat that to timestamp and devide by (60*60)

SELECT (UNIX_TIMESTAMP(TIMEDIFF(endDate, startDate))/(60*60)) AS hours_difference FROM tasks 

Edit: Alternatively,TimestampDiff may also provide a valid solution in more elegant way providing its example:

SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); 

And your solution can be:

SELECT TIMESTAMPDIFF(HOUR, startDate, endDate) AS hours_different FROM tasks 

Answer 2

NOTE the most voted up answer in this chain is INCORRECT! Using HOUR will only return hours as an integer. Below would correct the most popular answer to return hours as an integer and minutes as a decimal (ie 6.5 hours).

TIME_TO_SEC(TIMEDIFF(endDate, startDate))/3600 as hours