CODEWITHSUNDEEP
mysqldatediff
El Dude
El Dude

Reputation: 5618

MySQL datediff strange results

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 |
+-------------------------------------------------------+

Upvotes: 2

Views: 566

Answers (3)

droben
droben

Reputation: 89

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 :)

Upvotes: 0

lshas
lshas

Reputation: 1731

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 |
+--------------------------------------------------------------------+

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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)

Upvotes: 2

Related Questions