MySQL select yesterday's date

Question

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?

Answer 1

This should do it:

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

Answer 2

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

subdate(currentDate, 1)

Answer 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

Answer 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.