mySQL 重复事件查询

Question

有一些解决方案，但我不知道如何针对我的表格进行更改。所以我希望有人能帮助我。

我有下表

╔═════╦═════════╦════════════╦════════════╦═══════════╗
║ 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 

Answer 1

这是我一直在玩的东西(这里是一个 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)
    )