mysql timediff to hours

Question

Hi, 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

HOUR(TIMEDIFF(endDate, startDate))

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

Answer 2

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.

Answer 3

TIMEDIFF(endDate, startDate) / 10000

Answer 4

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