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

本文介绍了非重叠，连续时间戳范围（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 );

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

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 ...