How To Set Sql To Find Records From Last Sunday To This Sunday (1 Week)

This is similar to what I have now, which is: SELECT COUNT(author) FROM `posts` WHERE `date` >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND author='FooBar' This will give me a coun

Solution 1:

To get the latest preceding sunday midnight, this should do it. Replace both instances of NOW() with your datetime to check another date.

SELECT DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())-1DAY) latest_sun

To get the sunday one week earlier, instead use DAYOFWEEK(NOW())+6 DAY.

EDIT: That'd make your query;

SELECTCOUNT(author)
FROM `posts` 
WHERE author='FooBar'AND `date` >= DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())+6DAY)
  AND `date` <  DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())-1DAY)