If I have a date like this:
And I want to write a MySQL query with
WHERE is “yesterday”, how do I do it?
A simple way to get yesterday’s date is to use
This should do it:
WHERE `date` = CURDATE() - INTERVAL 1 DAY
I think you’re looking for:
DATE_ADD(date_column, INTERVAL -1 DAY)
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.