mySQL重复事件查询
mySQL重复事件查询
提问于 2013-01-09 15:39:11
有一些解决方案，但我不知道如何为我的表更改它。所以我希望有人能帮助我。
我有下面的表格
╔═════╦═════════╦════════════╦════════════╦═══════════╗
║ 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 回答 2
Stack Overflow用户
发布于 2013-01-14 11:32:16
这是我一直在尝试的东西(这里是一个带有一些示例数据的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)
)Stack Overflow用户
发布于 2018-07-18 04:02:29
在同行程序员Aaron Hanson的帮助下找到了解决方案。问题出在夏令时。这是一个更新的查询...
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14230380
复制相关文章
相似问题