-1
SELECT ETA,STA FROM `Schedule` WHERE `Num`="5567";

2013-08-26 18:37:00 2013-08-26 18:30:00

SELECT DATEDIFF(ETA,STA) FROM `Schedule` WHERE `Num`="5567";

0

Why the result is 0 instead of 7 (i.e. minutes)?

Andy G
  • 19,232
  • 5
  • 47
  • 69
Klausos Klausos
  • 15,308
  • 51
  • 135
  • 217
  • The [manual of MySQL and DATEDIFF says it returns the difference in days](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff)... – Bobby Aug 26 '13 at 11:19

5 Answers5

1

You can do this using TIME_TO_SEC MySQL function.

Sample

SELECT (TIME_TO_SEC(ETA) - TIME_TO_SEC(STA))/60 AS `minutes`
Dipesh Parmar
  • 27,090
  • 8
  • 61
  • 90
1

DATEDIFF will give result in days.

SELECT HOUR (ETA - STA) FROM `Schedule` WHERE `Num`="5567";

An alternative for hour difference.

milesh
  • 570
  • 3
  • 8
  • 19
0

Use TIMEDIFF function

SELECT TIMEDIFF(ETA,STA) FROM `Schedule` WHERE `Num`="5567";
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
0

Refer to this thread on how to calculate the difference in munites: Calculating time difference between 2 dates in minutes

DATEDIFF will return the difference in days!

From the docs: DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
    -> 1
Community
  • 1
  • 1
bpgergo
  • 15,669
  • 5
  • 44
  • 68
0

SELECT (TIMEDIFF(ETA,STA)) FROM SCHEDULE will give it in formatted minutes and return 00:07:00

SELECT TIME_TO_MIN(TIMEDIFF(ETA,STA)) FROM SCHEDULE gives 420

(7 * 60 seconds = 420)

KevinY
  • 1,229
  • 1
  • 12
  • 27