提取1周时间间隔的记录，当新周开始时，他们开始下一个计时器

Question

I am using the query given below to extract the records for 1 Week Time interval.

 mysql_query("SELECT t1.username, SUM(t2.points) AS total 
    FROM actcontest t2 JOIN user t1 ON t1.userid = t2.userid WHERE t2.date
    BETWEEN (CURDATE() - INTERVAL 1 WEEK) AND CURDATE()  GROUP BY t1.username ORDER BY 
    total DESC LIMIT 20");

Once the records are fetched I am using the different query to award bonuses to the top 3 users.

This works fine.

Problem: Once bonus has been awarded to the last week, top 3 members, new dates shall start from the new week starting dates, where last week ended.

suppose: we started the timer on date: [dd/mm/yy]
Week 1 = {01-01-2010 to 07/01/2010}
Week 2 = {07-01-2010 to 14/01/2010}
Week 3 = {14-01-2010 to 21/01/2010}
Week 4 = {21-01-2010 to 28/01/2010}

Can you please help me in building this logic for my script. I am using php and Mysql.

What I want to Do:
One i have awarded bonus to week one winners. I want timer to start fetching the members for the next week only, It should not include the previous dates .

I hope i clarified the question.

Answer 1

Consider something like this:

SET @weekNo := 4;
SET @dateStart := '2014-01-01';

SELECT ADDDATE(CONCAT(@dateStart, ' 00:00:00'), INTERVAL 7*(@weekNo-1) DAY),
    ADDDATE(CONCAT(@dateStart, ' 23:59:59'), INTERVAL 7*@weekNo-1 DAY);

You can check the above script in sql. You can change the 2 variables at the top: @weekNo is the week you need to compute the date interval since the start date. And the @dateStart is the date the timer started.

In your php code you can compute the date by replacing the dateStart and weekNo with php variables.