4
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?

Klausos Klausos
  • 15,308
  • 51
  • 135
  • 217
  • 1
    do the same to the `hours` as well, multiply the `hour` diff by `60` and add it to your `delay`... – Joum Aug 26 '13 at 14:01
  • 1
    this might also help: http://stackoverflow.com/questions/2174058/mysql-timediff-to-hours – Joum Aug 26 '13 at 14:02
  • I really hope that `$flightNum_arr` is [properly escaped](http://bobby-tables.com/) because when you see things like this, using string concatenation to compose queries, virtually every time it isn't. – tadman Aug 26 '13 at 14:16

2 Answers2

4

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.

Alma Do
  • 37,009
  • 9
  • 76
  • 105
4

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
Bora
  • 10,529
  • 5
  • 43
  • 73