Clever Geek Handbook

PHP / MySQL: get the last * full * weekly records

I am currently using the following SQL to get the latest records for the last seven days from a table:

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

However, I need to change this so that it returns the last full weekly entries (midnight to midnight to Saturday). This way, the results do not change throughout the week, but when someone visits on Sunday morning, they will be updated.

I just can't figure out how to work on days etc. Also, are there built-in MySQL functions to do this?

I hope I have explained this clearly enough. It's in a PHP application.

+1


source to share


2 answers


see the MySQL YEARWEEK () function .

So, you can do something like

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


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

What could be better, somehow calculate the date "last Sunday at 00:00" and then the database won't need to run the function for every row, but I couldn't see an obvious way to do that in MySQL. However, you can easily create 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'";
+2


source


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



0


source






More articles:


All Articles