MySQL comes with dozens of date and time functions. It can already handle calculations at the database level and you may filter the result based on the number of days between two dates. MySQL’s integrated DATEDIFF date function handles that calculation for you.

This tutorial shows you how to retrieve the days between two dates.

MySQL Series Overview

Use DATEDIFF to Get the Days Between Dates

Use MySQL’s DATEDIFF function to calculate the number of days from one date to another. MySQL calculates the result by running expr1 - expr2. The DATEDIFF method signature looks like this:

SELECT DATEDIFF(expr1, expr2)  

The expr1 and expr2 values are date or date time values. If one of the provided values is NULL, the result is NULL, too.

Here are more examples of using the DATEDIFF function:

SELECT DATEDIFF("2024-08-31", "2024-08-01")  
-- 30

SELECT DATEDIFF(NULL, "2024-08-01")  
-- NULL

SELECT DATEDIFF("2024-08-31", NULL)  
-- NULL

Example

Let’s say we have a user_trials table with entries like the ones below. We’re storing a user ID and the related dates for the trial’s start and end:

| userid | trialstartson | trial_endson | |---------|----------------|--------------| | 1 | 2024-08-01 | 2024-08-31 | | 2 | 2024-07-22 | 2027-07-29 |

You can retrieve the number of the trial’s duration in days using the MySQL DATEDIFF function:

SELECT  
    user_id,
    trial_startson,
    trial_endson,
    DATEDIFF(trial_endson, trial_startson) as days_difference,
    DATEDIFF(trial_endson, trial_startson) + 1 as days_inclusive,
FROM user_trials  

The query result is this:

| userid | trialstartson | trialendson | daysdifference | days_inclusive | |---------|----------------|--------------|-----------------|----------------| | 1 | 2024-08-01 | 2024-08-31 | 30 | 31 | | 2 | 2024-07-22 | 2027-07-29 | 7 | 8 |

Notice the days_inclusive column that adds one day to the calculation. Depending on your use case, you may need to include the first date. Don’t add the extra day to exclude the first date.

That’s it!

---

Mentioned Resources

• MySQL docs for the DATEDIFF function