MYSQL datetime rows from 7 days ago?

Question

Trying all sorts of things but coming up empty handed at the moment. I have it running hardcoded like this:

WHERE (activities.created_at BETWEEN '2015-08-07 00:00:00'
                              AND '2015-08-07 23:59:59.999')

I'm just trying to automate it a bit so I don't have to go in there every day and change the date to whatever it was 7 days ago.

My most recent test is:

WHERE (`activities`.`created_at` = CURDATE() - INTERVAL 7 DAY)

It isn't throwing an error, but rather showing an empty set when there should be 338 rows to show.

Answer 1

This should work

where date(created_at) = CURDATE() - interval 7 day

Your problem is that the datatype on either side of the equal sign weren't the same. created_at is a datetime column, while curdate() returns a date. The above statement converts created_at to a date before comparing.

Answer 2

I'd do it like this:

 WHERE `activities`.`created_at` >= DATE(NOW()) - INTERVAL 7 DAY
   AND `activities`.`created_at` <  DATE(NOW()) - INTERVAL 6 DAY

Use CURDATE() if you prefer, in place of DATE(NOW()).

My preference is to do a "less than" midnight of the following day.

Doing less than or equal to a value with time component of "23:59:59" will work fine, as long as resolution is down to second, which is what the MySQL DATETIME or TIMESTAMP is. Some databases support date time values with even more precision, down to 3ms in SQL Server, e.g. "23:59:59.982".

The "less than midnight of the following day" pattern works for date time values that are more precise than 1 sec.