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中文網其他相關文章！