extract last week data (and not last 7 days)

Question

I have data with below format.

id     - autoincrement
myDate - timestamp

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

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

I know you would say, week starts from Sunday, but as I am working in Arab countries, here Friday is OFF and work starts from Saturday.

I know for last 7 days it would be just below code

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

Data at sqlfiddle

The condition would be opposite; lower value comes first for the `BETWEEN` syntax. ;) — hjpotter92, Feb 12 '13 at 09:23
`where week(myDate) = week(now()-1) and year(myDate) = year(now())` for normal weeks. — जलजनक, Feb 12 '13 at 09:24
http://stackoverflow.com/questions/10656996/week-of-the-year-for-weeks-starting-with-saturday duplicate of this? — duffy356, Feb 12 '13 at 09:27
@DoSparKot : World week start with Sunday and my week start with saturday — Fahim Parkar, Feb 12 '13 at 09:30
and this [MySQL - Saturday as first day of Week?](http://stackoverflow.com/questions/3687119/is-it-possible-to-set-mysql-to-have-a-saturday-as-the-start-of-the-week) — जलजनक, Feb 12 '13 at 09:30
@SalmanA : I can do this in Java, but I wanted to do this in MySQL as I would be using same query for my mobile application where I am using PHP. — Fahim Parkar, Feb 12 '13 at 11:23

Minesh · Answer 1 · 2013-02-13T04:04:46.147

3

Can you try below method to compute 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 might get hint for better solution.

Demo at sqlfiddle

edited Feb 13 '13 at 04:04

answered Feb 12 '13 at 09:51

Minesh

2,284
1
14
22

with your query I am getting data from monday to saturday. I want it from saturday to friday. :( – Fahim Parkar Feb 12 '13 at 11:26
@FahimParkar Yes I was working on while SqlFiddle went down, I have updated the SQL and it went down again, however it is close, it shall get you started though, please see I have hard-coded date to check the various scenario, but you can test that once done, replace hard-coded '2013-02-10' with NOW() function – Minesh Feb 12 '13 at 12:45
can you have sqlfiddle solution with `NOW()`? – Fahim Parkar Feb 12 '13 at 13:39
@FahimParkar It is there with NOW() option but You are expected to change yourself to test :) – Minesh Feb 12 '13 at 15:25

extract last week data (and not last 7 days)

Data at sqlfiddle

1 Answers1

Demo at sqlfiddle