mysql timediff to hours

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? 

 Answers

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, endDate, startDate) AS hours_different
FROM tasks

---

HOUR(TIMEDIFF(endDate, startDate))

might work—if I'm reading the docs correctly.

---

eg: startDate 2010-01-31 00:00:00, endDate 2010-01-31 19:24:22

SELECT (UNIX_TIMESTAMP(dateFin)-UNIX_TIMESTAMP(dateDebut))/3600 hour_diff
FROM tasks

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

returns 19.4061 which is good

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

Only returns hours while i also need minutes to be converted.

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

returns 0. I think the first one is the most efficent. Thanks !!

---

You can use UNIX_TIMESTAMP to do the calculation in SELECT query.

SELECT (UNIX_TIMESTAMP(endDate)-UNIX_TIMESTAMP(startDate))/3600 hour_diff
  FROM tasks

UNIX_TIMESTAMP convert datetime to number of second from epoch. You can substract both timestamp to get difference in second. Divide it with 3600 will give you difference in hour.

---

TIMEDIFF(endDate, startDate) / 10000