I can query my items from this week using:
SELECT * FROM data WHERE YEARWEEK(receivedOn, 1) = YEARWEEK(CURDATE(), 1)
But I would like to fetch items from previous week, or previous previous week. How can I do it?
Something like this (for previous week):
YEARWEEK(receivedOn - 1 * week, 1) = YEARWEEK(CURDATE() - 1 * week, 1)
for previous previous week
YEARWEEK(receivedOn - 2 * week, 1) = YEARWEEK(CURDATE() - 2 * week, 1)
Thank you
Your original query, and all answers that were given, applies a date function on the stored column. As Gordon Linoff points out, this is inefficient because it cannot take advantage of an index on the column.
I am going to suggest using direct filtering instead. You can get the beginning of the current week (starting Monday, as in your original code) with the following expression:
current_date - interval weekday(current_date) day
From there on, here is how to filter on dates that belong to the current week:
select *
from data
where receivedon >= current_date - interval weekday(current_date) day
and receivedon < current_date + interval 7 - weekday(current_date) day
If you want to filter on the previous week:
where receivedon >= current_date - interval weekday(current_date) + 7 day
and receivedon < current_date + interval weekday(current_date) day
And on the the week before that:
where receivedon >= current_date - interval weekday(current_date) + 14 day
and receivedon < current_date + interval weekday(current_date) + 7 day