How to Calculate the Difference Between Two Dates in MySQL

Problem

You have two columns of the date type and you want to calculate the difference between them.

Example

In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between arrival and departure, or the number of days from arrival to departure inclusively.

The travel table looks like this:

Solution

SELECT
  id,
  departure,
  arrival,
  DATEDIFF(arrival, departure) AS date_difference,
  DATEDIFF(arrival, departure) + 1 AS days_inclusive
FROM travel;

The result is:

Discussion

To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days. In this case, the enddate is arrival and the startdate is departure.

In most cases, though, what you really want is the number of days from the first date to the second date inclusively. You need to add 1 day to the difference: DATEDIFF(arrival, departure) + 1.