处理日期和时间跨度的好方法？
对于我的网络应用程序，我需要处理访问请求。输入周一至周日时间表的请求，指定每天的时间。本周的日程最多可以重复 52 次。用户在一周内可以有多个计划（星期一 8-9 和星期一 10-11） 有以下要求：
- 可搜索/可过滤
- 检测重叠请求
我希望数据库能够处理尽可能多的提升。目前我能想到的唯一设计是将每天的访问存储为单独的记录。这样做时，我将提取用户的所有访问权限并循环以确定新请求是否重叠。这需要代码或存储过程。
有谁有更好的数据库模型想法或处理代码重叠的干净方法？
For my web app i need to handle access requests. Requests are entered for a monday-sunday schedule specifying hours for each day. This week schedule can repeat up to 52 times. A user can have multiple schedules during a week (mon 8-9 and mon 10-11) There are the following requirements:
- Searchable/filterable
- Detect overlapping requests
I want the database to handle as much of the lifting as possible. Right now the only design I can think of is storing each day's access as a separate record. Doing this I would pull all accesses for a user and loop to determine if the new request overlaps. This requires code or a stored procedure.
Does anyone have a better database model idea or a clean way to deal with overlaps in code?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果将每个访问请求存储在包含 start_time 和 end_time 字段的表中，则可以使用数据库的 BETWEEN 功能来确定特定访问是否会与数据库中已有的访问重叠。
例如，假设某人在周一上午 9:00 到中午 12:00 完成了访问请求。
然后其他人过来并尝试在周一上午 11:00 到下午 3:00 提出访问请求。要确定这是否会与其他内容冲突，您需要查找以下可能的条件：
这些可以被转换为 SQL 查询，这将避免加载和迭代应用程序中的记录。作为奖励，您可以使用事务来防止竞争条件。
If you store each access request in a table with fields with start_time and end_time, then you could use the database's BETWEEN functionality to determine if a particular access would overlap with one already in the database.
As an example, let's say that someone had completed an access request for Monday from 9:00AM to 12:00PM.
Then someone else comes and tries to make an access request for Monday from 11:00AM to 3:00PM. To determine if this would conflict with something else, you'd need to look for the following possible conditions:
These can bet translated down to a SQL query which would prevent having to load and iterate over records in the application. As a bonus, you can use transactions to guard against race conditions.