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

Perform This Hours of Operation Query in PostgreSQL

In PostgreSQL, you can use the tsrange data type to represent a range of timestamps without time zone. This can be used to simplify queries for hours of operation, which may wrap around the end of the week.

Table Layout

Create a new table with the following schema:

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

The hours column will store the hours of operation for each shop.

Inserting Data

You can insert data into the hoo table using the f_hoo_hours() function, which takes two timestamps and returns a tsrange value:

INSERT INTO hoo(shop_id, hours)
VALUES (123, f_hoo_hours('2016-01-11 00:00+04', '2016-01-11 08:00+04'));

Querying Data

You can query the hoo table to find the shops that are open at a given time using the following query:

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

This query will return all of the shops that are open at the current time.

Indexing

To improve the performance of the query, you can create a GiST or SP-GiST index on the hours column:

CREATE INDEX hoo_hours_gist_idx ON hoo USING gist (hours);

This index will allow PostgreSQL to quickly find the shops that are open at a given time.

Conclusion

Using the tsrange data type and the f_hoo_hours() function, you can simplify queries for hours of operation in PostgreSQL. By creating a GiST or SP-GiST index on the hours column, you can further improve the performance of these queries.

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