MySQL中的DATEDIFF无法正常工作

Question

I am trying to calculate difference between two dates in days, here are the two dates:

$date_1 = "2017-06-12 13:44:58";
$date_2 = "2017-05-04 19:06:24";

When I do it through PHP script it gives 38 as result, which is correct.

$datediff = strtotime($date_1) - strtotime($date_2);
echo floor($datediff / (60 * 60 * 24));

Result: 38 ----> Correct
Because there is still difference in hours of the given dates

But when I do the same in MySQL, then it gives 39 as result, which is wrong.

select DATEDIFF("2017-06-12 13:44:58","2017-05-04 19:06:24")

Result: 39 ----> Incorrect

Answer 1

From MySQL help:

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.

You can use TIMESTAMPDIFF instead:

select ABS(TIMESTAMPDIFF(DAY,"2017-06-12 13:44:58","2017-05-04 19:06:24"));
+---------------------------------------------------------------------+
| ABS(TIMESTAMPDIFF(DAY,"2017-06-12 13:44:58","2017-05-04 19:06:24")) |
+---------------------------------------------------------------------+
|                                                                  38 |
+---------------------------------------------------------------------+