dt614037527
dt614037527
2011-06-20 16:27

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

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;
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dragon7713 dragon7713 10年前

    How about using the google calendar API? google has support for recurring events of all kind and there's already a built in library in the zend framework. http://framework.zend.com/manual/en/zend.gdata.calendar.html

    We tried doing a custom made recurring events as well, and gave up on that after it was too buggy. The editing event was a nightmare to develop. We're switching to this API as soon as we can.

    点赞 评论 复制链接分享
  • duanlu5055 duanlu5055 10年前

    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.

    点赞 评论 复制链接分享