mySQL重复事件查询 [英] mySQL Repeating Events Query

问题描述

有一些解决方案，但是我不知道如何更改我的桌子.所以我希望有人可以帮助我.

There are some solutions, but i don't know how to change that for my table. So i hope someone could help me.

我有下表

╔═════╦═════════╦════════════╦════════════╦═══════════╗
║ 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天的所有重复发生的事件?

How can i now select every event from now on up to 7 days and also all recurring events in the next 7 days?

以下是我尝试过的方法，但效果不佳且未完成.

The following i've tried but not working very good and not finished.

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 

推荐答案

这是我一直在玩的东西(这里是

Here's something I've been playing with (and here it is as an sqlfiddle with some sample data)... not 100% sure about it, but it should grab the last 7 days of data. Note that I'm using MySQL DATETIME versus integer timestamps, but you should be able to convert that easily (for testing the query it was much easier to use string dates).

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)
    )

这篇关于mySQL重复事件查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！