Introduction

Selecting rows from "last week" in MySQL sounds simple, but the correct query depends on what you mean by that phrase. Sometimes it means the last seven days relative to right now, and sometimes it means the previous calendar week such as Monday through Sunday. Those are different filters and should not be written the same way.

Last Seven Days Versus Previous Calendar Week

The most common interpretation is "the last seven days from now." For a timestamp column named created_at, that looks like this:

SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL 7 DAY;

This returns rows from the rolling seven-day window ending at the current moment.

If your column stores only a date with no time component, you can do the same with CURDATE():

SELECT *
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 7 DAY;

That is not the same as "previous week" on the calendar. It is just a rolling lookback window.

Previous Calendar Week

If you mean the full previous week, define explicit start and end boundaries.

A Monday-based week example:

1SELECT *
2FROM orders
3WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 7 DAY)
4  AND created_at <  DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY);

This returns the rows from the previous Monday up to, but not including, the current week's Monday.

That "greater than or equal to start, less than end" pattern is usually safer than BETWEEN for datetime data because it avoids boundary ambiguity at the end of the range.

Why Half-Open Ranges Are Better

A query like this is common:

WHERE created_at BETWEEN start_date AND end_date

The problem is that datetime columns include time. If end_date is a date literal like 2025-03-07, then rows later that same day may not behave the way you expect.

A safer pattern is:

WHERE created_at >= '2025-03-01'
  AND created_at <  '2025-03-08'

This includes the whole final day cleanly without needing to guess whether to append 23:59:59.

That pattern is one of the most important habits in date filtering.

Do Not Wrap The Indexed Column In A Function

A common beginner query is:

SELECT *
FROM orders
WHERE DATE(created_at) >= CURDATE() - INTERVAL 7 DAY;

This may work logically, but it often prevents MySQL from using an index on created_at efficiently because the column is wrapped in a function.

Prefer filtering directly on the raw column:

SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL 7 DAY;

That keeps the predicate sargable and gives MySQL a better chance to use the index.

Example With Aggregation

Often you want not just the rows, but a summary of last week's activity.

1SELECT DATE(created_at) AS day, COUNT(*) AS total
2FROM orders
3WHERE created_at >= NOW() - INTERVAL 7 DAY
4GROUP BY DATE(created_at)
5ORDER BY day;

Here using DATE(created_at) in the SELECT and GROUP BY is fine because the filtering already happened using the raw indexed column.

This is a good pattern when you need reporting but still want efficient range filtering.

Time Zone Considerations

If the application and database use different time zones, "last week" can shift in surprising ways. For example, NOW() uses the session time zone. If users think in local time but the database server runs in UTC, the boundary may not match business expectations.

So before finalizing the query, decide:

• are timestamps stored in UTC
• should the range be based on server time or user-local time
• does the reporting definition of week start on Monday or Sunday

These are data-contract questions, not just SQL syntax questions.

A Practical Rule Of Thumb

Use these templates:

Rolling seven days:

WHERE created_at >= NOW() - INTERVAL 7 DAY

Previous calendar week with explicit boundaries:

WHERE created_at >= start_of_last_week
  AND created_at <  start_of_this_week

That keeps the intent obvious and the query maintainable.

Common Pitfalls

The biggest mistake is not defining what "last week" means. Rolling seven days and previous calendar week are different queries.

Another mistake is using DATE(created_at) in the WHERE clause and accidentally hurting index usage.

People also rely on BETWEEN with date-like endpoints on datetime columns, which often introduces end-of-day bugs.

Finally, do not ignore time zones. A query that is technically correct in server time can still be wrong for the business definition of the reporting window.

Summary

• Decide first whether "last week" means the last seven days or the previous calendar week.
• Use direct range comparisons on the raw datetime column for better index usage.
• Prefer half-open intervals such as >= start and < end for datetime filtering.
• Avoid wrapping the filtered column in DATE() unless you are willing to trade away index efficiency.
• Be explicit about time zone and week-start assumptions.