How to get the number of days of difference between two dates on mysql?
Note if you want to count FULL 24h days between 2 dates, datediff can return wrong values for you.
As documentation states:
Only the date parts of the values are used in the calculation.
which results in
select datediff('2016-04-14 11:59:00', '2016-04-13 12:00:00')
returns 1 instead of expected 0.
Solution is using select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00');
(note the opposite order of arguments compared to datediff).
Some examples:
select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00');
returns 0select timestampdiff(DAY, '2016-04-13 11:00:00', '2016-04-14 11:00:00');
returns 1select timestampdiff(DAY, '2016-04-13 11:00:00', now());
returns how many full 24h days has passed since 2016-04-13 11:00:00 until now.
Hope it will help someone, because at first it isn't much obvious why datediff returns values which seems to be unexpected or wrong.
I prefer TIMESTAMPDIFF because you can easily change the unit if need be.
What about the DATEDIFF function ?
Quoting the manual's page :
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
In your case, you'd use :
mysql> select datediff('2010-04-15', '2010-04-12');
+--------------------------------------+
| datediff('2010-04-15', '2010-04-12') |
+--------------------------------------+
| 3 |
+--------------------------------------+
1 row in set (0,00 sec)
But note the dates should be written as YYYY-MM-DD
, and not DD-MM-YYYY
like you posted.
Use the DATEDIFF()
function.
Example from documentation:
SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1