Clever Geek Handbook

Retrieving data from the last week (and not the last 7 days)

I have data with the below format.

id     - autoincrement
myDate - timestamp

What I want to extract is to get ids for the last week.

Note Last week I mean from Sat-Thu and not the last 7 days. Given the current date (Feb 12, 2013), for me the last week would be 2-Feb-2013 to 8-Feb-2013

.

I know what you are going to say, the week starts on Sunday, but when I work in Arab countries, here Friday is off and work starts on Saturday.

I know for the last 7 days it will be just below the code

BETWEEN NOW() and DATE_SUB(NOW(), INTERVAL 7 DAY)

Data in sqlfiddle

+3


source to share


1 answer


Can you try the method below to calculate the desired result:

SELECT *,WEEKOFYEAR(dt),DAYOFWEEK(dt),DAYNAME(dt), DAYOFWEEK(NOW()),WEEKOFYEAR(NOW()) 
FROM tab1 
WHERE 1
AND 
(
  (DAYOFWEEK(NOW()) = 1 OR DAYOFWEEK(NOW()) = 6 OR DAYOFWEEK(NOW()) = 7) 
  AND 
  WEEKOFYEAR(dt) = WEEKOFYEAR(NOW()) AND DAYOFWEEK(dt) < 6
)
OR 
(    
  (DAYOFWEEK(NOW()) = 2 OR DAYOFWEEK(NOW()) = 3 OR DAYOFWEEK(NOW()) = 4  
  OR DAYOFWEEK(NOW()) = 5)
  AND
  (
     (
      (WEEKOFYEAR(dt) = WEEKOFYEAR(NOW())-2 AND DAYOFWEEK(dt) >= 6)
      OR 
      (WEEKOFYEAR(dt) = WEEKOFYEAR(NOW())-1 AND DAYOFWEEK(dt) > 1 AND DAYOFWEEK(dt) < 6)
     )
  )
);


I know this is not the smartest way, but based on this, you can get a hint for a better solution.

Demo at sqlfiddle

+3


source






More articles:


All Articles