You could try
SELECT * FROM date JOIN
(SELECT CONVERT_TZ('2013-11-01 00:00:00','GMT','UTC') AS w1start,
CONVERT_TZ('2013-11-07 23:59:59','GMT','UTC') AS w1end,
CONVERT_TZ('2013-11-08 00:00:00','GMT','UTC') AS w2start,
CONVERT_TZ('2013-11-15 23:59:59','GMT','UTC') AS w2end,
CONVERT_TZ('2013-11-16 00:00:00','GMT','UTC') AS w3start,
CONVERT_TZ('2013-11-22 23:59:59','GMT','UTC') AS w3end) AS weeks
WHERE date BETWEEN w1start AND w1end
OR date BETWEEN w2start AND w2end
OR date BETWEEN w3start AND w3end
ORDER BY date DESC
This is at least a start.
If, however, you want to tell the application which of the 3 conditions is fulfilled, yiu might want to do
SELECT date BETWEEN w1start AND w1end AS firstweek,
date BETWEEN w2start AND w2end AS secondweek,
date BETWEEN w3start AND w3end AS thirdweek,
whatevercolumsyouneed
FROM date JOIN
(SELECT CONVERT_TZ('2013-11-01 00:00:00','GMT','UTC') AS w1start,
CONVERT_TZ('2013-11-07 23:59:59','GMT','UTC') AS w1end,
CONVERT_TZ('2013-11-08 00:00:00','GMT','UTC') AS w2start,
CONVERT_TZ('2013-11-15 23:59:59','GMT','UTC') AS w2end,
CONVERT_TZ('2013-11-16 00:00:00','GMT','UTC') AS w3start,
CONVERT_TZ('2013-11-22 23:59:59','GMT','UTC') AS w3end) AS weeks
HAVING firstweek OR secondweek OR thirdweek
ORDER BY date DESC
(untested; I am not sure if HAVING
is placed correctly.)