Articles Cookbook
Back to list
MySQL

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

Database:

Operators:

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
1bread2019-07-202019-08-22
2butter2018-07-302019-08-10
3milk2019-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:

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

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.

Recommended courses:

Recommended articles:

See also:

go to top