Get items from next week

I was starting to do a query such as:

SELECT * 
FROM mytable
WHERE (weekofyear(now())+1) <= date_inserted < weekofyear(now()+2)

The problem with this is that items from previous years will also show up here. What would be the best way to do this query — I’m hoping I can still use an index on the query on the date_inserted field, which is why I’m asking this here

Answer

I would phrase this as:

where
        date_inserted >=  current_date + interval 7 - weekday(current_date) day
    and date_inserted <  current_date + interval 14 - weekday(current_date) day

Expression current_date + interval 7 - weekday(current_date) day gives you the first day of next week. This works under the assumptions that your weeks start on Monday.

You can see that no date function is applied on the date_inserted column, which mean that MySQL would happily use an index on that column for filtering, if any.