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?

Possible duplicate of [MySQL selecting yesterday's date](http://stackoverflow.com/questions/7146828/mysql-selecting-yesterdays-date) — derobert, Jul 01 '16 at 16:49

John Conde · Answer 1 · 2013-03-25T19:19:54.480

27

This should do it:

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

edited Mar 25 '13 at 19:19

answered Mar 25 '13 at 19:10

John Conde

217,595
99
455
496

What if it's a minute earlier than the current time? – SLaks Mar 25 '13 at 19:10
I'm not sure what you're getting at. Can you clarify please? – John Conde Mar 25 '13 at 19:12
1

Should that be `curdate()`, not `now()`? AFAIK, your code will only work if the time-of-day matches exactly – SLaks Mar 25 '13 at 19:18
2

I'm assuming a column type of `date` which may not be true. So your suggestion is a good one and is safer so I have edited my answer to use it. – John Conde Mar 25 '13 at 19:21
3

If `date` is a `DateTime`, it should be `DATE(date) = CURDATE - INTERVAL 1 DAY` ? – Daniel W. Mar 18 '14 at 11:36

score 23 · Accepted Answer · edited Jun 17 '16 at 10:39

23

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

subdate(currentDate, 1)

edited Jun 17 '16 at 10:39

FK-

1,462
1
10
17

answered Mar 25 '13 at 19:11

Simon Dorociak

33,374
10
68
106

score 1 · Answer 3 · answered Mar 25 '13 at 19:10

1

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

answered Mar 25 '13 at 19:10

Kris

40,604
9
72
101

score 1 · Answer 4 · answered Sep 02 '14 at 21:03

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.

MySQL select yesterday's date

4 Answers4

Linked