Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert TIMEDIFF to hours plus minutes

Tags:

php

mysql

minute

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?

like image 425
Klausos Klausos Avatar asked Feb 15 '23 11:02

Klausos Klausos


2 Answers

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.

like image 68
Alma Do Avatar answered Feb 24 '23 12:02

Alma Do


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
like image 33
Bora Avatar answered Feb 24 '23 12:02

Bora