PHP/MySQL: Retrieving the last *full* weeks entries

Question

Hi, I'm currently using the following SQL for retrieving the last seven days worth of entries from a table:

purchased >= date_sub(now() ,interval 7 day)

However, I need to change this so it retrieves the last full weeks worth of entries (midnight Saturday to midnight Saturday). So basically, throughout the week the results never change, but when someone visits on Sunday morning they will have refreshed.

I just cant get my head around how to work out the days etc. Also, are there built in MySQL functions for doing this?

I hope I've explained that clearly enough. This is in a PHP application.

Thanks, Jack

Answer 1

MySQL has a WEEK() function, that allows you to get the numeric week of the year from a date. You can get (the week of today) -1, and compare that to the week of your records you are filtering on.

Answer 2

see the MySQL function YEARWEEK().

So you could do something like

SELECT * FROM table WHERE YEARWEEK(purchased) = YEARWEEK(NOW());

You can change the starting day of the week by using a second mode parameter

What might be better however is to somehow calculate the date of 'last sunday at 00:00', and then the database would not have to run a function for each row, but I couldn't see an obvious way of doing that in MySQL. You could however easily generate this in php and do something like

$sunday = date(('Y-m-d H:i:s'), strtotime('last sunday 00:00'));
$sql = "SELECT * FROM table WHERE purchased >= '$sunday'";