How to set SQL to find records from last sunday to this sunday (1 week)
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())-1 DAY) latest_sun
To get the sunday one week earlier, instead use DAYOFWEEK(NOW())+6 DAY
.
EDIT: That'd make your query;
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)
ParoX
Updated on June 13, 2022Comments
-
ParoX about 2 years
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 count of how many times an author has posted in the past 1 week.
I want to instead have it so if I am to run the program on Sunday at 5:30 PM to look for posts from last Sunday 12:00 AM to this Sunday 12:00 AM. Likewise, if I forget to run it on Sunday and it's monday now. I still want it to run from last Sunday 12:00 AM to the Sunday that just past 12:00 AM
Edit:
I have done what I needed using PHP to form the correct SQL statement, but I am still curious how to do this in just SQL.
<?php $dayofweek = strftime("%A",time()); if($dayofweek == "Sunday") { $last_sunday = date('Y-m-d h:i:s',strtotime('Last Sunday')); $this_sunday = date('Y-m-d h:i:s',strtotime('Sunday')); } else { $last_sunday = date('Y-m-d h:i:s',strtotime('Last Sunday',strtotime('Last Sunday'))); $this_sunday = date('Y-m-d 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'"; ?>