183

I need to get the number 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.

informatik01
  • 16,038
  • 10
  • 74
  • 104
Audel
  • 2,479
  • 5
  • 24
  • 20

8 Answers8

300

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.

Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
53

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.

Konrad Gałęzowski
  • 1,761
  • 1
  • 20
  • 29
19

Use the DATEDIFF() function.

Example from documentation:

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
    -> 1
Tobias Cohen
  • 19,893
  • 7
  • 54
  • 51
8

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

theblang
  • 10,215
  • 9
  • 69
  • 120
6

Get days between Current date to destination Date

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

output

days

 335
vijayabalan
  • 91
  • 1
  • 3
2
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
Sabito stands with Ukraine
  • 4,271
  • 8
  • 34
  • 56
Developer
  • 3,857
  • 4
  • 37
  • 47
0

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
jrm
  • 599
  • 6
  • 12
0
    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

Mohammad Ali Abdullah
  • 321
  • 2
  • 10