要求: 1.没有运营期可以相互重叠
Requirements: 1. No operating period can overlap with each other
问题:
推荐答案
1 的答案很明确。为确保没有重叠,请使用 排除约束 :
The answer to 1. is clear. To make sure there is no overlap use an exclusion constraint:
CREATE TABLE operating_period ( id serial PRIMARY KEY -- PK is NOT NULL automatically ,during tstzrange NOT NULL ,EXCLUDE USING gist (during WITH &&) -- no overlap );这是在之前的,它自动支持多种类型的查询。相关答案:
This is implemented with a GiST index on during, that supports many types of queries automatically. Related answer:
- 使用PostgreSQL中的EXCLUDE阻止相邻/重叠的条目
- 在PostgreSQL中执行这个小时的操作查询
- Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
- Perform this hours of operation query in PostgreSQL
2。和 3。的答案并不清楚,因为它真的取决于很多事情两者都有利弊。对于开放时间,我最有可能在当前版本的Postgres中使用范围类型。我还将为所有条目强制执行 [] 边界,以保持简单。 第一个链接答案的详细信息。
Answers to 2. and 3. are not as clear, because it really depends on a lot of things. Both have their pros and cons. For opening hours I would most likely go with range types in current versions of Postgres. I would also enforce [) bounds for all entries to keep things simple. Details in the first linked answer.
如果您应该使用(start_at,end_at),您将对 OVERLAPS 运算符:
If you should go with (start_at, end_at), you'll be interested in the OVERLAPS operator:
- 在PostgreSQL中获取两个日期之间的结果
- 在PostgreSQL中查找重叠的日期范围
- Getting results between two dates in PostgreSQL
- Find overlapping date ranges in PostgreSQL
无论哪种方式,这里的指南是问每个问题一个问题,而不是整个列表...
Either way, the guideline here is to ask one question per question, not a whole list ...
更多推荐
非重叠,连续时间戳范围(tstzrange)开放时间
发布评论