How Can PostgreSQL's `tsrange` Simplify Queries for Business Hours of Operation?

Hours of Operation Query in PostgreSQL Using tsrange

Introduction

In PostgreSQL, the hours of operation for a particular entity can be stored as pairs of integer columns, such as opens_on and closes_on, representing the weekday, and opens_at and closes_at, representing the time of day. However, performing queries on such data can be complex due to potential overlaps and irregularities in the schedule.

Alternative Approach with tsrange

To address these complexities, a more efficient approach involves using the tsrange data type, which represents a range of timestamps without time zones. By utilizing tsrange, we can store hours of operation as sets of ranges. This approach offers several benefits:

Data Structure

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

For instance, if a shop is open from Wednesday 6 PM to Thursday 5 AM UTC, the corresponding hours range would be:

'[1996-01-03 18:30, 1996-01-04 05:00]'

Functions

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$;

Query

The original complex query can now be replaced with the following simplified statement:

SELECT *
FROM hoo
WHERE hours @> f_hoo_time(now());

Indexing

To optimize query performance, an SP-GiST index on hours can be created:

CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours);

Performance

This approach using SP-GiST index offers significant performance improvements, especially when searching for large numbers of results. It outperforms other indexing strategies and reduces the execution time for both low and high result counts.

The above is the detailed content of How Can PostgreSQL's `tsrange` Simplify Queries for Business Hours of Operation?. For more information, please follow other related articles on the PHP Chinese website!