MySQL Query Finding rows since last [day of week]

Looking for some help with a MySQL query. I have a table with a field containing a timestamp value (such as 2008-04-21 10:12:04). I want a query that will return rows from that table where the timestamp is greater than the most recent Saturday.

For example, today is Monday so the were clause would include all records where the timestamp was on Saturday (two days ago), Sunday, or today.

If today was a Thursday, the were clause would include records from the previous Sat, Sun, Mon, Tue, Wed, and Thu.

If today was a Saturday, get records created that day.

So I basically need to figure out the date of the most recent Saturday, and have the where clause fetch all records starting from 00:00:00 on that day.

 

SELECT * FROM table WHERE 
timestamp_field >= CAST( CONCAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()-5) DAY), ' 00:00:00') AS DATETIME)

Code (markup):

 

Brilliant, thanks. I'll be sure to check it out to see how it works.

 

Waiting for the response

 

Sorry, what I meant was "brilliant, thanks, it works perfectly" and "I'll be sure to check it out and see why it works so I can learn."

Cheers!

 

Ohhh, thanks for the comments

 

hmmm.... perhaps this isn't working across new months as it appears to have broken this morning.

 

Fixed query

SELECT * FROM table WHERE 
timestamp_field >= CONCAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(DATE_SUB(CURDATE(), INTERVAL 5 DAY)) DAY), ' 00:00:00');

Code (markup):

 

Hey, that works better, thanks again!

 

You are welcome.