tags:

views:

801

answers:

2

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

A: 

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.

acrosman
You need to check the year, otherwise you might get records from the same week but a year ago
Tom Haigh
Good point, my mistake.
acrosman
+2  A: 

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'";
Tom Haigh
Thanks for that, I think it looks like the best solution. However I don't want it to get everything since the last Sunday, but instead everything between the second to last Sunday and the last Sunday, so it would always be "last weeks" entries.
Jack Sleight
OK, I've worked it out. Used "last sunday 00:00 -1 week", then added an additional WHERE condition.
Jack Sleight