finding events from previous Sunday to next Sunday

Question

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/11.

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

How can this be done?

score 2 · Answer 1 · answered May 18 '11 at 16:23

2

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

answered May 18 '11 at 16:23

Pentium10

204,586
122
423
502

If the range is supposed to be inclusive of both Sundays then you will need to account for that as well I believe. – Tom H May 18 '11 at 16:44
@Tom That's why I have the OR clause there. – Pentium10 May 18 '11 at 20:04
I don't have a mySQL DB handy, but the OR will only take effect if NOW() is on a certain day. I don't see how that will return an 8-day range in all cases. If Weekday(NOW()) <> 6 then you're only getting one week of dates. I would think that you would need an OR with respect to Weekday(eventdate). – Tom H May 18 '11 at 20:11

score 0 · Answer 2 · answered May 18 '11 at 20:27

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.

score 0 · Answer 3 · edited May 23 '17 at 11:55

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.

finding events from previous Sunday to next Sunday

3 Answers3