非重叠，连续时间戳范围（tstzrange）开放时间

问题描述：

CREATE TABLE operating_period (
  id SERIAL NOT NULL PRIMARY KEY,
  during TSTZRANGE NOT NULL,
  -- other meta fields
);

要求：
1.没有运营期可以相互重叠

Requirements: 1. No operating period can overlap with each other

问题：

如何添加约束以确保在运行时间？

在查询速度方面，我最好使用两列（start_at，end_at）或GIST索引快对于 tstzrange ？

在模式设计中，是 tstzrange 常用？或者我更好用两列？

How do I add a constraint to make sure that there is no overlap in the operating hours?
In terms of query speed, am I better off with two columns (start_at, end_at) or is GIST index fast for tstzrange?
In schema design, is tstzrange commonly used? Or am I better of with two columns?

答

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
);

这是在之前的code >，它自动支持多种类型的查询。相关答案：

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）开放时间

相关推荐