CODEWITHSUNDEEP
mysqldatetime
Rocco The Taco
Rocco The Taco

Reputation: 3797

MySQL now() value on datetime using date only

I have a datetime field called LastChangeTimestamp that has a format like 2015-08-20 10:24:59 I basically want to return all results from the current day back 7 days. I've gotten to where I can use the now() command but am returning zero results I suspect because the system is trying to match the date and time down to the seconds back maybe? How do I return records from the current date only (no time) using the datetime field and go back 7 days?

WHERE LastChangeTimestamp < unix_timestamp(now() - interval 7 day)

Upvotes: 1

Views: 587

Answers (1)

Nir Levy
Nir Levy

Reputation: 12953

if your column is in date format, you don't need to use unixtimestamp, you can just use date_sub() function to get the date 7 days ago, and compare to it:

WHERE LastChangeTimestamp > DATE_SUB(now(),INTERVAL 7 DAY);

Upvotes: 2

Related Questions