MySQL datediff strange results

Question

I am getting weird results for the datediff function. It's zero for different and same timestamps?

select datediff('2015-04-25 20:37:45','2015-04-25 05:20:00');
+-------------------------------------------------------+
| datediff('2015-04-25 20:37:45','2015-04-25 05:20:00') |
+-------------------------------------------------------+
|                                                     0 |
+-------------------------------------------------------+

select datediff('2015-04-25 20:37:45','2015-04-25 20:37:45');
+-------------------------------------------------------+
| datediff('2015-04-25 20:37:45','2015-04-25 20:37:45') |
+-------------------------------------------------------+
|                                                     0 |
+-------------------------------------------------------+

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 — lshas, Apr 25 '15 at 18:43
thanks, what would be the alternative then? I need the time difference in minutes — El Dude, Apr 25 '15 at 18:43

score 2 · Answer 1 · answered Apr 25 '15 at 18:46

datediff will do the calculation on the dates and find the difference in days, however you can use timestampdiff for different difference like hour,min etc as

mysql> select timestampdiff(hour,'2015-04-25 05:20:00','2015-04-25 20:37:45') as hour;
+------+
| hour |
+------+
|   15 |
+------+
1 row in set (0.00 sec)

lshas · Accepted Answer · 2015-04-25T18:50:31.433

If you look at the MySQL Manual:

DATEDIFF
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

So what you are getting is correct.

If you are looking for time difference, I would suggest looking into TIMEDIFF or TIMESTAMPDIFF.

If you would like to get the difference in minutes, the best way would be TIMESTAMPDIFF.

Here is an example of how you could do it:

SELECT TIMESTAMPDIFF(MINUTE, '2015-04-25 20:37:45','2015-04-25 05:20:00');
+--------------------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE, '2015-04-25 20:37:45','2015-04-25 05:20:00') |
+--------------------------------------------------------------------+
|                                                               -917 |
+--------------------------------------------------------------------+

score 0 · Answer 3 · edited May 23 '17 at 10:28

you should use TIMEDIFF() function instead of DATEDIFF if you want to compare two timestamps with difference lower than day. For example:

select TIMEDIFF('2015-04-25 20:37:45','2015-04-25 05:20:00');

or for result in sec:

select TIME_TO_SEC(TIMEDIFF('2015-04-25 20:37:45','2015-04-25 05:20:00'));

For more details:

MySQL: how to get the difference between two timestamps in seconds

hope this helps :)

MySQL datediff strange results

3 Answers3