Back to cookbooks list Articles Cookbook

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

  • DATEDIFF()

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.

idnamepurchase_dateexpiration_date
1bread 2019-07-202019-08-22
2butter2018-07-302019-08-10
3milk 2019-01-122019-01-13
4yogurt2019-02-252019-02-24

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:

namedays
bread 33
butter376
milk 1
yogurt-1

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.)
Discover the best interactive MySQL courses

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.

Recommended courses:

Recommended articles:

See also: