PostgreSQLのmulti rangeを利用する

はじめに

今回はPostgreSQLのmulti rangeを利用してみます。カレンダーのデータを登録する時に便利と聞いたので、まずは触ってみるところから始めます！！
今回は、PostgreSQL16で動かしています。

Hands-on

今回のHans-onは下記Stepで学んでいきます！

ステップ	内容
Step 1	イベントに複数の時間帯を登録する（Insert）
Step 2	特定の日時が含まれているかどうかを検索する（@>）
Step 3	時間帯の追加とマージの動作を見る（`
Step 4	指定時間帯と 重なっていない イベントだけを取得する（NOT &&）

Step1 イベントに複数の時間帯を登録する（Insert）

TBLの準備

CREATE TABLE public.event_multirange_test (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    time_ranges TSMULTIRANGE NOT NULL,
    all_day BOOLEAN NOT NULL DEFAULT FALSE
);

データのinsert

INSERT INTO public.event_multirange_test (title, time_ranges, all_day)
VALUES (
    '午前・午後の打ち合わせ',
    '{[2025-05-16 09:00, 2025-05-16 11:00), [2025-05-16 14:00, 2025-05-16 15:30)}'::tsmultirange,
    FALSE
);

SELECT * FROM public.event_multirange_test;

ここだけだと全然良さがわからないですね。次に行きましょう！

Step2 特定の日時が含まれているかどうかを検索する（@>）

2025-05-16 09:30」が時間範囲に 含まれているイベントを取得する。

SELECT *
FROM public.event_multirange_test
WHERE time_ranges @> TIMESTAMP '2025-05-16 09:30';

→確かにデータが取得できていますね。

→'2025-05-16 08:30'の場合だと、取得はできないですね。

→開始の9:00は取得できますね。

→開始の11:00は取得できない。
この処理の場合、左閉・右開区間 [start, end)で実施しています。
ここ大事なところですね！！！

Step3 時間帯の追加とマージの動作を見る（||）

現在の状態

UPDATE public.event_multirange_test
SET    time_ranges =
       time_ranges
       +
       ('[2025-05-16 11:00,2025-05-16 12:00)'::tsrange)::tsmultirange
WHERE  id = 1;

→確かに11:00~12:00が増えていますね。

Step 4：重なっていないイベントを取得する

SELECT *
FROM public.event_multirange_test
WHERE NOT (
  time_ranges && '[2025-05-16 11:30:00, 2025-05-16 12:30:00)'::tsrange
);

確かに取得できていないですね。

最後に

multi rangeは初めて触りましたが、意外といいですね。幅があるものを検索する場合、2つカラムを持つよりも1つのカラム内で定義できるとSQLがいけるのはいいですね！！