mySQL selecting in last 7 days

Databases
1

Each week at 4:00 PM EST a cron script is run with the below query. The query selects the posted article with the most votes. However, it didn’t work today (it’s returning 0 records) because the winning article was posted today and received the votes today.

I need to select the article that has the most votes within the last 7 days. that means any time after 4:00 PM EST last friday to 4:00 EST today.

is this query correct, and my problem lies elsewhere?

SELECT
	vote_post_id AS post_id,
	COUNT(vote_usr_id) AS total
FROM
	posts_votes
WHERE
	vote_date_added BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE()
GROUP BY
	post_id
ORDER BY
	total DESC
LIMIT 1
2

what datatype is the vote_date_added column, and how do you populate its values?

the reason i ask is because if you are using a current timestamp, i.e. with hours and minutes included, then any values for today will not get included

this is because your BETWEEN values are evaluated as 2010-09-10 00:00:00 and 2010-09-17 00:00:00, so anything after midnight today is not included

3

It’s datetime. I use now().

What would you recommend?

4

i would recommend

WHERE vote_date_added
  BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY
      AND CURRENT_TIMESTAMP
5

Thanks Rudy! :slight_smile: