SQLで予定が空いている時間帯を求める

はじめに

業務でPostgreSQLのtsrange、range_agg、unnestを使う機会があり便利だったのですが、すぐ忘れてしまいそうなので備忘録にしました。

検証データを準備

予定を保存するテーブル作成

CREATE TABLE member_schedules (
    id SERIAL PRIMARY KEY,
    user_name VARCHAR(50),      -- 誰の予定か
    title VARCHAR(100),         -- 予定名
    start_at TIMESTAMP,         -- 予定の開始日時
    end_at TIMESTAMP            -- 予定の終了日時
);

予定データを登録

INSERT INTO member_schedules (user_name, title, start_at, end_at) VALUES
('メンバー1', '予定1', '2026-09-30 10:00:00', '2026-09-30 12:00:00'),
('メンバー2', '予定2', '2026-09-30 11:00:00', '2026-09-30 13:00:00'),
('メンバー3', '予定3', '2026-09-30 15:00:00', '2026-09-30 17:00:00');

動作確認

ステップ1：tsrange() で範囲型に変換して range_agg で合体

まずは TIMESTAMP 型の start_at と end_at を、tsrange(start_at, end_at) を使って時間枠（範囲型）に変換します。
その時間枠を range_agg() で集約することで、全員の予定がマージされた「予定埋まりタイムライン」を作れます。

WITH converted_ranges AS (
    -- 1. タイムスタンプを範囲型（tsrange）に変換
    SELECT 
        tsrange(start_at, end_at) AS busy_range
    FROM 
        member_schedules
)
-- 2. 全員の予定を1つにマージ
SELECT 
    range_agg(busy_range) AS total_busy_multirange
FROM 
    converted_ranges;

結果

重複していた「10:00〜12:00」と「11:00〜13:00」が自動的にマージされ、1つの塊になります。

{["2026-09-30 10:00:00","2026-09-30 13:00:00"),["2026-09-30 15:00:00","2026-09-30 17:00:00")}

ステップ2：営業時間から「予定埋まりタイムライン」を引き算する

全体のベースとなる「営業時間（09:00 〜 18:00）」から、先ほど作った「予定埋まりタイムライン」を引き算（- 演算子）します。
これで「誰も予定が入っていない（共通の空き時間）」だけが残ります。

WITH converted_ranges AS (
    SELECT tsrange(start_at, end_at) AS busy_range FROM member_schedules
),
busy_summary AS (
    SELECT range_agg(busy_range) AS total_busy_time FROM converted_ranges
)
-- 営業時間から予定を引き算して「空き時間の塊」を作る
SELECT 
    tsmultirange(tsrange('2026-09-30 09:00:00', '2026-09-30 18:00:00')) 
    - total_busy_time AS total_free_time
FROM 
    busy_summary;

結果

{["2026-09-30 09:00:00","2026-09-30 10:00:00"),["2026-09-30 13:00:00","2026-09-30 15:00:00"),["2026-09-30 17:00:00","2026-09-30 18:00:00")}

ステップ3：unnest と lower / upper でTIMESTAMP型に戻す

最後に、塊になっている空き時間を unnest() でバラバラの行（レコード）に展開し、さらに lower() と upper() を使って、普通の TIMESTAMP 型に戻します。

WITH converted_ranges AS (
    SELECT tsrange(start_at, end_at) AS busy_range FROM member_schedules
),
busy_summary AS (
    SELECT range_agg(busy_range) AS total_busy_time FROM converted_ranges
),
free_slots_summary AS (
    SELECT 
        tsmultirange(tsrange('2026-09-30 09:00:00', '2026-09-30 18:00:00')) 
        - total_busy_time AS total_free_time
    FROM 
        busy_summary
)
-- unnest で行にバラし、lower/upper で普通の TIMESTAMP に戻して出力
SELECT 
    lower(unnest(total_free_time)) AS free_start_at,
    upper(unnest(total_free_time)) AS free_end_at
FROM 
    free_slots_summary;

結果

free_start_at	free_end_at
2026-09-30 09:00:00.000	2026-09-30 10:00:00.000
2026-09-30 13:00:00.000	2026-09-30 15:00:00.000
2026-09-30 17:00:00.000	2026-09-30 18:00:00.000

さいごに

tsrange、range_agg、unnestを使うだけでかなりシンプルになるので、時間帯を求める際は一度使ってみることをおすすめします。