How Can I Efficiently Query Business Hours in PostgreSQL Using `tsrange`?

Executing this business hours query in PostgreSQL

The original question illustrates using SQL to query for records that meet a specific business hours range in an ActiveRecord (RoR) application. The query uses complex conditions to compare the current point in time with the stored value. The question raises the question whether this is possible by converting the time value to some date type and letting PostgreSQL handle the comparison.

Best practice: Use the tsrange type

To solve this problem, it is recommended to use PostgreSQL's tsrange (time range) data type to store business hours. tsrange allows you to store business hours expressed as time periods, and PostgreSQL provides built-in functions and operators to manipulate these ranges.

Schema changes

To do this, you need to make schema changes to the existing table:

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

Create a new column hours to store the tsrange value:

ALTER TABLE hours_of_operations
ADD COLUMN hours TSrange

Then use the following statement to update hours Column:

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

Query condition

After updating the table structure, you can write a more concise and efficient SQL query to meet the requirements of the original question:

SELECT *
FROM hours_of_operations
WHERE current_timestamp @> hours

This query uses the @> operator to check whether the current time point is within the stored hours range. If you need to consider time zone issues, use the appropriate time function to convert the current time to UTC or other desired time zone.

Advantages

The advantages of using tsrange include:

• The query is simple and easy to understand.
• Avoid complex comparison logic and improve performance.
• Supports complex business hours, such as those that span a day or a package cycle.
• Can be used with other time-related functions and indexes provided by PostgreSQL.

The above is the detailed content of How Can I Efficiently Query Business Hours in PostgreSQL Using `tsrange`?. For more information, please follow other related articles on the PHP Chinese website!