MySQL now() value on datetime using date only

Question

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)

http://stackoverflow.com/questions/25564482/how-to-compare-datetime-with-only-date-in-sql-server — akhil gupta, Feb 11 '16 at 18:24

score 2 · Accepted Answer · answered Feb 11 '16 at 18:22

2

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);

answered Feb 11 '16 at 18:22

Nir Levy

12,750
3
21
38

MySQL now() value on datetime using date only

1 Answers1