I prefer TIMESTAMPDIFF because you can easily change the unit if need be.
Use the DATEDIFF()
function.
Example from documentation:
SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1
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.
SELECT md.*, DATEDIFF(md.end_date, md.start_date) AS days FROM membership_dates md
output::
id entity_id start_date end_date days
1 1236 2018-01-16 00:00:00 2018-08-31 00:00:00 227
2 2876 2015-06-26 00:00:00 2019-06-30 00:00:00 1465
3 3880 1990-06-05 00:00:00 2018-07-04 00:00:00 10256
4 3882 1993-07-05 00:00:00 2018-07-04 00:00:00 9130
hope it helps someone in future
Get days between Current date to destination Date
SELECT DATEDIFF('2019-04-12', CURDATE()) AS days;
output
335
Source: Stackoverflow.com