PostgreSQL 的'tsrange”类型如何优化营业时间的查询？

在 PostgreSQL 中执行操作时间查询

在 PostgreSQL 中，您可以执行查询来查找所有“打开”的记录使用日期和时间比较的组合来指定操作时间。但是，这种方法可能会变得复杂，尤其是在周末的情况下。

使用 tsrange 类型的建议解决方案

要简化和优化此查询，您可以重新设计该表，将营业时间存储为一组 tsrange（不含时区的时间戳范围）值。这需要 PostgreSQL 版本 9.2 或更高版本，并涉及以下步骤：

表布局：

• 创建一个包含名为 hours 的 tsrange 列的表来存储营业时间而不是单独的开盘时间和关盘时间列。

示例数据：

CREATE TABLE hoo (
  hoo_id  serial PRIMARY KEY
, shop_id int NOT NULL
, hours   tsrange NOT NULL
);

• UTC 周三 18:30 至周四 05:00 的营业时间可插入为：

INSERT INTO hoo(shop_id, hours)
VALUES (123, '[1996-01-03 18:30, 1996-01-04 05:00]');

排除约束：

• 添加了排除约束，以防止使用 GiST 索引每个商店的条目重叠。

辅助函数：

• 两个辅助函数，f_hoo_time() 和f_hoo_hours() 被定义为标准化跨越周日午夜的时间戳和分割范围。

优化查询：

使用新的表结构和辅助函数，您可以可以简化您的查询到：

SELECT *
FROM hoo
WHERE hours @> f_hoo_time(now());

优点：

• tsrange 的使用简化了查询语法，并通过利用支持的索引提高了性能。
• 排除约束确保数据完整性并防止条目重叠。
• GiST 索引提供基于小时字段的快速高效搜索。
• 仅小时的 SP-GiST 索引进一步提高了涉及大量结果的查询的性能。

以上是PostgreSQL 的'tsrange”类型如何优化营业时间的查询？的详细内容。更多信息请关注PHP中文网其他相关文章！