英文:
Query SQL Table for dates that start/end within a range AND dates that are part of that range but start before/after
问题
概述：
我有一个SQL数据库表，其中包括每一行（事件）的开始和结束日期，格式为时间戳。
目标：
显示给定周的事件，这些事件包括在该周内开始和结束的事件，但也可能在该周内进行（它们的开始/结束日期可能在该周内或在周开始之前和周结束之后）。期望结果的示例：
- 在7月16日至7月22日期间，结果应包括以下事件：
- 开始和结束于7月16日、7月19日或7月22日的事件
- 从7月10日开始，到7月19日结束的事件
- 从7月19日开始，到7月26日结束的事件
- 从7月4日开始，到7月30日结束的事件
我已经尝试过使用BETWEEN和使用start > {$week_start} AND end < {$week_end}的查询，但这两种方法都没有考虑到“重叠”日期。
问题：
是否可能编写一个单一查询，包括所有这些情况？
顺便说一下 - 如果有更好的解决方案，我可以使用除时间戳之外的日期写法 
英文:
Overview:
I have a SQL database table that includes start and end dates formatted as timestamps for each row (event).
Goal:
Display events for a given week, which would include events that start and end within that week, but could also take place during that week (their start/end dates could fall within the week or start before and end after). Example of desired results:
- Given Week of July 16 - July 22, results would include events that:
- start and end July 16, July 19, or July 22
- start July 10 and end July 19
- start July 19 and end July 26
- start July 4 and end July 30
I have tried queries that use BETWEEN and queries with start > {$week_start} AND end < {$week_end}, but neither of these account for 'overlap' dates.
Question
Is it possible to write a single query that includes all of these scenarios?
FWIW - Im open to writing dates in something other than timestamps if theres a better solution
答案1
得分: 1
start <= {$week_end} AND end >= {$week_start} 的翻译是：
开始 <= {$week_end} 且 结束 >= {$week_start}
英文:
The logic you are looking for is
start <= {$week_end} AND end >= {$week_start}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论