Introduction

In MySQL, “yesterday” is usually expressed with CURDATE() - INTERVAL 1 DAY or DATE_SUB(CURDATE(), INTERVAL 1 DAY). That solves the simple case of calculating the date value itself, but when you query real tables, the correct filter depends on whether your column stores a DATE or a DATETIME.

Get Yesterday as a Date Value

If you only need the date itself, either of these expressions works:

SELECT CURDATE() - INTERVAL 1 DAY;

SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY);

Both return yesterday’s date without a time portion.

Compare Against a DATE Column

If your table column is a DATE, comparison is straightforward.

SELECT *
FROM daily_reports
WHERE report_date = CURDATE() - INTERVAL 1 DAY;

This works well because both sides of the comparison represent whole dates.

Querying a DATETIME Column Is Different

A common mistake is writing this against a DATETIME column:

SELECT *
FROM orders
WHERE created_at = CURDATE() - INTERVAL 1 DAY;

That usually fails to match rows, because created_at contains hours, minutes, and seconds while the right side resolves to midnight.

For DATETIME, use a range query covering the full day:

1SELECT *
2FROM orders
3WHERE created_at >= CURDATE() - INTERVAL 1 DAY
4  AND created_at < CURDATE();

That captures every timestamp from the start of yesterday up to but not including the start of today.

Why Range Queries Are Better Than Wrapping the Column

You may see code like this:

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

It works logically, but wrapping the column in DATE() often prevents efficient index use. The range form is usually better for performance:

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

That lets MySQL use an index on created_at more effectively.

Yesterday Versus the Last 24 Hours

These are not the same requirement. “Yesterday” means the previous calendar day in the current session time zone. “The last 24 hours” means a rolling window from the current moment.

For the last 24 hours, use NOW() instead of CURDATE():

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

That query returns rows from exactly 24 hours ago up to now, which may include part of today and exclude part of yesterday.

Using NOW() When You Really Need Timestamps

CURDATE() returns only the current date. NOW() returns the current date and time.

If you need “the same time yesterday” rather than “the entire previous calendar day,” use:

SELECT NOW() - INTERVAL 1 DAY;

That is a different business rule, so it is worth being explicit in application code and reporting logic.

Time Zone Considerations

Date calculations follow the MySQL session time zone. If your application writes timestamps in UTC but the session runs in another time zone, “yesterday” may not mean what you think.

You can inspect the current setting with:

SELECT @@session.time_zone, @@global.time_zone;

When date boundaries matter, make sure application expectations and database time zone settings line up.

Common Pitfalls

The most common mistake is comparing a DATETIME column directly to yesterday’s date value and expecting all of yesterday’s rows to match.

Another pitfall is wrapping indexed columns in DATE() for convenience, which can hurt performance on larger tables.

Developers also sometimes confuse “yesterday” with “the last 24 hours.” Those are not always the same thing.

Finally, time zone settings can shift day boundaries in surprising ways if the application and database are not aligned.

Summary

• Use CURDATE() - INTERVAL 1 DAY or DATE_SUB(CURDATE(), INTERVAL 1 DAY) to compute yesterday’s date.
• Compare directly for DATE columns.
• Use a half-open range for DATETIME columns.
• Prefer range predicates over DATE(column) when performance matters.
• Verify time zone behavior when your application depends on exact day boundaries.