PostgreSQLのmulti rangeでカレンダーの機能を使ってみる

## はじめに
カレンダー機能を使う上で、値に幅のあるデータを扱います。その場合、multi rangeを使うと良さそうなので、学んでいきます！

## TBLの作成、基本操作
```sql:
CREATE TABLE calendar_events (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,  -- ユーザー識別
    title TEXT NOT NULL,       -- イベントのタイトル
    time_ranges TSMULTIRANGE NOT NULL, -- イベント時間帯（複数可）
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);
```

・データの登録
```sql
INSERT INTO public.calendar_events (user_id, title, time_ranges)
VALUES (
    1,
    'カンファレンス',
    '{[2025-05-15 10:00,2025-05-15 12:00)}'::tsmultirange
);
```

・データ取得
```sql:
calendar=# SELECT id, title, time_ranges
FROM public.calendar_events
WHERE id = 1;
 id |     title      |                   time_ranges                   
----+----------------+-------------------------------------------------
  1 | カンファレンス | {["2025-05-15 10:00:00","2025-05-15 12:00:00")}
(1 row)
```

### 今週のデータ作成、データ取得
```sql
-- 月曜 09:00-10:00
INSERT INTO calendar_events (user_id, title, time_ranges)
SELECT
    1,
    '週次ミーティング',
    tsmultirange(
        tsrange(
            date_trunc('week', current_date::timestamp) + interval '1 day 09:00',
            date_trunc('week', current_date::timestamp) + interval '1 day 10:00',
            '[)'
        )
    );

-- 水曜 14:00-15:30
INSERT INTO calendar_events (user_id, title, time_ranges)
SELECT
    1,
    '開発レビュー',
    tsmultirange(
        tsrange(
            date_trunc('week', current_date::timestamp) + interval '3 day 14:00',
            date_trunc('week', current_date::timestamp) + interval '3 day 15:30',
            '[)'
        )
    );

-- 金曜 10:00-12:00
INSERT INTO calendar_events (user_id, title, time_ranges)
SELECT
    1,
    '外部打合せ',
    tsmultirange(
        tsrange(
            date_trunc('week', current_date::timestamp) + interval '5 day 10:00',
            date_trunc('week', current_date::timestamp) + interval '5 day 12:00',
            '[)'
        )
    );
```

```sql:
calendar=# SELECT id, title, time_ranges
FROM public.calendar_events;
 id |      title       |                   time_ranges                   
----+------------------+-------------------------------------------------
  1 | カンファレンス   | {["2025-05-15 10:00:00","2025-05-15 12:00:00")}
  3 | 外部打合せ       | {["2025-05-17 10:00:00","2025-05-17 12:00:00")}
  4 | 開発レビュー     | {["2025-05-15 14:00:00","2025-05-15 15:30:00")}
  5 | 週次ミーティング | {["2025-05-13 09:00:00","2025-05-13 10:00:00")}
(4 rows)
```
→開始時刻の昇順に並んでないですね。開始時刻順に並び替えましょう！

```sql:
calendar=# SELECT 
  id,
  title,
  time_ranges
FROM public.calendar_events
ORDER BY
  (
    -- multirange を unnest して各 range の下限を取り、最小値をソートキーに
    SELECT MIN(lower(r))
    FROM unnest(time_ranges) AS r
  ) ASC;
 id |      title       |                   time_ranges                   
----+------------------+-------------------------------------------------
  5 | 週次ミーティング | {["2025-05-13 09:00:00","2025-05-13 10:00:00")}
  1 | カンファレンス   | {["2025-05-15 10:00:00","2025-05-15 12:00:00")}
  4 | 開発レビュー     | {["2025-05-15 14:00:00","2025-05-15 15:30:00")}
  3 | 外部打合せ       | {["2025-05-17 10:00:00","2025-05-17 12:00:00")}
(4 rows)
```
:::note info
order byが普段のSQLとちょっと違いますね💦💦
知らないとできないので、しっかり学習しておきましょう！
:::

→今週のデータだけだと、絞り込みがうまくできているかわからないので、先週と来週のデータも追加します。

```sql:
-- 先週のデータ
INSERT INTO public.calendar_events (user_id, title, time_ranges)
VALUES (
    1,
    'カンファレンス',
    '{[2025-05-02 10:00:00,2025-05-02 12:00)}'::tsmultirange
);

-- 来週のデータ
INSERT INTO public.calendar_events (user_id, title, time_ranges)
VALUES (
    1,
    'カンファレンス',
    '{[2025-05-20 10:00:00,2025-05-20 12:00)}'::tsmultirange
);
```

```sql:
calendar=# SELECT 
  id,
  title,
  time_ranges
FROM public.calendar_events
ORDER BY
  (
    -- multirange を unnest して各 range の下限を取り、最小値をソートキーに
    SELECT MIN(lower(r))
    FROM unnest(time_ranges) AS r
  ) ASC;
 id |      title       |                   time_ranges                   
----+------------------+-------------------------------------------------
  6 | カンファレンス   | {["2025-05-02 10:00:00","2025-05-02 12:00:00")} ←先週のデータ
  5 | 週次ミーティング | {["2025-05-13 09:00:00","2025-05-13 10:00:00")}
  1 | カンファレンス   | {["2025-05-15 10:00:00","2025-05-15 12:00:00")}
  4 | 開発レビュー     | {["2025-05-15 14:00:00","2025-05-15 15:30:00")}
  3 | 外部打合せ       | {["2025-05-17 10:00:00","2025-05-17 12:00:00")}
  7 | カンファレンス   | {["2025-05-20 10:00:00","2025-05-20 12:00:00")} ←来週のデータ
(6 rows)
```

```sql
calendar=# SELECT 
  id,
  title,               
  time_ranges                                                        
FROM public.calendar_events                                  
ORDER BY                                             
  (                                                                  
    -- multirange を unnest して各 range の下限を取り、最小値をソートキーに
    SELECT MIN(lower(r))                                          
    FROM unnest(time_ranges) AS r      
  ) ASC;                 
 id |      title       |                   time_ranges                   
----+------------------+-------------------------------------------------
  6 | カンファレンス   | {["2025-05-02 10:00:00","2025-05-02 12:00:00")}
  5 | 週次ミーティング | {["2025-05-13 09:00:00","2025-05-13 10:00:00")}
  1 | カンファレンス   | {["2025-05-15 10:00:00","2025-05-15 12:00:00")}
  4 | 開発レビュー     | {["2025-05-15 14:00:00","2025-05-15 15:30:00")}
  3 | 外部打合せ       | {["2025-05-17 10:00:00","2025-05-17 12:00:00")}
  7 | カンファレンス   | {["2025-05-20 10:00:00","2025-05-20 12:00:00")}
(6 rows)
```

```sql:
calendar=# WITH week_bounds AS (
  SELECT
    -- 今週の日曜 00:00 を timestamp 型で
    ( date_trunc('day', current_date::timestamp)
        - extract(dow FROM current_date)::int * interval '1 day'
    ) AS sun_start,
    -- 翌週の日曜 00:00（排他 end）を timestamp 型で
    ( date_trunc('day', current_date::timestamp)
        - extract(dow FROM current_date)::int * interval '1 day'
      + interval '7 day'
    ) AS next_sun_start
)
SELECT
  ce.id,
  ce.title,
  ce.time_ranges
FROM public.calendar_events AS ce
CROSS JOIN week_bounds AS w
WHERE
  -- tsrange( timestamp, timestamp, '範囲指定' ) に合うように
  ce.time_ranges
  && tsrange(w.sun_start, w.next_sun_start, '[)')
ORDER BY
  (
    SELECT MIN(lower(r))
    FROM unnest(ce.time_ranges) AS r
  ) ASC;
 id |      title       |                   time_ranges                   
----+------------------+-------------------------------------------------
  5 | 週次ミーティング | {["2025-05-13 09:00:00","2025-05-13 10:00:00")}
  1 | カンファレンス   | {["2025-05-15 10:00:00","2025-05-15 12:00:00")}
  4 | 開発レビュー     | {["2025-05-15 14:00:00","2025-05-15 15:30:00")}
  3 | 外部打合せ       | {["2025-05-17 10:00:00","2025-05-17 12:00:00")}
(4 rows)
```
:::note info
SQLでゴリ押すとこんな感じの実装になるんですね。なるほど。
`&& tsrange(w.sun_start, w.next_sun_start, '[)')`が肝となる箇所ですね。
上で示した例だと、`w.sun_start`,`w.next_sun_start`はWITH句で定義していますが、アプリケーション側から引数渡す方が柔軟性ありそうですね。あと、これは閉区間、開区間のおはなしも混じっているんですね。
:::