使用范围列简化时间查询

操作网站

MemFire Cloud:https://memfiredb.com/

创建应用->sql编辑器

在使用预订应用或日历应用等应用程序时，您需要存储事件的开始时间和结束时间。您可能还需要查询特定时间范围内发生的事件或确保某些事件不重叠。如果您有一个包含两个独立列的表start_at，end_at用于保存事件的开始和结束，则可能很难执行高级查询或添加约束以防止重叠。本文将展示范围类型列如何提供有用的查询功能和高级约束以避免重叠。

传统日期列的问题

传统上，在处理事件或时期时，开发人员通常使用两个单独的列来表示范围的开始和结束。例如：

create table reservations (
  id serial primary key,
  title text,
  start_at timestamptz,
  end_at timestamptz
);

虽然这种方法有效，但它有一些缺点：

1. 查询复杂性：编写查询来查找重叠事件或特定时期内的事件变得复杂且容易出错。
2. 数据完整性：确保预留不重叠很困难。

输入范围类型

范围类型是 Postgres 中的数据类型，用于保存基类型范围的开始和结束。 的范围int4是int4range， 的范围timestamptz是tstzrange， 的范围date是daterange。每个范围都有一个起始值、一个结束值，以及方括号[]或圆括号()将它们括起来。方括号表示结束是包含的，圆括号表示结束是不包含的。int4range的[2,5)表示从 2（包括它）到 5（不包括它）的整数范围，即 2、3 和 4。

查询范围

使用这些范围值，我们可以创建如下所示的预留表：

create table reservations (
  id serial primary key,
  title text,
  duration tstzrange
);

使用tstzrange而不是两timestamptz列有几个优点。首先，它允许我们轻松使用运算&&符查询与提供的范围重叠的预订。看看以下选择查询：

select *
	from reservations
	where duration && '[2024-07-04 16:00, 2024-07-04 19:00)';

此查询返回持续时间与 重叠的行[2024-07-04 16:00, 2024-07-04 19:00)。例如，[2024-07-04 18:00, 2024-07-04 21:00)将返回带有 的行，但[2024-07-04 20:00, 2024-07-04 22:00)不会返回带有 的行。在查找给定时间段内的预订或活动时，可以使用重叠运算符。

Postgres 提供了更多特定于范围的运算符。官方 Postgres 文档提供了完整的范围运算符列表。

对范围列添加约束

在开发预订应用时，您可能希望确保没有重叠的预订。范围列可以轻松添加此类约束。以下 SQL 语句添加了排除约束，可防止新插入/更新与任何现有预订重叠。

alter table reservations
	add constraint exclude_duration exclude
	using gist (duration with &&)

由于上述约束，以下 SQL 语句中的第二次插入会失败，因为duration与第一次插入重叠。

-- Add a first reservation
insert into reservations (title, duration)
values ('Tyler Dinner', '[2024-07-04 18:00, 2024-07-04 21:00)');

-- The following insert fails because the duration overlaps with the above
insert into reservations (title, duration)
values ('Thor Dinner', '[2024-07-04 20:00, 2024-07-04 22:00)');

现在，排除约束可防止任何预订重叠，但在现实世界中，单个预订表通常保存不同餐厅和餐厅内餐桌的预订，并且仅仅因为在一家餐厅进行了一次预订，并不意味着整个餐厅都被预订了。Postgres 可以创建这样的约束，只有当特定的其他列匹配且范围重叠时才不允许插入或更新。

假设我们table_id的预订表中有一列。这table_id可能代表此数据库所包含的各个餐厅的单个表。

create table reservations (
  id serial primary key,
  title text,
  table_id int4,
  duration tstzrange
);

有了table_id列之后，我们可以添加约束以确保同一张表上的预留不会重叠。该约束需要btree_gist扩展。

-- Enable the btree_gist index required for the constraint.
create extension btree_gist

-- Add a constraint to prevent overlaps with the same table_id
alter table reservations
  add constraint exclude_duration
  exclude using gist (table_id WITH =, duration WITH &&);

有了这个简单的约束，任何两个预订都不会以相同的 相互重叠table_id。如果我们运行以下插入，第二个插入将失败，因为它试图在持续时间重叠的情况下预订与第一个插入相同的表。

-- Add a first reservation
insert into reservations (title, table_id, duration)
values ('Tyler Dinner', 1, '[2024-07-04 18:00, 2024-07-04 21:00)');

-- Insert fails, because table 1 is taken from 18:00 - 21:00
insert into reservations (title, table_id, duration)
values ('Thor Dinner', 1, '[2024-07-04 20:00, 2024-07-04 22:00)');

-- Insert succeeds because table 2 is not taken by anyone
insert into reservations (title, table_id, duration)
values ('Thor Dinner', 2, '[2024-07-04 20:00, 2024-07-04 22:00)');

这就是如何创建一个可以容纳预订的密封桌子。

结论

Postgres 的范围列为处理预订系统等应用程序中的范围数据提供了解决方案。它们使用特定运算符（例如）简化查询，&&并通过启用约束来防止重叠，从而提高数据完整性。范围列为表示时间段的传统两列方法提供了一种替代方案。通过利用这些功能，开发人员可以用更少的代码创建更复杂、更可靠的应用程序。

更多

• 观看范围列的视频指南
• 检查约束视频
• 使用 Protomaps 在 Supabase 存储上自托管地图
• 行级安全指南