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
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.
for getting Diffrence in Hour :
Hour(TIMEDIFF(date1,date2)) as Hour1
for getting Diffrence in Minute :
Minute(TIMEDIFF(date1,date2)) as Minute1
for getting Diffrence in Second :
Second(TIMEDIFF(date1,date2)) as Second1
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 mysql funcion timestampdiff like below
TIMESTAMPDIFF(HOUR, '2018-06-01 00:00:00', '2018-06-01 12:00:00')