-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
  • 18,826
  • 5
  • 45
  • 66
Klausos Klausos
  • 14,142
  • 48
  • 129
  • 212
  • 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
  • 26,601
  • 7
  • 57
  • 86
1

DATEDIFF will give result in days.

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

An alternative for hour difference.

milesh
  • 520
  • 3
  • 8
  • 19
0

Use TIMEDIFF function

SELECT TIMEDIFF(ETA,STA) FROM `Schedule` WHERE `Num`="5567";
Madhivanan
  • 13,244
  • 1
  • 23
  • 28
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,165
  • 3
  • 39
  • 64
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,129
  • 1
  • 11
  • 25