CODEWITHSUNDEEP
mysql
Alfons
Alfons

Reputation: 331

MySQL TIMEDIFF negative value

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

Upvotes: 3

Views: 6484

Answers (4)

developerCK
developerCK

Reputation: 4506

Don't convert your time stamp to using Time, just put your values like

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

.Check Further from Mysql Function MysqlDate Time Function

B/c Time will extract only time from timestamp. so actually you are doing.

TIMEDIFF('00:04:50','23:59:59');

Upvotes: 2

Abhishek Jain
Abhishek Jain

Reputation: 2607

Try this:

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

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

Upvotes: 0

Jim
Jim

Reputation: 22656

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

Upvotes: 5

Alma Do
Alma Do

Reputation: 37365

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.

Upvotes: 3

Related Questions