How to Use DATEDIFF in MySQL
DATEDIFF is MySQL's workhorse function for calculating the difference between two dates. It returns an integer representing the number of days between two date values, making it essential for...
Key Insights
- DATEDIFF returns the number of days between two dates as an integer, calculated as date1 minus date2, making date order critical for correct results
- The function ignores time components entirely, treating all dates as midnight values, which can cause unexpected results with DATETIME columns
- Using DATEDIFF in WHERE clauses prevents index usage—filter by date ranges directly instead for better performance
Introduction to DATEDIFF
DATEDIFF is MySQL’s workhorse function for calculating the difference between two dates. It returns an integer representing the number of days between two date values, making it essential for everything from calculating subscription durations to identifying overdue invoices.
The syntax is straightforward:
DATEDIFF(date1, date2)
The function subtracts date2 from date1 and returns the result in days. If date1 is later than date2, you get a positive number. If date1 is earlier, you get a negative number.
Here’s a simple example:
SELECT DATEDIFF('2024-03-15', '2024-03-01') AS days_difference;
-- Returns: 14
SELECT DATEDIFF('2024-03-01', '2024-03-15') AS days_difference;
-- Returns: -14
You’ll reach for DATEDIFF whenever you need to quantify time spans in days: tracking how long customers have been active, calculating delivery times, finding overdue items, or determining age-based eligibility. It’s faster and more reliable than trying to calculate date differences manually.
Basic DATEDIFF Usage
The return value is always an integer. No decimals, no fractions—just whole days. This simplicity is both DATEDIFF’s strength and its limitation.
Date order matters significantly. The calculation is always date1 - date2, so reversing your arguments reverses the sign of your result. This becomes important when you’re building queries that need to handle both past and future dates.
-- Calculate shipping time for orders
SELECT
order_id,
order_date,
ship_date,
DATEDIFF(ship_date, order_date) AS days_to_ship
FROM orders
WHERE ship_date IS NOT NULL;
This query shows how many days elapsed between ordering and shipping. Positive numbers indicate normal processing; negative numbers would indicate data errors where the ship date precedes the order date.
Here’s a practical example for finding record age:
-- Find how old each user account is
SELECT
user_id,
username,
created_at,
DATEDIFF(CURDATE(), created_at) AS account_age_days
FROM users
ORDER BY account_age_days DESC;
Using CURDATE() as the first argument ensures you always get positive numbers representing days since creation. If you need to highlight recent signups, reverse the order and filter for negative values approaching zero.
DATEDIFF with Different Date Formats
MySQL has three main temporal types: DATE, DATETIME, and TIMESTAMP. DATEDIFF accepts all of them, but here’s the crucial detail: it strips away all time information before calculating.
-- Both of these return 0, not 1
SELECT DATEDIFF('2024-03-15 23:59:59', '2024-03-15 00:00:01');
SELECT DATEDIFF('2024-03-15 14:30:00', '2024-03-15 09:15:00');
DATEDIFF treats every datetime as midnight on that date. For many use cases, this is exactly what you want—you’re interested in calendar days, not precise durations. But it can surprise you if you’re not expecting it.
When working with DATETIME columns where the time component matters for other parts of your query, you can be explicit:
-- Calculate days between datetime values
SELECT
event_id,
start_datetime,
end_datetime,
DATEDIFF(end_datetime, start_datetime) AS duration_days,
-- Compare with extracting dates first
DATEDIFF(DATE(end_datetime), DATE(start_datetime)) AS explicit_days
FROM events;
Both approaches yield identical results, but the explicit version makes your intent clearer to other developers.
The NOW() and CURDATE() functions behave differently with DATEDIFF:
-- These might return different results depending on current time
SELECT
DATEDIFF(NOW(), '2024-03-01') AS with_now,
DATEDIFF(CURDATE(), '2024-03-01') AS with_curdate;
Both return the same value because DATEDIFF ignores the time component from NOW(). But using CURDATE() is more explicit about your intent and marginally faster since MySQL doesn’t need to fetch the current time.
Common Use Cases and Practical Applications
Subscription duration tracking is a classic DATEDIFF application:
-- Calculate active subscription days
SELECT
subscription_id,
user_id,
start_date,
CASE
WHEN end_date IS NULL THEN DATEDIFF(CURDATE(), start_date)
ELSE DATEDIFF(end_date, start_date)
END AS subscription_days
FROM subscriptions;
This pattern handles both active subscriptions (NULL end_date) and completed ones.
Finding overdue items is another prime use case:
-- Find invoices overdue by more than 30 days
SELECT
invoice_id,
customer_id,
due_date,
DATEDIFF(CURDATE(), due_date) AS days_overdue,
amount
FROM invoices
WHERE
status = 'unpaid'
AND DATEDIFF(CURDATE(), due_date) > 30
ORDER BY days_overdue DESC;
This query identifies problem accounts that need immediate attention. The WHERE clause filters for significantly overdue invoices, not just those a day or two late.
For customer tenure analysis:
-- Segment customers by tenure
SELECT
customer_id,
first_purchase_date,
DATEDIFF(CURDATE(), first_purchase_date) AS tenure_days,
CASE
WHEN DATEDIFF(CURDATE(), first_purchase_date) < 30 THEN 'New'
WHEN DATEDIFF(CURDATE(), first_purchase_date) < 365 THEN 'Regular'
ELSE 'Loyal'
END AS customer_segment
FROM customers
WHERE first_purchase_date IS NOT NULL;
This segments customers based on how long they’ve been buying from you, enabling targeted marketing campaigns.
DATEDIFF vs Other Date Functions
DATEDIFF only returns days. When you need hours, minutes, or seconds, use TIMESTAMPDIFF:
-- Compare DATEDIFF with TIMESTAMPDIFF
SELECT
order_id,
created_at,
completed_at,
DATEDIFF(completed_at, created_at) AS days_difference,
TIMESTAMPDIFF(HOUR, created_at, completed_at) AS hours_difference,
TIMESTAMPDIFF(MINUTE, created_at, completed_at) AS minutes_difference
FROM orders
WHERE completed_at IS NOT NULL
LIMIT 5;
TIMESTAMPDIFF takes a unit as its first argument (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR), giving you precise control over granularity.
For converting DATEDIFF results to weeks or months:
-- Convert days to weeks and approximate months
SELECT
subscription_id,
start_date,
end_date,
DATEDIFF(end_date, start_date) AS total_days,
FLOOR(DATEDIFF(end_date, start_date) / 7) AS total_weeks,
FLOOR(DATEDIFF(end_date, start_date) / 30) AS approx_months,
TIMESTAMPDIFF(MONTH, start_date, end_date) AS exact_months
FROM subscriptions
WHERE end_date IS NOT NULL;
Note that dividing DATEDIFF by 30 gives you approximate months. TIMESTAMPDIFF with MONTH accounts for varying month lengths and is more accurate for month calculations.
DATE_ADD and DATE_SUB are for date arithmetic, not comparison:
-- These are different operations
SELECT
DATEDIFF(CURDATE(), '2024-01-01') AS days_since,
DATE_ADD('2024-01-01', INTERVAL 90 DAY) AS ninety_days_later,
DATE_SUB(CURDATE(), INTERVAL 30 DAY) AS thirty_days_ago;
Use DATE_ADD/DATE_SUB when you need to calculate a new date. Use DATEDIFF when you need to measure the span between existing dates.
Common Pitfalls and Best Practices
NULL handling is critical. If either argument to DATEDIFF is NULL, the result is NULL:
-- Safely handle NULL dates
SELECT
order_id,
order_date,
ship_date,
COALESCE(DATEDIFF(ship_date, order_date), 0) AS days_to_ship,
IFNULL(DATEDIFF(CURDATE(), ship_date), 999) AS days_since_ship
FROM orders;
Choose your default value based on your business logic. Zero might indicate “not yet shipped” while 999 could flag items needing investigation.
Performance becomes an issue when you use DATEDIFF in WHERE clauses on large tables:
-- Slow - prevents index usage
SELECT * FROM orders
WHERE DATEDIFF(CURDATE(), order_date) <= 30;
-- Fast - uses index on order_date
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
The first query requires MySQL to calculate DATEDIFF for every row before filtering. The second query lets MySQL use an index on order_date to quickly find relevant rows. This difference matters enormously on tables with millions of rows.
When you must use DATEDIFF for complex logic, do the heavy filtering first:
-- Optimized approach
SELECT
order_id,
order_date,
DATEDIFF(CURDATE(), order_date) AS age_days
FROM orders
WHERE
order_date >= '2024-01-01' -- Index-friendly filter first
AND order_date <= CURDATE()
AND DATEDIFF(CURDATE(), order_date) % 7 = 0 -- Complex logic second
Timezone awareness: DATEDIFF doesn’t handle timezones. If your application spans multiple timezones, convert everything to UTC before comparison:
-- Ensure consistent timezone handling
SELECT
DATEDIFF(
CONVERT_TZ(end_time, 'America/New_York', 'UTC'),
CONVERT_TZ(start_time, 'America/New_York', 'UTC')
) AS days_difference
FROM events;
DATEDIFF is simple, fast, and reliable for day-based date arithmetic. Master these patterns and you’ll handle the vast majority of date comparison scenarios efficiently. Just remember: it only counts days, it ignores time components, and it should rarely appear in WHERE clauses on large tables.