How to Calculate Date Difference in MySQL

How do I calculate the number of days between two dates in MySQL?

DATEDIFF() returns the number of whole days between two DATE or DATETIME values.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How does MySQL calculate date difference?

Use DATEDIFF(date1, date2). MySQL subtracts date2 from date1 and returns the signed count of days. Positive means date1 is later.

What is the correct DATEDIFF syntax?

Syntax is simple: DATEDIFF(expr1, expr2). Both expressions must resolve to DATE, DATETIME, or TIMESTAMP. The function ignores any time component.

Which data types work with DATEDIFF?

Use DATE, DATETIME, TIMESTAMP columns, or string literals convertible to dates (e.g., '2024-01-15'). Mixing types is allowed but can slow queries through implicit casts.

How do I measure days between signup and first order?

Join Customers and Orders, take the earliest order_date, then apply DATEDIFF against created_at.

SELECT c.id, c.name, MIN(o.order_date) AS first_order_date, DATEDIFF(MIN(o.order_date), c.created_at) AS days_to_first_order FROM Customers c JOIN Orders o ON o.customer_id = c.id GROUP BY c.id, c.name;

What best practices improve accuracy and speed?

Store dates in UTC, index date columns used in filters, and always place the later date first to avoid negative results unless that sign matters.

What mistakes should I avoid?

Argument order errors flip the sign. Relying on VARCHAR dates forces conversions and blocks index use, slowing scans.

Why How to Calculate Date Difference in MySQL is important

How to Calculate Date Difference in MySQL Example Usage


/* Days between purchase and product restock */
SELECT p.id,
       p.name,
       r.restock_date,
       o.order_date,
       DATEDIFF(r.restock_date, o.order_date) AS days_until_restock
FROM Products p
JOIN OrderItems oi  ON oi.product_id = p.id
JOIN Orders     o   ON o.id = oi.order_id
JOIN (
    SELECT product_id, MIN(created_at) AS restock_date
    FROM ProductRestocks
    GROUP BY product_id
) r ON r.product_id = p.id;

How to Calculate Date Difference in MySQL Syntax


DATEDIFF(expr1, expr2)

expr1  – First DATE/DATETIME/TIMESTAMP or string literal. Treated as the later date in a typical usage.
expr2  – Second DATE/DATETIME/TIMESTAMP or string literal.
Return – INT signed day count (expr1 – expr2).

Example (ecommerce):
SELECT DATEDIFF(o.order_date, c.created_at) AS days_gap
FROM Customers c
JOIN Orders o ON o.customer_id = c.id;

Common Mistakes

Supplying arguments in the wrong order: DATEDIFF(signup, first_order) returns a negative value. Place the later date first or wrap the result in ABS() if only the magnitude matters.
Using VARCHAR columns that hold date strings. MySQL must convert each row, preventing index use and causing full table scans. Convert columns to DATE or DATETIME.