[Solved] MySQL select yesterday’s date

If I have a date like this:

'2013-03-25'

And I want to write a MySQL query with WHERE is “yesterday”, how do I do it?

Solution #1:

A simple way to get yesterday’s date is to use subdate() function:

subdate(currentDate, 1)
Respondent: Simon Dorociak
Solution #2:

This should do it:

WHERE `date` = CURDATE() - INTERVAL 1 DAY
Respondent: John Conde
Solution #3:

I think you’re looking for:

DATE_ADD(date_column, INTERVAL -1 DAY)

see https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Respondent: Kris
Solution #4:

I always have to refer to a code snippet to wrap my head around this again.

It’s customary to store datetimes in UTC in the database.

But usually, when generating reports, the times need to be adjusted for a specific timezone.

Here’s the snippet I use to show selecting yesterday, adjusted for Pacific time:

SET @date_start = DATE_SUB((CURDATE() - INTERVAL 1 DAY), INTERVAL 8 HOUR);
SET @date_end = DATE_SUB(CURDATE(), INTERVAL 8 HOUR);

SELECT
    projects.token,
    projects.created_at as 'UTC created_at',
    DATE_SUB(projects.created_at, INTERVAL 8 HOUR) as 'Pacific created_at'
FROM
    projects
WHERE
    projects.created_at BETWEEN @date_start AND @date_end;

Note: I set the variables in the snippet so it’s easier to look at. When I write the final query, I usually don’t use the variables.

Respondent: Elliot Larson
The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.