PHP/MySQL:对数据库中的重复事件进行建模，但查询日期范围 [英] PHP/MySQL: Model repeating events in a database but query for date ranges

问题描述

我正在开发一个(我打算成为)简单的PHP/MySQL应用程序.作为它的一部分，我希望能够对重复事件进行建模，但是我需要能够查询两个日期之间发生的所有事件(包括重复事件).这些事件只有一个日期，一天中的时间无关紧要.

I'm working on a (what I was intending to be) simple PHP/MySQL app. As part of it I'd like to be able to model repeating events, however I need to be able to query all the events that happened between two dates (including repeated events). The events only have a date, the time of day doesn't matter.

我一直在对此进行研究，并研究了各种方法，包括 Calendar重复/重复事件-最佳存储方法和重复日历事件和某些事件最终数学.

I've been researching this and have looked in to various approaches including Calendar Recurring/Repeating Events - Best Storage Method and Repeating calendar events and some final maths.

但是，我在网上找到的任何支持此功能的数据库模式示例似乎都仅支持查询某天发生的事件.不支持在一系列日期之间发生的事件.

However, any example of a database schema supporting this that I find online, only seems to support querying for events that happened on a certain day. There is no support for events that happened between a range of dates.

作为一个抽象示例

事件表(具有某种重复表示):

Events table (with some sort of repeat representation):

Event   | Start Date   |   Repeats
-------------------------------------
Meeting | 10/Dec/2012  |   Every 7 days
Lunch   | 10/Dec/2012  |   Every 1 days

目标抽象查询的结果SELECT Events BETWEEN 09/Dec/2012 AND 20/Dec/2012

Event   |  Date        |   Repeats
-------------------------------------
Meeting | 10/Dec/2012  |   Every 7 days
Meeting | 17/Dec/2012  |   Every 7 days
Lunch   | 10/Dec/2012  |   Every 1 days
Lunch   | 11/Dec/2012  |   Every 1 days
Lunch   | 12/Dec/2012  |   Every 1 days
Lunch   | 13/Dec/2012  |   Every 1 days
etc...
Lunch   | 20/Dec/2012  |   Every 1 days

是否存在支持此类查询的数据库架构?我该如何围绕该架构查询两天之间发生的任何事件(包括重复事件)?

Is there a database schema that will support these kind of queries? How would I go around making a query on that schema for any event (including repeating events) that happened between two days?

或者是用于重复事件的设计模式?

Or perhaps a design pattern that is used for repeating events?

推荐答案

我将创建一个只有一个名为id的列的统计表，并用0到500的数字填充该表.现在，我们可以轻松地使用它进行选择而不是使用while循环.

I would create a tally table with just one col called id and fill that table with numbers from 0 to 500. Now we easily use that to make selections instead of using a while loop.

Id
-------------------------------------
0
1
2
etc...

然后将事件存储在具有Name as varchar，startdate as datetime和repeats as int

Then i'd store the events in a table with Name as varchar, startdate as datetime and repeats as int

Name    | StartDate            |   Repeats
-------------------------------------
Meeting | 2012-12-10 00:00:00  |   7
Lunch   | 2012-12-10 00:00:00  |   1

现在，我们可以使用统计表通过以下方式选择两个日期之间的所有日期:

Now we can use the tally table to select all dates between two dates by using:

SELECT DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY) as showdate
FROM `tally`
WHERE (DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY)<='2012-12-20 00:00:00')
ORDER BY Id ASC

ShowDate
-------------------------------------
2012-12-09 00:00:00
2012-12-10 00:00:00
2012-12-11 00:00:00
2012-12-12 00:00:00
2012-12-13 00:00:00
2012-12-14 00:00:00
2012-12-15 00:00:00
2012-12-16 00:00:00
2012-12-17 00:00:00
2012-12-18 00:00:00
2012-12-19 00:00:00
2012-12-20 00:00:00

然后将其加入事件表中，以计算开始日期和显示日期之间的差.我们在repeats列中划分了结果，如果其余部分是0，则我们有匹配项.

Then we join this on the events table to calculate the difference between the startdate and the showdate. We devided the results of this by the repeats column and if the remainder is 0, we have match.

所有组合变为:

SELECT E.Id, E.Name, E.StartDate, E.Repeats, A.ShowDate, DATEDIFF(E.StartDate, A.ShowDate) AS diff
FROM events AS E, (
    SELECT DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY) as showdate
    FROM `tally`
    WHERE (DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY)<='2012-12-20 00:00:00')
    ORDER BY Id ASC
) a
WHERE MOD(DATEDIFF(E.StartDate, A.ShowDate), E.Repeats)=0
AND A.ShowDate>=E.StartDate

这将导致

Id  | Name       |StartDate             | Repeats   | ShowDate              | diff
---------------------------------------------------------------------------------
1   | Meeting    | 2012-12-10 00:00:00  | 7         | 2012-12-10 00:00:00   | 0
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-10 00:00:00   | 0
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-11 00:00:00   | -1
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-12 00:00:00   | -2
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-13 00:00:00   | -3
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-14 00:00:00   | -4
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-15 00:00:00   | -5
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-16 00:00:00   | -6
1   | Meeting    | 2012-12-10 00:00:00  | 7         | 2012-12-17 00:00:00   | -7
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-17 00:00:00   | -7
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-18 00:00:00   | -8
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-19 00:00:00   | -9
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-20 00:00:00   | -10

现在您可以(并且应该！)加快处理速度.例如，通过直接将日期存储在表中，这样您就可以直接选择所有日期，而不是将日期表与dateadd一起使用.您可以缓存且无需再次计算的每件事都是很好的.

Now you could (and should!) speed things up. For instance by directly storing dates in a table so you can just select all dates directly instead of using a tally table with dateadd. Every thing you can cache and dont have to calculate again is good.

这篇关于PHP/MySQL:对数据库中的重复事件进行建模，但查询日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！