CODEWITHSUNDEEP
mysqlsqldate
Frantisek
Frantisek

Reputation: 7693

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?

Upvotes: 11

Views: 52060

Answers (4)

Simon Dorociak
Simon Dorociak

Reputation: 33495

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

subdate(currentDate, 1)

Upvotes: 23

Elliot Larson
Elliot Larson

Reputation: 11029

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.

Upvotes: 1

John Conde
John Conde

Reputation: 219794

This should do it:

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

Upvotes: 27

Kris
Kris

Reputation: 41827

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

Upvotes: 1

Related Questions