Introduction

Filtering MySQL rows for “today and the last 30 days” sounds simple, but the correct query depends on what your column stores and what the business rule actually means. The best solution usually compares the raw date column against calculated boundaries so the query stays both correct and index-friendly.

Decide Whether You Mean Dates or a Rolling Window

There are two common interpretations of this requirement:

• all rows from the current calendar date back through 30 calendar days
• all rows from the last 30 times 24 hours from the current moment

Those are not the same thing. If it is 4:00 PM right now, a rolling 30-day window excludes rows from 31 days ago at 9:00 AM, while a calendar-based report may still include that whole date if it falls inside the reporting range.

Defining the time window first prevents subtle bugs later.

Querying a DATE Column

If the column stores a DATE, there is no time component to worry about. In that case, BETWEEN is easy to read and usually correct.

SELECT id, order_date, total
FROM orders
WHERE order_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE();

This includes:

• today
• every full date back to 30 days ago

For pure date data, that is often all you need.

Querying a DATETIME or TIMESTAMP Column

If the column stores a timestamp, use a half-open range instead of BETWEEN. That avoids awkward end-of-day bugs and makes the intent much clearer.

1SELECT id, created_at, total
2FROM orders
3WHERE created_at >= CURDATE() - INTERVAL 30 DAY
4  AND created_at < CURDATE() + INTERVAL 1 DAY;

This query means:

• start at midnight 30 days ago
• include every timestamp today
• stop just before midnight tomorrow

That is usually the cleanest SQL translation of “today and the last 30 days.”

If you truly want the last 30 times 24 hours from the current instant, use NOW() instead:

SELECT id, created_at, total
FROM orders
WHERE created_at >= NOW() - INTERVAL 30 DAY;

That is a different business rule, so avoid swapping CURDATE() and NOW() without thinking through the impact.

Keep the Query Index-Friendly

A common beginner solution is to wrap the column in DATE():

SELECT id, created_at, total
FROM orders
WHERE DATE(created_at) BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE();

The logic is fine, but performance can suffer because MySQL may not be able to use a normal index on created_at efficiently once the column is wrapped in a function.

Prefer calculating the boundary values and comparing them directly to the raw column:

WHERE created_at >= CURDATE() - INTERVAL 30 DAY
  AND created_at < CURDATE() + INTERVAL 1 DAY

That pattern is usually easier for the optimizer to work with.

Parameterized Example from Application Code

If the number of days comes from the application, compute the range in code and pass it safely as parameters instead of building SQL strings manually.

1from datetime import datetime, timedelta
2import mysql.connector
3
4conn = mysql.connector.connect(
5    host="127.0.0.1",
6    user="app",
7    password="secret",
8    database="shop",
9)
10
11upper = datetime.utcnow()
12lower = upper - timedelta(days=30)
13
14with conn.cursor(dictionary=True) as cursor:
15    cursor.execute(
16        """
17        SELECT id, created_at, total
18        FROM orders
19        WHERE created_at >= %s
20          AND created_at < %s
21        ORDER BY created_at DESC
22        """,
23        (lower, upper),
24    )
25    rows = cursor.fetchall()
26
27print(rows[:5])
28conn.close()

This keeps the query safe, makes testing easier, and avoids accidental formatting problems.

Time Zones Matter

The phrase “today” only makes sense in a specific time zone. If the database stores UTC timestamps but the report is meant for a user in Toronto, then CURDATE() on the database server may not reflect the date boundary the user expects.

Pick one rule and stick to it:

• store and query everything in UTC
• or convert user-local day boundaries into UTC before running the query

Without that rule, reports tend to break near midnight or around daylight saving transitions.

Check the Execution Plan

When these queries run on large tables, verify that MySQL is actually using your index:

1EXPLAIN
2SELECT id, created_at, total
3FROM orders
4WHERE created_at >= CURDATE() - INTERVAL 30 DAY
5  AND created_at < CURDATE() + INTERVAL 1 DAY;

If the table is also filtered by another field such as status, a composite index may help:

CREATE INDEX idx_orders_status_created_at
    ON orders (status, created_at);

The right index depends on the full query shape, not just on the date predicate.

Common Pitfalls

• Using NOW() when the report really means full calendar dates including all of today.
• Wrapping the indexed timestamp column in DATE() and making the filter harder to optimize.
• Using BETWEEN on a timestamp range without thinking about the end-of-day boundary.
• Ignoring time zone rules when deciding what “today” should mean.
• Treating DATE, DATETIME, and TIMESTAMP columns as if they should all use the exact same filter.

Summary

• Decide first whether you want calendar days or a rolling 30-day window.
• For DATE columns, BETWEEN with CURDATE() is often correct and readable.
• For DATETIME or TIMESTAMP, a half-open range is usually the safer pattern.
• Compare raw columns against calculated bounds instead of calling functions on the column.
• Use EXPLAIN and appropriate indexes when the table is large.