Created
April 9, 2015 01:14
-
-
Save jhartman86/155ade6f0203cc09dc49 to your computer and use it in GitHub Desktop.
time query with advanced repeaters
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SET @startDate='2015-03-01'; | |
| SET @dayLimit='7'; | |
| SET @calendarIDs='1,2'; | |
| SELECT | |
| _eventList._syntheticDate, | |
| _eventList.eventID, | |
| _eventList.eventTimeID, | |
| _eventList.computedStartUTC, | |
| _eventList.computedStartLocal, | |
| _eventList.computedEndUTC, | |
| _eventList.computedEndLocal, | |
| _eventList.useCalendarTimezone, | |
| _eventList.derivedTimezone, | |
| syntheticRepeater AS synthetic | |
| FROM | |
| ( | |
| SELECT | |
| _synthesized._syntheticDate, | |
| TIMESTAMP(_synthesized._syntheticDate, TIME(_events.startUTC)) AS computedStartUTC, | |
| # Weekly repeats can have their calculated local conversions | |
| # move to a day before or after the ordinal (eg. "tuesday") when | |
| # converting from UTC. If the converted local time is different than | |
| # the synthesized day, then we take the timestamp from the localized | |
| # conversion and apply it to the synthesized date (eg. 2015-01-01 + 'LOCALIZED-TIMESTAMP') | |
| # (CASE WHEN ( | |
| # _events.repeatTypeHandle = 'weekly') AND | |
| # (_synthesized._syntheticDate != DATE(CONVERT_TZ(TIMESTAMP(DATE(_synthesized._syntheticDate), TIME(_events.startUTC)), 'UTC', _events.derivedTimezone))) | |
| # IS TRUE THEN | |
| # TIMESTAMP(_synthesized._syntheticDate, TIME(CONVERT_TZ(TIMESTAMP(DATE(_synthesized._syntheticDate), TIME(_events.startUTC)), 'UTC', _events.derivedTimezone))) | |
| # ELSE | |
| # CONVERT_TZ(TIMESTAMP(DATE(_synthesized._syntheticDate), TIME(_events.startUTC)), 'UTC', _events.derivedTimezone) | |
| # END) AS computedStartLocal, | |
| CONVERT_TZ(TIMESTAMP(DATE(_synthesized._syntheticDate), TIME(_events.startUTC)), 'UTC', _events.derivedTimezone) AS computedStartLocal, | |
| TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE,_events.startUTC,_events.endUTC), TIMESTAMP(_synthesized._syntheticDate, TIME(_events.startUTC))) AS computedEndUTC, | |
| CONVERT_TZ(TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE,_events.startUTC,_events.endUTC), TIMESTAMP(_synthesized._syntheticDate, TIME(_events.startUTC))), 'UTC', _events.derivedTimezone) AS computedEndLocal, | |
| _events.*, | |
| (CASE WHEN (_synthesized._syntheticDate != DATE(_events.startUTC)) IS TRUE THEN 1 ELSE 0 END) as syntheticRepeater | |
| FROM ( | |
| SELECT DATE(@startDate + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) AS _syntheticDate | |
| FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a | |
| CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b | |
| CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c | |
| LIMIT 120 | |
| ) AS _synthesized | |
| # 3 way join events, eventtimes, and eventtimeweekdays - creates a HUGE matrix | |
| JOIN ( | |
| SELECT | |
| sev.id AS eventID, | |
| sec.id AS calendarID, | |
| sevt.id AS eventTimeID, | |
| sev.title, | |
| sev.useCalendarTimezone, | |
| (CASE WHEN (sev.useCalendarTimezone = 1) IS TRUE THEN sec.defaultTimezone ELSE sev.timezoneName END) as derivedTimezone, | |
| sev.eventColor, | |
| sev.ownerID, | |
| sev.fileID, | |
| sevt.startUTC, | |
| sevt.endUTC, | |
| sevt.isOpenEnded, | |
| sevt.isAllDay, | |
| sevt.isRepeating, | |
| sevt.repeatTypeHandle, | |
| sevt.repeatEvery, | |
| sevt.repeatIndefinite, | |
| sevt.repeatEndUTC, | |
| sevt.repeatMonthlyMethod, | |
| sevt.repeatMonthlySpecificDay, | |
| sevt.repeatMonthlyOrdinalWeek, | |
| sevt.repeatMonthlyOrdinalWeekday, | |
| sevtwd.repeatWeeklyday | |
| FROM SchedulizerCalendar sec | |
| JOIN SchedulizerEvent sev ON sev.calendarID = sec.id | |
| JOIN SchedulizerEventTime sevt ON sevt.eventID = sev.id | |
| LEFT JOIN SchedulizerEventTimeWeekdays sevtwd ON sevtwd.eventTimeID = sevt.id | |
| WHERE sev.calendarID in (1,7) | |
| ) AS _events | |
| # Repeat Stuff | |
| WHERE(_events.isRepeating = 1 | |
| AND (_events.repeatIndefinite = 1 OR (_synthesized._syntheticDate <= _events.repeatEndUTC AND _events.repeatIndefinite = 0)) | |
| AND (DATE(_events.startUTC) <= _synthesized._syntheticDate) | |
| AND (_events.eventTimeID NOT IN (SELECT _nullifiers.eventTimeID FROM SchedulizerEventTimeNullify _nullifiers WHERE _synthesized._syntheticDate = DATE(_nullifiers.hideOnDate))) | |
| AND ( | |
| (_events.repeatTypeHandle = 'daily' | |
| AND (DATEDIFF(_synthesized._syntheticDate,_events.startUTC) % _events.repeatEvery = 0 ) | |
| ) | |
| OR (_events.repeatTypeHandle = 'weekly' | |
| AND (_events.repeatWeeklyday = DAYOFWEEK(_synthesized._syntheticDate)) | |
| AND (CEIL(DATEDIFF(_events.startUTC, _synthesized._syntheticDate) / 7 ) % _events.repeatEvery = 0) | |
| ) | |
| OR ((_events.repeatTypeHandle = 'monthly' AND _events.repeatMonthlyMethod = 'specific') | |
| AND (_events.repeatMonthlySpecificDay = DAYOFMONTH(_synthesized._syntheticDate)) | |
| AND ((MONTH(_synthesized._syntheticDate) - MONTH(_events.startUTC)) % _events.repeatEvery = 0) | |
| ) | |
| OR ((_events.repeatTypeHandle = 'monthly' AND _events.repeatMonthlyMethod = 'ordinal') | |
| AND ((DATE_ADD(DATE_SUB(LAST_DAY(_synthesized._syntheticDate), INTERVAL DAY(LAST_DAY(_synthesized._syntheticDate)) -1 DAY), INTERVAL (((_events.repeatMonthlyOrdinalWeekday + 7) - DAYOFWEEK(DATE_SUB(LAST_DAY(_synthesized._syntheticDate), INTERVAL DAY(LAST_DAY(_synthesized._syntheticDate)) -1 DAY))) % 7) + ((_events.repeatMonthlyOrdinalWeek * 7) -7) DAY)) = _synthesized._syntheticDate) | |
| AND ((MONTH(_synthesized._syntheticDate) - MONTH(_events.startUTC)) % _events.repeatEvery = 0) | |
| ) | |
| OR(_events.repeatTypeHandle = 'yearly' | |
| AND ((YEAR(_synthesized._syntheticDate) - YEAR(_events.startUTC)) % _events.repeatEvery = 0) | |
| ) | |
| ) | |
| ) | |
| # Individuals | |
| OR( | |
| (_events.isRepeating = 0 AND _synthesized._syntheticDate = DATE(_events.startUTC)) | |
| ) | |
| ) AS _eventList; | |
| # WORKING PERFECTLY IF NEED TO ROLL BACK; THIS IS BEFORE SETTING UP | |
| # AUTOMATICALLY DERIVED TIMEZONES | |
| # SELECT | |
| # _eventList._syntheticDate, | |
| # _eventList.eventID, | |
| # _eventList.eventTimeID, | |
| # _eventList.computedStartUTC, | |
| # _eventList.computedStartLocal, | |
| # _eventList.computedEndUTC, | |
| # _eventList.computedEndLocal, | |
| # _eventList.useCalendarTimezone, | |
| # _eventList.timezoneName, | |
| # syntheticRepeater AS synthetic | |
| # FROM | |
| # ( | |
| # SELECT | |
| # _synthesized._syntheticDate, | |
| # TIMESTAMP(_synthesized._syntheticDate, TIME(_events.startUTC)) AS computedStartUTC, | |
| # CONVERT_TZ(TIMESTAMP(DATE(_synthesized._syntheticDate), TIME(_events.startUTC)), 'UTC', _events.timezoneName) AS computedStartLocal, | |
| # TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE,_events.startUTC,_events.endUTC), TIMESTAMP(_synthesized._syntheticDate, TIME(_events.startUTC))) AS computedEndUTC, | |
| # CONVERT_TZ(TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE,_events.startUTC,_events.endUTC), TIMESTAMP(_synthesized._syntheticDate, TIME(_events.startUTC))), 'UTC', _events.timezoneName) AS computedEndLocal, | |
| # _events.*, | |
| # (CASE WHEN (_synthesized._syntheticDate != DATE(_events.startUTC)) IS TRUE THEN 1 ELSE 0 END) as syntheticRepeater | |
| # FROM ( | |
| # SELECT DATE(@startDate + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) AS _syntheticDate | |
| # FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a | |
| # CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b | |
| # CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c | |
| # LIMIT 120 | |
| # ) AS _synthesized | |
| # # 3 way join events, eventtimes, and eventtimeweekdays - creates a HUGE matrix | |
| # JOIN ( | |
| # SELECT | |
| # sev.id AS eventID, | |
| # sev.calendarID, | |
| # sevt.id AS eventTimeID, | |
| # sev.title, | |
| # sev.useCalendarTimezone, | |
| # sev.timezoneName, | |
| # sev.eventColor, | |
| # sev.ownerID, | |
| # sev.fileID, | |
| # sevt.startUTC, | |
| # sevt.endUTC, | |
| # sevt.isOpenEnded, | |
| # sevt.isAllDay, | |
| # sevt.isRepeating, | |
| # sevt.repeatTypeHandle, | |
| # sevt.repeatEvery, | |
| # sevt.repeatIndefinite, | |
| # sevt.repeatEndUTC, | |
| # sevt.repeatMonthlyMethod, | |
| # sevt.repeatMonthlySpecificDay, | |
| # sevt.repeatMonthlyOrdinalWeek, | |
| # sevt.repeatMonthlyOrdinalWeekday, | |
| # sevtwd.repeatWeeklyday | |
| # FROM SchedulizerEvent sev | |
| # JOIN SchedulizerEventTime sevt ON sevt.eventID = sev.id | |
| # LEFT JOIN SchedulizerEventTimeWeekdays sevtwd ON sevtwd.eventTimeID = sevt.id | |
| # WHERE sev.calendarID in (1,7) | |
| # ) AS _events | |
| # # Repeat Stuff | |
| # WHERE(_events.isRepeating = 1 | |
| # AND (_events.repeatIndefinite = 1 OR (_synthesized._syntheticDate <= _events.repeatEndUTC AND _events.repeatIndefinite = 0)) | |
| # AND (DATE(_events.startUTC) <= _synthesized._syntheticDate) | |
| # AND (_events.eventTimeID NOT IN (SELECT _nullifiers.eventTimeID FROM SchedulizerEventTimeNullify _nullifiers WHERE _synthesized._syntheticDate = DATE(_nullifiers.hideOnDate))) | |
| # AND ( | |
| # (_events.repeatTypeHandle = 'daily' | |
| # AND (DATEDIFF(_synthesized._syntheticDate,_events.startUTC) % _events.repeatEvery = 0 ) | |
| # ) | |
| # | |
| # OR (_events.repeatTypeHandle = 'weekly' | |
| # AND (_events.repeatWeeklyday = DAYOFWEEK(_synthesized._syntheticDate)) | |
| # AND (CEIL(DATEDIFF(_events.startUTC, _synthesized._syntheticDate) / 7 ) % _events.repeatEvery = 0) | |
| # ) | |
| # | |
| # OR ((_events.repeatTypeHandle = 'monthly' AND _events.repeatMonthlyMethod = 'specific') | |
| # AND (_events.repeatMonthlySpecificDay = DAYOFMONTH(_synthesized._syntheticDate)) | |
| # AND ((MONTH(_synthesized._syntheticDate) - MONTH(_events.startUTC)) % _events.repeatEvery = 0) | |
| # ) | |
| # | |
| # OR ((_events.repeatTypeHandle = 'monthly' AND _events.repeatMonthlyMethod = 'ordinal') | |
| # AND ((DATE_ADD(DATE_SUB(LAST_DAY(_synthesized._syntheticDate), INTERVAL DAY(LAST_DAY(_synthesized._syntheticDate)) -1 DAY), INTERVAL (((_events.repeatMonthlyOrdinalWeekday + 7) - DAYOFWEEK(DATE_SUB(LAST_DAY(_synthesized._syntheticDate), INTERVAL DAY(LAST_DAY(_synthesized._syntheticDate)) -1 DAY))) % 7) + ((_events.repeatMonthlyOrdinalWeek * 7) -7) DAY)) = _synthesized._syntheticDate) | |
| # AND ((MONTH(_synthesized._syntheticDate) - MONTH(_events.startUTC)) % _events.repeatEvery = 0) | |
| # ) | |
| # | |
| # OR(_events.repeatTypeHandle = 'yearly' | |
| # AND ((YEAR(_synthesized._syntheticDate) - YEAR(_events.startUTC)) % _events.repeatEvery = 0) | |
| # ) | |
| # ) | |
| # ) | |
| # # Individuals | |
| # OR( | |
| # (_events.isRepeating = 0 AND _synthesized._syntheticDate = DATE(_events.startUTC)) | |
| # ) | |
| # ) AS _eventList; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment