CODEWITHSUNDEEP
mysqlsql
Andre
Andre

Reputation: 839

MYSQL select records older than 1 day

I am trying to select records that are older than 1 day or 24 hours from a MYSQL DB. I can't figure it out why this statement doesn't work with NOW() and it only works if I explicitly add the date manually, here is the code for your review:

date_posted is setup as "datetime" in the DB table.

Works this way (Not desirable, for testing only)

SELECT count( DISTINCT my_id ) AS 'a'
FROM my_table
WHERE date_posted >= DATE_SUB( '2015-10-03 09:10:19', INTERVAL 24 HOUR )

It should work this way, but it brings a "0" count result:

SELECT count( DISTINCT my_id ) AS 'a'
FROM my_table
WHERE date_posted >= DATE_SUB( NOW(), INTERVAL 24 HOUR )

Upvotes: 4

Views: 9158

Answers (1)

Payer Ahammed
Payer Ahammed

Reputation: 907

SELECT count( DISTINCT my_id ) AS 'a'
FROM my_table
WHERE date_posted >= subdate(current_date, 1)

here subdate(current_date, 1) will return your the yesterday date if your want to select only the yesterday post then use the following WHERE date_posted = subdate(current_date, 1)

Upvotes: 3

Related Questions