Convert TIMEDIFF to hours plus minutes

Question

time1: 2013-08-26 16:33:00  
time2: 2013-08-26 15:10:00

$query="UPDATE `FlightSchedule` 
SET delay = MINUTE(TIMEDIFF(time1, time2)) 
WHERE `flightNum_arr`='".$flightNum_arr."';";

It saves the value 23 as the delay. Instead the correct answer should be 83 minutes. How to get it?

Answer 1

I think you are looking for:

$query="UPDATE `FlightSchedule` 
SET delay = CEIL((UNIX_TIMESTAMP(time1) - UNIX_TIMESTAMP(time2))/60)
WHERE `flightNum_arr`='".$flightNum_arr."';";

Alternatively, there is TIME_TO_SEC function - and, since it provides result in seconds, you'll need to divide it to 60 too.

Answer 2

It returns 1 hour 23 min. You have to get hour.

Following codes return 4980 sec. And you have to divide 60 for minutes.

You can use TIME_TO_SEC

TIME_TO_SEC(TIMEDIFF(time1, time2)) / 60