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