如何使用 tsrange 有效率地查詢 PostgreSQL 營業時間？

PostgreSQL 中執行此營業時間查詢

原問題闡述了使用 SQL 在 ActiveRecord (RoR) 應用程式中查詢滿足特定營業時間範圍的記錄。查詢使用了複雜的條件來比較目前時間點與儲存的值。問題提出了透過將時間值轉換為某種日期類型並讓 PostgreSQL 處理比較是否可行的疑問。

最佳實務：使用 tsrange 類型

為了解決這個問題，建議使用 PostgreSQL 的 tsrange (時間範圍) 資料類型來儲存營業時間。 tsrange 可讓您儲存以時間段的形式表示的營業時間，而 PostgreSQL 提供了內建函數和運算子來操作這些範圍。

架構變更

為此，需要對現有的表進行架構變更：

ALTER TABLE hours_of_operations
ALTER COLUMN opens_on TYPE TIMESTAMP
ALTER COLUMN closes_on TYPE TIMESTAMP
ALTER COLUMN opens_at TYPE TIME
ALTER COLUMN closes_at TYPE TIME

建立新的列hours 來儲存tsrange 值：

ALTER TABLE hours_of_operations
ADD COLUMN hours TSrange

然後使用以下語句更新hours列：

UPDATE hours_of_operations
SET hours = tsrange(opens_on || ' ' || opens_at, closes_on || ' ' || closes_at)

查詢條件

更新了表格結構後，可以編寫一條更簡潔且高效的SQL 查詢來滿足原始問題的要求：

SELECT *
FROM hours_of_operations
WHERE current_timestamp @> hours

此查詢使用@> 運算子檢查目前時間點是否在儲存的hours 範圍內。如果您需要考慮時區問題，請使用對應的時間函數將目前時間轉換為 UTC 或其他所需的時區。

優勢

使用 tsrange 的優點包括：

• 查詢簡單且易於理解。
• 避免複雜的比較邏輯，提升效能。
• 支援複雜的營業時間，例如跨越一天或包裹週期的營業時間。
• 可以與 PostgreSQL 提供的其他時間相關功能和索引一起使用。

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