MySQL TIMEDIFF negative value

Question

I've a problem using TIMEDIFF with two different date. The following query "should" return 00:04:51

mysql> SELECT TIMEDIFF(TIME('2013-07-21 00:04:50'),TIME('2013-07-20 23:59:59'));
+-------------------------------------------------------------------+

|TIMEDIFF(TIME('2013-07-21 00:04:50'),TIME('2013-07-20 23:59:59')) |

+-------------------------------------------------------------------+

| -23:55:09                                                         |

+-------------------------------------------------------------------+

1 row in set (0.00 sec)

Any tips? Which is the easiest way? Thank you

Answer 1

If there's no date info then it has to be assumed these values are on the same day. Retain the date info and it will work as expected*:

SELECT TIMEDIFF('2013-07-21 00:04:50','2013-07-20 23:59:59');

*Provided the answer is smaller than TIMEs max of 838:59:59

Answer 2

That is a correct result, because you are casting your dates to only time values (you're stripping meaning part of your operands). If you want to include date influence to the result, you should use DATE_DIFF() function or not to strip your dates by TIME() function.