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? Thanks !

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

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

HOUR(TIMEDIFF(endDate, startDate))

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

TIMESTAMPDIFF(HOUR, startDate, endDate)

is the best way to do it, because it works with big time intervals, like

TIMESTAMPDIFF(HOUR, "2012-01-01 00:00:00", "2050-01-01 00:00:00")

Result: 333120

while

HOUR(TIMEDIFF("2050-01-01 00:00:00", "2012-01-01 00:00:00"))

Result: 838

fails.

As we see in the example above, it surprisingly even works beyond the timestamp limitation in year 2038.

The maximum hours which are returned by HOUR(TIMEDIFF(dateEnd, dateStart)) is 838, because TIMEDIFF is limited to the range allowed for TIME values.

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.