CODEWITHSUNDEEP
mysqlsql
user2168066
user2168066

Reputation: 635

Select rows from yesterday's date

I have a query function that selects all rows from the previous days. However, I need it to only select the rows with yesterdays date but am unsure how to include just the previous day.

My current query is:

SELECT pdate FROM table 1
WHERE pdate < Date(NOW()) + INTERVAL 1 DAY

Upvotes: 0

Views: 2875

Answers (2)

AdamMc331
AdamMc331

Reputation: 16691

You can use the DATE_SUB() function to get yesterday's date, and then use a WHERE clause condition to look for just that date, like this:

SELECT *
FROM myTable
WHERE DATE(pDate) = DATE_SUB(pDate, INTERVAL 1 DAY);

Here is a list of MySQL's Date and Time Functions which may help you.

NOTE: This will work, but because you are using a function on the pDate column in the where clause this will not be able to take advantage of any indexes you have (see comments below). Brian Driscoll has given an answer that will work better. I am choosing to leave this answer because while it is less efficient, I believe it is more readable as the where clause is very explicit in what it is checking and is slightly more readable. Whether or not the trade off is worth it here is up to the developer.

Upvotes: 0

Brian Driscoll
Brian Driscoll

Reputation: 19635

I would imagine it would look something like this, which has the advantage of using indexes (if you have them implemented)

SELECT pdate FROM table 1
WHERE pdate >= Date(NOW()) - INTERVAL 1 DAY
AND pdate < Date(NOW())

Upvotes: 3

Related Questions