如何使用 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中文网其他相关文章！