How to Find the Number of Days Between Two Dates in MySQL

Problem

You’d like to get the difference, in days, between two dates in a MySQL database.

Example

Our database has a table named food with data in the columns id, name, purchase_date, and expiration_date.

For each food product, let’s get the name of the product and the number of days between its expiration and purchase dates.

Solution

We’ll use the DATEDIFF() function. Here’s the query you would write:

SELECT
  name,
  DATEDIFF(expiration_date, purchase_date) AS days
FROM food;

Here’s the result of the query:

Discussion

Use the DATEDIFF() function to retrieve the number of days between two dates in a MySQL database. This function takes two arguments:

1. The end date. (In our example, it’s the expiration_date column.)
2. The start date. (In our example, it’s the purchase_date column.)

These arguments can be date/datetime values, expressions that return date/datetime values, or columns of the datetime or date data type.

This function subtracts the start date from the end date and returns the number of days as an integer. In our example, butter was purchased on '2018-07-30', but its expiration date was '2019-08-10'; after purchase, it could be eaten during 376 days. Notice that yogurt was purchased as an outdated product: the difference in days is -1 and its purchase date is later than its expiration date.