PostgreSQL の「tsrange」は営業時間のクエリをどのように簡素化できるのでしょうか?

tsrange を使用した PostgreSQL の操作時間クエリ

概要

PostgreSQL の時間特定のエンティティの操作は、次のような整数列のペアとして保存できます。 opens_on と closes_on は平日を表し、opens_at と closes_at は時刻を表します。ただし、このようなデータに対するクエリの実行は、スケジュールの重複や不規則性の可能性により複雑になる可能性があります。

tsrange を使用した代替アプローチ

これらの複雑さに対処するには、より効率的なこのアプローチでは、タイム ゾーンを含まないタイムスタンプの範囲を表す tsrange データ型を使用します。 tsrange を利用することで、稼働時間を範囲のセットとして保存できます。このアプローチにはいくつかの利点があります。

データ構造

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

たとえば、お店が水曜日の午後 6 時から木曜日の午前 5 時 (UTC) まで営業している場合、対応する時間は範囲は

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

関数

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

クエリ

元の複雑なクエリは次のようになります。以下の簡略化されたものに置き換えられますステートメント:

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

インデックス作成

クエリのパフォーマンスを最適化するために、時間ごとに SP-GiST インデックスを作成できます:

CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours);

パフォーマンス

を使用したこのアプローチSP-GiST インデックスでは、特に多数の結果を検索する場合にパフォーマンスが大幅に向上します。他のインデックス作成戦略よりも優れたパフォーマンスを発揮し、結果数が少ない場合でも多い場合でも実行時間を短縮します。

以上がPostgreSQL の「tsrange」は営業時間のクエリをどのように簡素化できるのでしょうか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。