CODEWITHSUNDEEP
mysql
user2279205
user2279205

Reputation: 175

MySQL Get Yesterday Content

I would like to have user can see their yesterday picture uploaded on profile page.

I this correct?

SELECT * FROM pictures WHERE userid = '$userid' AND DATE(pictureuploadeddate) = DATE_SUB(CURDATE(), INTERVAL 25 HOUR) ORDER BY uploaded DESC

Yet still not working. Thanks for the help.

Upvotes: 0

Views: 180

Answers (3)

somnath
somnath

Reputation: 1335

Why are you using DATE(pictureuploadeddate) isnt pictureuploadeddate a date format column?

My below solution assumed `pictureuploadeddate' to be a date type column. You can simply do a comparison like the following:

SELECT * FROM pictures WHERE userid = '$userid' AND pictureuploadeddate = ADDDATE(CURDATE(), -1) ORDER BY uploaded DESC

ADDDATE(CURDATE(), -1) returns yesterday's date by subtracting 1 day from today.

Upvotes: 1

Hunter Zhao
Hunter Zhao

Reputation: 4649

Take the following condition should be OK. 

DATE_FORMAT(DATE(pictureuploadeddate), '%m-%d-%Y') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%m-%d-%Y')

Upvotes: 1

John Woo
John Woo

Reputation: 263723

try this,

DATE(pictureuploadeddate) = CURDATE() - INTERVAL 1 DAY

to take advantage of the index,

pictureuploadeddate >= CURDATE() - INTERVAL 1 DAY AND pictureuploadeddate < CURDATE()

Upvotes: 1

Related Questions