1

So, I'm trying to build upon the schema and queries presented in

Calendar Recurring/Repeating Events - Best Storage Method

The main limitation above is the queries only return events for a specific date. I need a query to an arbitrary date range (separate query for each date in range is unacceptable)

My schema:

CREATE TABLE `events` (  
    `event_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(128) NOT NULL,
    `description` varchar(1000) DEFAULT NULL,
    `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`event_id`),
) ENGINE=InnoDB AUTO_INCREMENT=4268 DEFAULT CHARSET=utf8

CREATE TABLE `events_dates` (
    `events_date_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `event_id` bigint(20) unsigned NOT NULL,
    `date_start` date NOT NULL,
    `date_end` date NOT NULL,
    `time_start` time DEFAULT NULL,
    `time_end` time DEFAULT NULL,
    `repeat_interval` int(11) DEFAULT NULL COMMENT 'simple repetition:  1: repeat every x days, 2: every-other day, 7: every-7 days',
    `repeat_year` smallint(4) DEFAULT NULL COMMENT 'NULL ("*") or year',
    `repeat_month` tinyint(2) DEFAULT NULL COMMENT 'NULL ("*") or month (1-12)',
    `repeat_day` tinyint(2) DEFAULT NULL COMMENT 'NULL ("*") or day of month (1-31)',
    `repeat_nth_day` tinyint(1) DEFAULT NULL COMMENT 'use in combination with repeat_weekday',
    `repeat_weekday` tinyint(1) DEFAULT NULL COMMENT 'NULL ("*") or 1=Sunday, 7=Saturday',
    PRIMARY KEY (`events_date_id`),
    KEY `event_id` (`event_id`),
    CONSTRAINT `events_dates_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4268 DEFAULT CHARSET=utf8

This date will repeat on the 4th Thursday of every month in 2016

`date_start` : "2016-01-01"
`date_end` : "2016-12-31"
`time_start` : NULL,
`time_end` : NULL,
`repeat_interval` : NULL
`repeat_year` : NULL
`repeat_month` : NULL
`repeat_day` : NULL
`repeat_nth_day` : 4
`repeat_weekday` : 5

And a query to get events occurring on a single date:

CREATE DEFINER=`root`@`localhost` PROCEDURE `events_get_date`(_date DATE)
BEGIN

    DECLARE _year INT DEFAULT YEAR(_date);
    DECLARE _month INT DEFAULT MONTH(_date);
    DECLARE _day INT DEFAULT DAYOFMONTH(_date);
    DECLARE _nth_day INT DEFAULT 1 + floor((DAYOFMONTH(_date) - 1) / 7);
    DECLARE _weekday INT DEFAULT DAYOFWEEK(_date);

    SELECT e.*,
        ed.`date_start`,
        ed.`date_end`,
        ed.`time_start`,
        ed.`time_end`
    FROM `events` e
    JOIN `events_dates` ed ON ed.`event_id` = e.`event_id`
    WHERE
        (
            (`date_start` <= _date) AND
            (`date_end` >= _date) AND
            (DATEDIFF(_date, `date_start`) % `repeat_interval` = 0)
        ) OR
        (
            (`date_start` <= _date) AND
            (`date_end` >= _date) AND
            (`repeat_year` IS NULL OR `repeat_year` = _year) AND
            (`repeat_month` IS NULL OR `repeat_month` = _month) AND
            (`repeat_day` IS NULL OR `repeat_day` = _day) AND
            (`repeat_nth_day` IS NULL OR `repeat_nth_day` = _nth_day) AND
            (`repeat_weekday` IS NULL OR `repeat_weekday` = _weekday)
        )
    GROUP BY e.`event_id`;

END

I'm stuck coming up with a procedure to return events that occur within a date range

In particular if a event utilizes repeat_nth_day and repeat_weekday...
for example: repeat_nth_day = 4 and repeat_weekday = 5 (4th Thursday of the month). Trivial when querying a single date, but I have no idea how to do it for a date range.

Here's what I've got so far:

CREATE DEFINER=`root`@`localhost` PROCEDURE `_events_filter_get_range`(_date_start DATE, _date_end DATE)
BEGIN

    DECLARE _dom_start INT DEFAULT DAYOFMONTH(_date_start);
    DECLARE _dom_end INT DEFAULT DAYOFMONTH(_date_end);
    DECLARE _month_start INT DEFAULT MONTH(_date_start);
    DECLARE _month_end INT DEFAULT MONTH(_date_end);
    DECLARE _year_start INT DEFAULT YEAR(_date_start);
    DECLARE _year_end INT DEFAULT YEAR(_date_end);
    DECLARE _day_diff INT DEFAULT DATEDIFF(_date_end, _date_start);
    DECLARE _month_diff INT DEFAULT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM _date_end), EXTRACT(YEAR_MONTH FROM _date_start));
    DECLARE _year_diff INT DEFAULT _year_end - _year_start;
    #DECLARE _nth_day INT DEFAULT 1 + floor((DAYOFMONTH(_date) - 1) / 7);
    #DECLARE _weekday INT DEFAULT DAYOFWEEK(_date);

    SELECT
        e.*,
        ed.`date_start`,
        ed.`date_end`,
        ed.`time_start`,
        ed.`time_end`
    FROM `events` e
    JOIN `events_dates` ed ON ed.`event_id` = e.`event_id`
    WHERE
        (
            (`date_start` <= _date_end) AND
            (`date_end` >= _date_start) AND
            (ABS(DATEDIFF(_date_end, `date_start`)) % `repeat_interval` <= _day_diff)
        ) OR
        (
            (`date_start` <= _date_end) AND
            (`date_end` >= _date_start) AND
            (`repeat_year` IS NULL OR
                `repeat_year` BETWEEN _year_start AND _year_end) AND
            (`repeat_month` IS NULL OR
                (_month_diff >= 11) OR
                (_year_diff = 0 AND `repeat_month` BETWEEN _month_start AND _month_end) OR
                # Dec 2015 - Jan 2015
                (_year_diff = 1 AND (`repeat_month` <= _month_end OR `repeat_month` >= _month_start))
            ) AND
            (`repeat_day` IS NULL OR
                (_month_diff > 1) OR
                # Jan 25 - Feb 26
                (_month_diff = 1 AND _dom_start < _dom_end) OR
                # Jan 25 - Feb 5
                (_month_diff = 1 AND _dom_start > _dom_end AND (`repeat_day` <= _dom_end OR `repeat_day` >= _dom_start)) OR
                # Jan 25 - Jan 26
                (_month_diff = 0 AND _dom_start < _dom_end AND `repeat_day` BETWEEN _dom_start AND _dom_end)
            )
            /*
                Here's where I'm stuck..
               How do I check if a date range contains a
               4th Thursday of the month (repeat_nth_day = 4, repeat_weekday = 5)
            */
            /*
            (`repeat_nth_day` IS NULL OR `repeat_nth_day` = _nth_day) AND
            (`repeat_weekday` IS NULL OR `repeat_weekday` = _weekday)
            */
        )
    GROUP BY e.`event_id`;

END

Is it possible?
Suggestions?
Thanks.

EDIT looks like most of my attempt is flawed..

`date_start` : "2016-01-01"
`date_end` : "2017-12-31"
`time_start` : NULL,
`time_end` : NULL,
`repeat_interval` : NULL
`repeat_year` : NULL
`repeat_month` : 2
`repeat_day` : 1
`repeat_nth_day` : NULL
`repeat_weekday` : NULL

Repeats every Feb 1 however it would get returned in the range 2016-02-15 - 2016-03-15 because the range includes Feb and includes the 1st (of March)... but does not include the 1st of Feb... :(

Community
  • 1
  • 1
Brad Kent
  • 4,100
  • 2
  • 18
  • 24

1 Answers1

0

I came up with an solution that involves 2 things that I don't have a lot of experience with: temporary tables and loops. I iterate over the date range and insert each dates results into a temporary table

REPEAT
    # events_filter_date inserts into the temporary table
    CALL events_filter_date(_date_cur);
    SET _date_cur = DATE_ADD(_date_cur, INTERVAL 1 DAY);
UNTIL _date_cur > _date_end
END REPEAT;

SELECT *
FROM `events_temp`;
Brad Kent
  • 4,100
  • 2
  • 18
  • 24