Choose a week from the MySQL database, where the week runs from Wednesday to Tuesday

Question

I have a perfectly working program that weekly captures information from my database. My current problem is that now I have to change the program by selecting the current week (from Sunday to Saturday) to select the week starting from Wednesday to Tuesday.

Here is the query that I have now that works fine on Sunday from Saturday:

SELECT time, roNum
FROM $user 
WHERE YEAR(date) = YEAR(CURDATE()) AND WEEK(date) = WEEK(CURDATE());

Any help would be greatly appreciated. Thank.

Answer 1

One way to do this is to adjust the date before retrieving the date components. In this case, you can probably subtract two days before extracting the year and week:

SELECT time, roNum
FROM $user
WHERE YEAR(date - interval 2 day) = YEAR(CURDATE()) AND
      WEEK(date - interval 2 day) = WEEK(CURDATE());

Answer 2

, , . . , , :

SELECT time, roNum
FROM $user
WHERE YEAR(date + interval 4 day) = YEAR(CURDATE()) AND
      WEEK(date + interval 4 day) = WEEK(CURDATE())