[Solved] MySQL select yesterday’s date

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

subdate(currentDate, 1)

This should do it:

WHERE `date` = CURDATE() - INTERVAL 1 DAY

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

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.