当前位置：文江博客话题详情

用于查询重复日历事件的 SQLite 语句

发布于 2024-11-28 01:02:41 字数 1182 浏览 3 评论 0原文

我正在设计一个日历应用程序，它重复无、每日、每周、每月和每周一次。每年。我的要求之一是“两个事件不应重叠” 我存储数据的表的名称

活动

字段

dtstart - 事件开始时间
dtend - 事件结束时间

考虑以下两种情况，

活动 1 8 月 15 日下午 3:00 - 4:00 重复 - 无
活动 2 8 月 15 日下午 2:00 - 下午 5-00 重复 - 无

在上述情况下，下面的 SQL 查询就像魅力一样

String sqlQuery = "从 dtstart 和 dtend 的事件中选择 * %d 和 %d 之间”；
sqlQuery = String.format(sqlQuery, dtstart, dtend);

现在，考虑情况二。

活动 1 8 月 15 日下午 3:00 - 下午 4:00 重复 - 每天直至 8 月 20 日
活动 2 8 月 18 日下午 2:00 - 下午 5-00 重复 - 无

情况二我的 sqlQuery 失败，因为它检查同一日期（8 月 18 日）的事件开始和结束时间。就我而言，我的查询应显示 8 月 15 日的冲突时间。

请帮助我进行 SQL 查询，以便检查重复发生的事件。

在事件表中，我存储开始时间、结束时间、上次日期发生次数和发生类型。

数据库方案如下

表名称：事件
标题 | dtstart | dtend | 重复类型 | 最后一次出现

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

嗳卜坏 2024-12-05 01:02:41

我假设您想要检测单个新插入（或更新）的事件是否有重叠（不是数据库中已有的任何事件是否有重叠），对吗？

如果是这样，您可以根据新插入事件的“重复类型”以程序方式（以客户端语言）生成所有开始/结束间隔 [s, e]，然后对每个事件执行以下查询这些间隔来检测重叠（我在这里使用 Oracle 语法，我假设 SQLite 类似）：

-- A time interval must be either completely "to the left" or completely
-- "to the right" of the other time interval for them not to overlap.
SELECT * FROM EVENT
WHERE
    NOT(
        (:s < DTSTART AND :s < DTEND AND :e < DTSTART AND :e < DTEND)
        OR (:s > DTSTART AND :s > DTEND AND :e > DTSTART AND :e > DTEND)
    )

不过，不要期望出色的性能（特别是如果您的事件有大量重复，或者如果 DTSTART/DTEND 没有索引或 SQLite 已索引）无法正确利用该指数）。

为了提高性能，您可能最好将所有事件缓存在内存中并在客户端进行所有处理，这将使您可以更轻松地使用启发式方法来“短路”某些处理。例如：

如果两个事件具有相同的“重复类型”，您可以只比较它们的初始间隔，而不必担心重复 - 如果它们最初不匹配，则它们永远不会匹配。
如果一个事件的“最后一次发生”早于其他事件（甚至不是“dtstart”），则无论“重复类型”如何，它们都永远无法匹配。
等等...

如果您确实想要所有处理数据库端并且您想要（查询）性能，您可能正在考虑某种地理空间/多维索引，并且您需要实际存储数据库中的事件重复，以便可以对它们进行索引，这可能会破坏您的插入性能。我不熟悉 SQLite 以及它是否支持这种索引......

I'm assuming you want to detect whether a single newly inserted (or updated) event has overlaps (not whether any of of the events already in the database have overlaps), correct?

If so, you could procedurally (in your client language) generate all start/end intervals [s, e] based on the newly inserted event's "repeat Type", and then execute the following query for each of these intervals to detect overlaps (I'm using Oracle syntax here, I'm assuming SQLite is similar):

-- A time interval must be either completely "to the left" or completely
-- "to the right" of the other time interval for them not to overlap.
SELECT * FROM EVENT
WHERE
    NOT(
        (:s < DTSTART AND :s < DTEND AND :e < DTSTART AND :e < DTEND)
        OR (:s > DTSTART AND :s > DTEND AND :e > DTSTART AND :e > DTEND)
    )

Don't expect stellar performance though (especially if your event has large number repetitions or if DTSTART/DTEND are not indexed or SQLite is unable to properly leverage that index).

For performance, you will probably be better off caching all events in memory and doing all processing client-side, which would allow you to more easily use heuristics to "short-circuit" some processing. For example:

If two events have same "repeat Type", you can just compare their initial intervals without worrying about repetitions - if they don't initially match, they'll never match.
If one event's "last Occurrence" is before other even't "dtstart", they can never match regardless of "repeat Type".
Etc...

If you really want all of your processing database-side and you want (query) performance, you are probably looking at some kind of geospatial/multidimensional indexing and you'll need to actually store the event repetitions in the database so they can be indexed, which would probably destroy your insert performance. I'm not familiar with SQLite and whether it supports this kind of indexing...

回复收藏 0 原文

夏の忆 2024-12-05 01:02:41

我想不出单个 SQL 语句可以检查重复事件的重叠，但这里有一些建议。

如果所有重复事件都有明确的结束日期，您可以创建一个由 EventID、StartTime 和 组成的表 EventInstance >结束时间。然后，您在 Event 表上编写 AFTER INSERT、AFTER UPDATE 和 AFTER DELETE 触发器，以更新存储在事件实例。然后您的查询就可以在 EventInstance 表上使用。但说实话，我没有任何使用 SQLite 的经验，所以我不知道它是否支持触发器。
在数据库中编写一个存储过程，用过程代码检查它（如果 SQLite 支持的话，再次检查）
检查Java 代码中的重叠事件。