PostgreSQL 的 tsrange 如何簡化營業時間查詢？

使用tsrange 在PostgreSQL 中進行操作查詢的時間

簡介

使用tsrange 的替代方法

為了解決這些複雜性，需要一種更有效率的方法該方法涉及使用tsrange 資料類型，它表示一系列沒有時區的時間戳。透過利用 tsrange，我們可以將運行時間儲存為範圍集。這個方法有幾個好處：

資料結構

例如，如果商店從週三下午 6 點到週四早上 5點（UTC）營業，則對應的營業時間範圍會為：

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

函數

'[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());

查詢

查詢

CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours);

原始複雜查詢現在可以是替換為以下簡化的語句：

索引

為了最佳化查詢效能，可以建立按小時的SP-GiST 索引：效能這種使用SP-GiST 索引的方法提供了顯著的效能改進，尤其是在搜尋大量結果時。它優於其他索引策略，並減少了低結果計數和高結果計數的執行時間。

以上是PostgreSQL 的 tsrange 如何簡化營業時間查詢？的詳細內容。更多資訊請關注PHP中文網其他相關文章！