非重叠，连续时间戳范围（tstzrange）开放时间 [英] Non-overlap, continuous timestamp ranges (tstzrange) for opening hours

问题描述

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

问题：

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


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


3. 在模式设计中，是<？c $ c> tstzrange 常用？或者我更好用两列？

1. How do I add a constraint to make sure that there is no overlap in the operating hours?
2. In terms of query speed, am I better off with two columns (start_at, end_at) or is GIST index fast for tstzrange?
3. 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 ...}

这篇关于非重叠，连续时间戳范围（tstzrange）开放时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！