finding events from previous Sunday to next Sunday

How can you find MySQL data for the current week plus the following Sunday?

Given a date (e.g. Wednesday 5/18/11), it would show events from the previous Sunday to the next Sunday. 5/15/11 through 5/22/1开发者_运维问答1.

The trick would be to find the 'previous' Sunday to a given date.

How can this be done?

SELECT * 
FROM   events 
WHERE  Yearweek(`eventdate`) = Yearweek(NOW()) 
        OR ( Weekday(NOW()) = 6 
             AND Yearweek(`eventdate`) = Yearweek( 
                 DATE_SUB(NOW(), INTERVAL 1 DAY)) ) 

Taking from Pentium's answer, with some adjustments...

SELECT
    *
FROM
    Events
WHERE
    YEARWEEK(`eventdate`) = YEARWEEK(NOW()) OR
    (
        WEEKDAY(`eventdate`) = 6 AND
        YEARWEEK(`eventdate`) = YEARWEEK(NOW()) + 1
    )

This may need to be adjusted depending on the values for WEEKDAY (is 6 Sunday?).

Also, while this should work, my guess is that mySQL won't be able to use any indexes on the eventdate column with this method. It's probably better to find the actual dates themselves for the bordering Sundays and then do a BETWEEN or <= >=. This should allow the use of an index on the eventdate. Even if you don't have an index on it now, you might want to use one in the future.

Using a calendar table . . .

select cal_date
from calendar
where cal_date between 
                 (select max(cal_date) from calendar
                  where cal_date <= '2011-05-15' and day_of_week = 'Sun') and
                 (select min(cal_date) from calendar
                  where cal_date > '2011-05-15' and day_of_week = 'Sun') 

It's not clear what you want if the given date is a Sunday. This previous query returns 15 rows given a date that falls on Sunday. It returns 8 rows for all other days. You can tweak the comparison operators in the WHERE clause to get the behavior you want.

I posted code for a calendar table earlier on SO. It's for PostgreSQL, but you should be able to adapt it to MySQL without much trouble.