特定MySQL日期查询情况的最佳实践

Question

I need to implement a rather complex query based on several date options in Zend / MySQL and would be glad to get some advice from people who are not as stuck in my code as I am :)

I have a table "event_dates", with date descriptions that are tied to an event model in my app. Every event can have multiple event dates and those can have rather complex settings.

The things that are configurable and relevant to this question are the following:

• Fix date
• Date range (date_from, date_to)
• Date range based on "on specific weekdays"
• Date range based on "every n-th week" (every 2nd week, leaving one out every turn)
• Date range based on "each n-th weekday in a month" (second monday)
• and of course every combination of these.

I am trying to write down the queries before coding them, so I have a clear view on what is needed - but there are some combinations that I just can't figure out to process without pre-coding a list of possible dates.

How would you implement this - in theory, not asking for specific code here (yet :) ). Would you create a list of possible hits in PHP and then query them in an easier way, or would you rather do a complex query of twenty wheres and ands and so on?

Thanks for your time

• Arne

PS: This might be helpful ->

CREATE TABLE `event_dates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `events_id` int(11) NOT NULL,
  `datetime_from` datetime DEFAULT NULL,
  `datetime_to` datetime DEFAULT NULL,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `time_from` time DEFAULT NULL,
  `time_to` time DEFAULT NULL,
  `date_type` tinyint(4) NOT NULL DEFAULT '0',
  `frequency_in_weeks` tinyint(4) DEFAULT NULL,
  `nth_wday_in_month_week` tinyint(4) DEFAULT '0',
  `nth_wday_in_month_wday` tinyint(4) NOT NULL DEFAULT '0',
  `is_mondays` tinyint(1) NOT NULL DEFAULT '0',
  `is_tuesdays` tinyint(1) NOT NULL DEFAULT '0',
  `is_wednesdays` tinyint(1) NOT NULL DEFAULT '0',
  `is_thursdays` tinyint(1) NOT NULL DEFAULT '0',
  `is_fridays` tinyint(1) NOT NULL DEFAULT '0',
  `is_saturdays` tinyint(1) NOT NULL DEFAULT '0',
  `is_sundays` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Answer 1

I've gone down this road with a calendar application, and ended up deciding to store each instance of an event in the database.

For this part of the application, there were two tables... one to store event information (title, description, etc.), and one for each instance of it (id, start date/time, end date/time, etc.).

The downside is that events really must end at some point in the future, whether that is a month from now, or 50 years from now. You could pick an arbitrary date in the future for "infinitely recurring" events, but that depends on your application whether or not this is appropriate.

The upsides are plenty. You get fast access to dates/times of events without having to calculate it on the fly... it's already in the database! It makes the rest of your code very simple.

The route you choose is up to you, but I've found that by keeping it simple and fast, everyone was quite pleased.