How to set SQL to search for records from last Sunday this Sunday (1 week)

Question

This is similar to what I have now:

SELECT COUNT(author) FROM `posts` WHERE `date` >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND author='FooBar'

This will give me a count of how many times the author has published in the last 1 week.

I want this to be the case if I have to run the program on Sunday at 17:30 to search for messages from the last Sunday from 12:00 to today's Sunday 12:00. Similarly, if I forget to launch it on Sunday and now on Monday. I still want him to run from the last Sunday from 12:00 to Sunday, which happened only at 12:00.

Edit:

I did what I needed using PHP to form the correct SQL statement, but I'm still wondering how to do this only in SQL.

 <?php $dayofweek = strftime("%A",time()); if($dayofweek == "Sunday") { $last_sunday = date('Ymd h:i:s',strtotime('Last Sunday')); $this_sunday = date('Ymd h:i:s',strtotime('Sunday')); } else { $last_sunday = date('Ymd h:i:s',strtotime('Last Sunday',strtotime('Last Sunday'))); $this_sunday = date('Ymd h:i:s',strtotime('Last Sunday')); } print "last_sunday={$last_sunday}<br>"; print "this_sunday={$this_sunday}<br>"; print "SELECT COUNT(author) FROM `posts` WHERE `date` <= '$this_sunday' AND `date` >= '$last_sunday' AND author='FooBar'"; ?> 

Answer 1

To get the last previous afternoon, this must do it. Replace both instances of NOW() with your time to check for a different date.

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

To get Sunday the week before, use DAYOFWEEK(NOW())+6 DAY .

EDIT: this will make your request:

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