Search code examples
sqlmysqlstored-proceduresrecurrencedatetime-generation

3rd <day_of_week> of the Month - MySQL

I'm working on a recurrence application for events. I have a date range of say, January 1 2010 to December 31 2011. I want to return all of the 3rd Thursdays (arbitrary) of the each month, efficiently. I could do this pretty trivially in code, the caveat is that it must be done in a stored procedure. Ultimately I'd want something like:

CALL return_dates(event_id);

That event_id has a start_date of 1/1/2010 and end_date of 12/31/2011. Result set would be something like:

1/20/2010
2/14/2010
3/17/2010
4/16/2010
5/18/2010
etc.

I'm just curious what the most efficient method of doing this would be, considering I might end up with a very large result set in my actual usage.

Solution

  • One idea that comes to mind - you can create a table and store the dates you're interested in there.