I am using this function to filter query results that are older than 60 days:
s.timeSubmitted >= ( CURDATE() - INTERVAL 60 DAY )
The problem is, the "60 days" part doesn't seem to be an exact figure. I want it to filter right where s.timeSubmitted is longer than 60 days, down to the exact second of s.timeSubmitted.
How do I write "60 Days" as an exact figure (down to the second)?
The problem is that
CURDATE() returns a
DATE type, not a
DATETIME type (an instant in time). The result of subtracting an interval from a
DATE is also a
Instead, try this:
s.timeSubmitted >= ( NOW() - INTERVAL 60 DAY )
This gives you what you want, because
NOW() returns a
DATETIME, so the result of the subtraction is also a
INTERVAL 60 DAY is exact - your problem is that
CURDATE() isn't. It returns whole days, not the current time.
I usually do
now()-interval 60 day
Assuming you want the same time of day 60 days ago;
s.timeSubmitted >= ( now() - interval 60 day);
Maybe an un-necessary note in this case; 1 day ago may be 23, 24 or 25 hours ago depending on DST changes, if you want a specific number of hours as an interval, don't use a day instead of 24 hours.