How to Get Yesterday's Date in MySQL

Retrieving yesterday’s date in MySQL requires you to use the CURDATE() or NOW() functions combined with the INTERVAL keyword. This guide shows how to calculate yesterday’s date and return it in different formats.

Understanding date functions in MySQL

MySQL provides several functions for date and time operations. The two commonly used for getting the current date are CURDATE() and NOW(). CURDATE() returns the current date without time, while NOW() returns both date and time.

Example: Current date and time

SELECT CURDATE() as CurrentDate, NOW() as CurrentDateTime;

Getting yesterday’s date

To get yesterday’s date, subtract one day from the current date. Use the INTERVAL keyword with CURDATE() or NOW().

Example: Yesterday’s date

SELECT CURDATE() - INTERVAL 1 DAY as YesterdayDate;

Formatting yesterday’s date

MySQL’s DATE_FORMAT() function formats the date output. For instance, to get yesterday’s date in a specific format, combine DATE_FORMAT() with the date calculation.

Example: Formatted yesterday’s date

SELECT DATE_FORMAT(CURDATE() - INTERVAL 1 DAY, '%Y-%m-%d') as FormattedYesterdayDate;

Use cases in queries

Retrieving records based on yesterday’s date is a common use case. For example, to find all entries from a logs table that occurred yesterday:

Example: Filtering data by yesterday’s date

SELECT *
FROM logs
WHERE DATE(log_date) = CURDATE() - INTERVAL 1 DAY;

Working with time zones

When working with time zones, use CONVERT_TZ() together with the date functions so the calculation uses the time zone you want.

Example: Yesterday’s date in a specific time zone

SELECT CONVERT_TZ(CURDATE(), '+00:00', 'America/New_York') - INTERVAL 1 DAY as YesterdayDateInNY;

Conclusion

Manipulating dates in MySQL is straightforward with functions like CURDATE() and DATE_FORMAT(). Account for time zones in your calculations to get accurate results.