How to Get Yesterday's Date in MySQL

TL;DR

MySQL has a function which will return the current date and you can subtract 1 from this value using INTERVAL to get yesterday’s date in MySQL.

SELECT CURDATE() - INTERVAL 1 DAY AS yesterday;

There are also a couple of other ways which we will look at in this article.

Using CURDATE()

As shown above, one of the simplest ways to get yesterday’s date in MySQL is by using the CURDATE() function. This function returns the current date (without the time). To get yesterday’s date, we can subtract 1 day from CURDATE().

SELECT CURDATE() - INTERVAL 1 DAY AS yesterday;

Explanation:

• CURDATE() returns today’s date, and we use the INTERVAL keyword to subtract one day from it, effectively giving us yesterday’s date.

Using DATE_SUB()

Another way to achieve the same result is with the DATE_SUB() function. This function allows us to subtract a specific time interval from a date.

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

Explanation:

• DATE_SUB() takes two arguments: a date and the interval to subtract. Here, we subtract 1 day from the current date.

Using INTERVAL

As mentioned earlier, the INTERVAL keyword is a flexible way to add or subtract time from dates. You can use INTERVAL in combination with NOW() or CURDATE() for various date manipulations.

SELECT NOW() - INTERVAL 1 DAY AS yesterday;

Explanation:

• NOW() returns the current date and time. Subtracting 1 day from NOW() also gives you yesterday’s date, but it will include the time component.

Custom Date Formats

You can also format the date using the DATE_FORMAT() function in MySQL. If you need yesterday’s date in a specific format, this method is useful.

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

This returns yesterday’s date in the format YYYY-MM-DD. You can adjust the format based on your needs.

Using NOW()

Although CURDATE() is used to work with dates without time, NOW() works with both date and time. If you want yesterday’s date but also need the exact timestamp for “this time yesterday,” you can use:

SELECT NOW() - INTERVAL 1 DAY AS yesterday_with_time;

This query will return yesterday’s date along with the time component, which can be handy for time-sensitive applications.

Providing Yesterday’s Date via PHP Code

In addition to using MySQL, there are times when you may want to calculate yesterday’s date on the server-side before passing it to your SQL queries. Using PHP, this can be done easily with the date() and strtotime() functions.

<?php
    $yesterday = date('Y-m-d', strtotime("-1 day"));
    echo $yesterday;
?>

Explanation:

• strtotime("-1 day") shifts the current timestamp back by one day.
• date('Y-m-d', ...) formats the timestamp as a date string (YYYY-MM-DD).

This method can be useful when you need to dynamically generate queries or when interacting with APIs that rely on date calculations.

Conclusion

In this blog post, we explored various ways to get yesterday’s date in MySQL. The CURDATE() - INTERVAL 1 DAY and DATE_SUB() methods are efficient and straightforward for extracting the previous day’s date. We also covered how to format dates and retrieve yesterday’s exact time using NOW(). Additionally, you can handle date calculations on the server-side using PHP, which gives you more flexibility when interacting with your database.

With these techniques in hand, you should now have a solid understanding of how to manage “yesterday’s date” in both MySQL and PHP, empowering you to write more dynamic and flexible queries.

Additional Resources:

• MySQL Date and Time Functions
• PHP Date and Time Functions