How to get the number of days of difference between two dates on MySQL?

I need to get the n开发者_如何转开发umber of days contained within a couple of dates on MySQL.

For example:

• Check in date is 12-04-2010
• Check out date 15-04-2010

The day difference would be 3.

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.

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 0
• select timestampdiff(DAY, '2016-04-13 11:00:00', '2016-04-14 11:00:00'); returns 1
• select 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.

Use the DATEDIFF() function.

Example from documentation:

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
    -> 1

I prefer TIMESTAMPDIFF because you can easily change the unit if need be.

Get days between Current date to destination Date

 SELECT DATEDIFF('2019-04-12', CURDATE()) AS days;

output

days

 335

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

If you want a more accurate value than just the rounded up number of days:

select timestampdiff(minute, min(date_col), max(date_col))/1440 from table

    TIMESTAMPDIFF(DAY,STR_TO_DATE(date_format('2018-01-01', '%d-%m-%YYYY'),'%d-%m-%YYYY'), '2020-01-01') TOTAL_DAYS

TOTAL_DAYS: 730 days