有一些解决方案，但我不知道如何改变，对于我的表。 所以我希望有人可以帮助我。
我有以下表
╔═════╦═════════╦════════════╦════════════╦═══════════╗
║ UID ║ TITLE ║ BEGIN ║ END ║ RECURRING ║
╠═════╬═════════╬════════════╬════════════╬═══════════╣
║ 1 ║ event A ║ 1359741600 ║ 1359745200 ║ none ║
║ 1 ║ event B ║ 1359741600 ║ 0 ║ daily ║
║ 1 ║ event C ║ 1359741600 ║ 0 ║ weekly ║
║ 1 ║ event D ║ 1359741600 ║ 0 ║ monthly ║
║ 1 ║ event E ║ 1359741600 ║ 0 ║ yearly ║
╚═════╩═════════╩════════════╩════════════╩═══════════╝
我怎么能现在从现在开始，选择每个事件多达7天，也是在未来7天所有的重复事件？
下面我试过，但没有工作非常好，没有完成。
SELECT
*
FROM
`tx_events_domain_model_event`
WHERE
/* none recurring events in the next 7 days */
(
recuring = 'none'
AND (begin_date + begin_time) >= UNIX_TIMESTAMP(NOW())
AND (end_date + end_time) <= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL 7 DAY))
)
OR
/* Daily */
recuring = 'daily'
OR
/* Weekly */
(
recuring = 'weekly'
AND DAYOFWEEK(NOW()) - 1 <= DAYOFWEEK(FROM_UNIXTIME(begin_date)) - 1
)
OR
/* Monthly */
(recuring = 'monthly'
AND
这里的东西我一直在玩（在这里是作为一个sqlfiddle一些样本数据）...不是100％肯定，但它应该抓住过去7天的数据。 请注意，我使用MySQL DATETIME与整数时间戳，但你应该能够转换，很容易（用于测试查询它更容易使用字符串日期）。
SELECT *
FROM
(SELECT
*,
CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', DAY(start)) AS monthly,
CONCAT(YEAR(NOW()), '-', MONTH(start), '-', DAY(start)) AS yearly
FROM events
) tmp
WHERE
(
(recurring = 'none')
OR (recurring = 'daily')
OR (recurring = 'weekly')
OR (
recurring = 'monthly'
AND (
(
monthly >= NOW()
AND monthly <= DATE_ADD(NOW(), INTERVAL 7 DAY)
)
OR (
DATE_ADD(monthly, INTERVAL 1 MONTH) >= NOW()
AND DATE_ADD(monthly, INTERVAL 1 MONTH) <= DATE_ADD(NOW(), INTERVAL 7 DAY)
)
)
)
OR (
recurring = 'yearly'
AND (
(
yearly >= NOW()
AND yearly <= DATE_ADD(NOW(), INTERVAL 7 DAY)
)
OR (
DATE_ADD(yearly, INTERVAL 1 YEAR) >= NOW()
AND DATE_ADD(yearly, INTERVAL 1 YEAR) <= DATE_ADD(NOW(), INTERVAL 7 DAY)
)
)
)
)
AND start <= NOW()
AND (
end IS NULL
OR end >= DATE_ADD(NOW(), INTERVAL 7 DAY)
)
这里是我的代码，我已经用了我的提醒表。
您只需将您的日期功能，它会找到你选择的日期是否有事件与否。 它会给这是经常性事件或非经常性事件的所有事件。
--
-- Table structure for table `reminder`
--
CREATE TABLE `reminder` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL,
`startdate` datetime NOT NULL,
`enddate` date NOT NULL,
`kind` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `reminder`
--
INSERT INTO `reminder` (`id`, `title`, `startdate`, `enddate`, `kind`) VALUES
(1, 'This is reminder', '2015-06-03 19:25:44', '2015-07-21', 'weekly');
-- Query data get results for 2015-06-17 from `reminder` table
SELECT CONCAT_WS(' ','2015-06-17',TIME(startdate)) AS ReminderDateTime, title
FROM reminder
WHERE IF(
kind='daily',
((UNIX_TIMESTAMP('2015-06-17') -UNIX_TIMESTAMP(DATE(startdate))) % (1*24*60*60)=0) ,
IF(
kind='weekly',
((UNIX_TIMESTAMP('2015-06-17') -UNIX_TIMESTAMP(DATE(startdate))) % (7*24*60*60)=0) ,
IF(
kind='monthly',
DAYOFMONTH('2015-06-17')=DAYOFMONTH(startdate),
IF(
kind='quarterly',
((UNIX_TIMESTAMP('2015-06-17') -UNIX_TIMESTAMP(DATE(startdate))) % (91*24*60*60)=0) ,
IF(
kind='yearly',
DAYOFYEAR('2015-06-17')=DAYOFYEAR(startdate),
IF(
kind='',
startdate,
'0000-00-00 00:00:00'
)
)
)
)
)
) AND DATE(startdate) <='2015-06-17' AND IF(enddate<>'0000-00-00',enddate>='2015-06-17','1') ORDER BY ReminderDateTime ASC
找到解决方案与其他程序员亚伦·汉森的帮助。 问题是夏令时。 下面是一个更新的查询...
SELECT
CONCAT_WS(
' ',
'2018-07-17',
TIME(start_time)
) AS ShiftDateTime
FROM
schedule
WHERE
IF(
repeat_type = 'daily',
'2018-07-17',
IF(
repeat_type = 'weekly',
DAYOFWEEK('2018-07-17') = DAYOFWEEK(start_date),
IF(
repeat_type = 'monthly',
DAYOFMONTH('2018-07-17') = DAYOFMONTH(start_date),
IF(
repeat_type = 'quarterly',
(
(
MONTH('2018-07-17') - MONTH(
DATE(start_date)
)
) % 3 = 0
),
IF(
repeat_type = 'yearly',
DAYOFYEAR('2018-07-17') = DAYOFYEAR(start_date),
IF(
repeat_type = NULL, start_date, '0000-00-00 00:00:00'
)
)
)
)
)
)
AND DATE(start_date) <= '2018-07-17'
AND IF(
repeat_end_date <> '0000-00-00', repeat_end_date >= '2018-07-17',
'1'
)
ORDER BY
ShiftDateTime ASC
