MySQL - expanding a date range into individual dates

Question

I have a table with a start date and repeat counts (simplified):

CREATE TABLE tblSchedule (
  schedStart timestamp,
  schedRepeatMonth tinyint,
  schedRepeatDays  tinyint,
  schedOpen bit
);

So, in a simplifed example, "open every Tuesday" would have a start date on a Tuesday and repeat every 7 days. The third Friday would start on a Friday and repeat once a month and 21 days. Closed on Christmas would have a date of Dec. 25th, no repeats, closed.

There are two types of queries it has to handle - "are we open on this date" and "how many times are we open between these dates". If the table were populated without repeats, these would be totally mundane queries and what I would like to do is create a select that produces rows as if they had been entered long-hand without repeats ie:

SELECT * from (
    SELECT schedStart, schedEnd from tblSchedule
     where schedStart between ('2001-01-01', '2001-12-31')
     and *expand_ranges_into_non_repeating_rows_here*)
)

The trouble is that SQL is about filtering information out, not expanding it so creating several rows from a single one is not exactly normal. I could use a while loop to create a temporary table but this is the schedule for multiple items across the entire system so it's going to be commonly run a lot, albeit with some date ranges being more common than others.

Any ideas? Bonus if we can keep the solution as standard-sql as possible.

Edit: I suppose the question could be further simplified and abstracted to "How can I create a sequence of rows without using a table."

There are quite a few similar questions already on SO, if you search Google for `recurring events SQL` or similar. One of these might help you. E.g. https://stackoverflow.com/questions/14230380/mysql-repeating-events-query, https://stackoverflow.com/questions/9277834/recurring-events-sql-query — fubar, Feb 19 '18 at 22:37
Both of these (and all those I've found) return whether or not a single repeat event occurs, not how many times and, just as importantly, when. It's the expansion of a single repeating row into multiple single-shot events within a query (ie not joining with an actual, physical table) that I'm trying to solve. Does give me some food for thought, though. — Mike, Feb 19 '18 at 23:03
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 19 '18 at 23:37

score 0 · Answer 1 · answered Feb 22 '18 at 00:18

Hmm. I think I withdraw the question. I've tried recursive views, some of which might be possible using the new cte feature of MySQL but the calculations involved were getting too time consuming (eg modulo on day-of-week making indexes useless). I've decided to go with expanding them into a temporary table at the start of the run.

I would delete the question but I think failed attempts are just as useful as successful ones. If anyone wants to continue this discussion, feel free.

MySQL - expanding a date range into individual dates

1 Answers1