Introduction

Subtracting 30 days from the current date and time in MySQL is a standard date-arithmetic task. The usual answer is DATE_SUB(NOW(), INTERVAL 30 DAY), but it is also useful to understand how the choice between NOW(), CURDATE(), and predicate shape affects the result.

Use DATE_SUB with NOW()

If you want the current timestamp minus 30 days, use NOW() together with DATE_SUB.

SELECT DATE_SUB(NOW(), INTERVAL 30 DAY) AS thirty_days_ago;

NOW() returns the current date and time, so the result keeps both parts.

You can write the same calculation with interval syntax as well:

SELECT NOW() - INTERVAL 30 DAY AS thirty_days_ago;

Both are common in MySQL. The DATE_SUB form is often easier to read when the query contains several date manipulations.

Know Whether You Want a Datetime or a Date

If the question is really about the last 30 calendar days rather than the last 30 times 24 hours, CURDATE() may be a better starting point.

SELECT DATE_SUB(CURDATE(), INTERVAL 30 DAY) AS date_only_cutoff;

CURDATE() returns only the date, with no time component. That difference matters in reporting queries. For example, filtering with a date-only cutoff can include or exclude records differently from filtering with a full timestamp cutoff.

Use It in WHERE Clauses Carefully

A very common use case is retrieving recent rows.

SELECT order_id, created_at
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

This means "rows from the last 30 times 24 hours." That is different from "all rows from the current day and the previous 29 days," which is a calendar-based interpretation.

For old-row cleanup, flip the comparison:

DELETE FROM sessions
WHERE last_access < DATE_SUB(NOW(), INTERVAL 30 DAY);

The calculation itself is simple. The real engineering question is which cutoff semantics your application needs.

Indexing and Predicate Shape

Avoid wrapping the column in a function when you want index-friendly filtering. This is usually better:

WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)

than this:

WHERE DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

The second form can make indexing less useful because the function is applied to every row value before comparison. When performance matters, compute the cutoff once and compare the column directly.

DATE_ADD with a Negative Interval Also Works

MySQL also allows subtraction through DATE_ADD and a negative interval.

SELECT DATE_ADD(NOW(), INTERVAL -30 DAY) AS thirty_days_ago;

This is valid, but most readers find DATE_SUB(..., INTERVAL 30 DAY) clearer because it says exactly what the query is doing.

Time zone policy also matters. If the application server, MySQL session, and business reporting rules disagree about the active time zone, a perfectly valid date-arithmetic expression can still produce the wrong business result.

Testing with representative sample timestamps around midnight, month boundaries, and daylight-saving changes can catch these semantic mistakes early, especially in reporting or retention code where date rules often carry business meaning beyond simple arithmetic.

Common Pitfalls

Confusing NOW() with CURDATE() leads to subtle bugs because one includes time and the other does not.

Using the wrong comparison direction in retention queries can reverse the meaning of the filter.

Wrapping the datetime column in a function during filtering can make the query less index-friendly.

Summary

• Use DATE_SUB(NOW(), INTERVAL 30 DAY) for the current timestamp minus 30 days.
• Use CURDATE() instead of NOW() when you want date-only semantics.
• In WHERE clauses, compare the column directly to the computed cutoff for better clarity and indexing.
• Be explicit about whether your application means 30 calendar days or the last 30 times 24 hours.