PostgreSQL 的 tsrange 如何简化营业时间查询？

使用 tsrange 在 PostgreSQL 中进行操作查询的时间

简介

在 PostgreSQL 中，时间特定实体的操作可以存储为整数列对，例如 opens_on 和closes_on 代表工作日，opens_at 和 closes_at 代表一天中的时间。然而，由于时间表中潜在的重叠和不规则性，对此类数据执行查询可能会很复杂。

使用 tsrange 的替代方法

为了解决这些复杂性，需要一种更高效的方法该方法涉及使用 tsrange 数据类型，它表示一系列没有时区的时间戳。通过利用 tsrange，我们可以将运行时间存储为范围集。这种方法有几个好处：

数据结构

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

例如，如果商店从周三下午 6 点到周四早上 5 点（UTC）营业，则相应的营业时间范围会为：

'[1996-01-03 18:30, 1996-01-04 05:00]'

函数

CREATE OR REPLACE FUNCTION f_hoo_time(timestamptz)
  RETURNS timestamp
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT timestamp '1996-01-01' + ( AT TIME ZONE 'UTC' - date_trunc('week',  AT TIME ZONE 'UTC'))
$func$;

CREATE OR REPLACE FUNCTION f_hoo_hours(_from timestamptz, _to timestamptz)
  RETURNS TABLE (hoo_hours tsrange)
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE COST 500 ROWS 1 AS
$func$
DECLARE
   ts_from timestamp := f_hoo_time(_from);
   ts_to   timestamp := f_hoo_time(_to);
BEGIN
   -- Perform sanity checks and split ranges at Sunday midnight if necessary.
   ...
END
$func$;

查询

原始复杂查询现在可以是替换为以下简化的语句：

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

索引

为了优化查询性能，可以创建按小时的 SP-GiST 索引：

CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours);

性能

这种使用 SP-GiST 索引的方法提供了显着的性能改进，尤其是在搜索大量结果时。它优于其他索引策略，并减少了低结果计数和高结果计数的执行时间。

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