PostgreSQLの日時系 Rangeについて学ぶ

## はじめに
PostgreSQLのRange型について学んでいこうと思います！！
今回は日時のみを対象に学んでいます！

## PostgreSQLのRange型について
PostgreSQLではRange型という物が存在します。最近まで、私もこんなカラムがあるんだと知らなかったんですが😅
調べてみると、Range型は3つ存在しているようです。
※ちなみにPostgreSQL 14からはmultirange型が追加されています。触ってみたんですが、そこまでは必要ないかなと感じたので、今回の対象外としています

| 型名 | 要素の型 | 格納される値 | タイムゾーン情報 | 主な用途 | 代表的な落とし穴 |
|------|-----------|-------------|-----------------|----------|------------------|
| `daterange` | `date` | **日単位**（0 時開始） | なし | 休暇・請求期間・学期など “日付だけ” で完結する区間 | 同日に複数イベントを区別できない |
| `tsrange` | `timestamp` **without** time zone | 「壁時計」の時刻 | なし | 社内など **単一タイムゾーン**で完結する日程管理 | DST 移行日に *存在しない時刻* が入る／DB `TimeZone` 変更で解釈ズレ |
| `tstzrange` | `timestamptz` (内部 UTC) | UTC に換算した**絶対時刻** | あり（自動で UTC↔ローカル変換） | 国際利用・複数 TZ を跨ぐカレンダー／予約システム | 取得時に必ず `AT TIME ZONE` が必要でクエリが冗長 |

:::note info
ごにょごにょ上に書いたんですが、日付のみならば`daterange`、日本のみで利用する場合は`tsrange`,世界標準で利用する場合は、`tstzrange`のようです。今回は`tsrange`のみを対象とします
:::

## Hand-on

```sql:
-- tsrange のラッパー型を定義
CREATE DOMAIN bounded_tsrange AS tsrange
  CHECK (
    NOT lower_inf(VALUE)
    AND NOT upper_inf(VALUE)
    AND NOT isempty(VALUE)
  );

-- テーブル定義で直接使用
CREATE TABLE calendar_events (
  id         serial PRIMARY KEY,
  title      text             NOT NULL,
  time_range bounded_tsrange  NOT NULL
);
```

<details><summary>SQLの成功と失敗例</summary>

```sql:
-- OK: 境界が両方ある
INSERT INTO calendar_events (title, time_range)
VALUES ('OK例', '[2025-05-15 10:00, 2025-05-15 12:00)'::tsrange);

-- NG: 上端なし
INSERT INTO calendar_events (title, time_range)
VALUES ('NG例', '[2025-05-15 10:00,)'::tsrange);
-- ERROR:  new row for relation "calendar_events" violates check constraint "chk_time_range_bounded"

-- NG: 下端なし
INSERT INTO calendar_events (title, time_range)
VALUES ('NG例2', '(, 2025-05-15 12:00]'::tsrange);

-- NG: 空区間
INSERT INTO calendar_events (title, time_range)
VALUES ('NG例3', '[2025-05-15 10:00, 2025-05-15 10:00)'::tsrange);
```

</details>


## 開区間、閉区間を学ぶ
境界値を含むかどうかは開区間、閉区間で定義しています。
例えば、9時〜10時で実施されているイベントを調べるとします。
データは下記のように用意します。

| ID | 区間表記<br>(`tsrange` 風) | 開始 | 終了 | 端点 | 9:00–10:00 と<br>重複? |
|:-:|:-------------------------|:-----|:-----|:-----|:---:|
| **A** | `[07:00, 08:30]` | 07:00 | 08:30 | **閉/閉** | ✖ |
| **B** | `[08:30, 09:00]` | 08:30 | 09:00 | **閉/閉** | ✔ |
| **C** | `(08:20, 09:00)` | 08:20 | 09:00 | **開/開** | ✖ |
| **D** | `[08:30, 09:30]` | 08:30 | 09:30 | **閉/閉** | ✔ |
| **E** | `[09:30, 10:00]` | 09:30 | 10:00 | **閉/閉** | ✔ |
| **F** | `[10:00, 10:30]` | 10:00 | 10:30 | **閉/閉** | ✔ |
| **G** | `[10:30, 11:00]` | 10:30 | 11:00 | **閉/閉** | ✖ |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/599049/9688339a-3254-416a-8115-e6770bb9d531.png)

:::note info
ここで注目するのは、BとCです。今回、検索の対象は9時〜10時に少しでも引っかかるものを対象としました。
Bは終端が10時の閉区間、Cは終端が10時の開区間。Cは境界値の10時を含まないが、Bは境界値の10時を含んでいるため、検索結果に影響がありました。
:::

<!--
# ────────────────────────────────────────
# 1. ライブラリ
# ────────────────────────────────────────
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
from IPython.display import display

# ────────────────────────────────────────
# 2. イベント定義
#    title, start, end, left_closed?, right_closed?
#    （left/right_closed を省略した行はデフォルト True＝閉区間）
# ────────────────────────────────────────
events_raw = [
    ("A", "2025-05-15 07:00", "2025-05-15 08:30"),
    ("B", "2025-05-15 08:30", "2025-05-15 09:00"),
    ("C", "2025-05-15 08:20", "2025-05-15 09:00", False, False),  # 開区間
    ("D", "2025-05-15 08:30", "2025-05-15 09:30"),
    ("E", "2025-05-15 09:30", "2025-05-15 10:00"),
    ("F", "2025-05-15 10:00", "2025-05-15 10:30"),
    ("G", "2025-05-15 10:30", "2025-05-15 11:00"),
]

# デフォルト値補完（未指定→閉区間）
events = [(*row, True, True) if len(row) == 3 else row for row in events_raw]

df = pd.DataFrame(events,
                  columns=["title", "start", "end", "left_closed", "right_closed"])
df["start"] = pd.to_datetime(df["start"])
df["end"]   = pd.to_datetime(df["end"])

# ────────────────────────────────────────
# 3. 検索区間 09:00–10:00（両端含むとみなす）
# ────────────────────────────────────────
query_start = datetime(2025, 5, 15, 9, 0)
query_end   = datetime(2025, 5, 15, 10, 0)

def overlaps(row):
    """開区間の場合は端点を ±ε ずらして重複判定"""
    eps = timedelta(microseconds=1)
    s = row["start"] + (eps if not row["left_closed"]  else timedelta(0))
    e = row["end"]   - (eps if not row["right_closed"] else timedelta(0))
    return (s <= query_end) and (e >= query_start)

df["overlap_query"] = df.apply(overlaps, axis=1)
display(df[["title", "start", "end", "overlap_query"]])

# ────────────────────────────────────────
# 4. 数直線タイムライン描画
# ────────────────────────────────────────
fig, ax = plt.subplots(figsize=(11, 3))

# ベースライン
ax.hlines(0,
          query_start - timedelta(hours=2),
          query_end   + timedelta(hours=2),
          color="black", linewidth=1)

# 検索区間を太線（オレンジ）でハイライト
ax.hlines(0, query_start, query_end,
          color="tab:orange", linewidth=8, label="09:00–10:00")

# 各イベント
for i, row in df.iterrows():
    y = (i + 1) * 0.5                        # 縦位置
    line_color = "red" if row["overlap_query"] else "black"
    
    # 区間バー
    ax.hlines(y, row["start"], row["end"], color=line_color, linewidth=4)
    
    # 端点マーカー
    #   開区間 → 白丸（facecolor='white', edgecolor='black'）
    #   閉区間 → 黒丸（facecolor='black', edgecolor='black')
    for pos, closed in [(row["start"], row["left_closed"]),
                        (row["end"],   row["right_closed"])]:
        ax.plot(pos, y, 'o',
                markersize=8,
                markerfacecolor='black' if closed else 'white',
                markeredgecolor='black')
    
    # ラベル
    ax.text(row["start"] + timedelta(minutes=3),
            y + 0.1,
            row["title"],
            va="bottom", fontsize=9)

# 軸体裁
ax.set_ylim(0, (len(df) + 2) * 0.5)
ax.get_yaxis().set_visible(False)
ax.set_xlabel("Time")
fig.autofmt_xdate()

# 凡例
legend_handles = [
    plt.Line2D([0], [0], marker='o', color='white',
               markerfacecolor='black', markeredgecolor='black',
               linestyle='None', label='Closed (●)'),
    plt.Line2D([0], [0], marker='o', color='white',
               markerfacecolor='white', markeredgecolor='black',
               linestyle='None', label='Open (○)'),
    plt.Line2D([0], [0], color='red', linewidth=4, label='Overlap 09:00–10:00')
]
ax.legend(handles=legend_handles, loc="upper right")

plt.tight_layout()
plt.show()
-->

## SQLの結果を確認する
```sql:
------------------------------------------------------------
-- データ投入（9:00-10:00 を基準にした 8 本）
------------------------------------------------------------
INSERT INTO calendar_events (title, time_range) VALUES
-- A 07:00–08:30  （閉区間）
('A', '[2025-05-15 07:00, 2025-05-15 08:30]'::tsrange),

-- B 08:30–09:00  （閉区間）
('B', '[2025-05-15 08:30, 2025-05-15 09:00]'::tsrange),

-- C 08:20–09:00  （開区間）
('C', '(2025-05-15 08:20, 2025-05-15 09:00)'::tsrange),

-- D 08:30–09:30  （閉区間）
('D', '[2025-05-15 08:30, 2025-05-15 09:30]'::tsrange),

-- E 09:30–10:00  （閉区間）
('E', '[2025-05-15 09:30, 2025-05-15 10:00]'::tsrange),

-- F 10:00–10:30  （閉区間）
('F', '[2025-05-15 10:00, 2025-05-15 10:30]'::tsrange),

-- G 10:30–11:00  （閉区間）
('G', '[2025-05-15 10:30, 2025-05-15 11:00]'::tsrange);
```
→データを準備します。

```sql:
calendar=# SELECT *
FROM   calendar_events
WHERE  time_range && '[2025-05-15 09:00, 2025-05-15 10:00]'::tsrange;
 id | title |                  time_range                   
----+-------+-----------------------------------------------
  4 | D     | ["2025-05-15 08:30:00","2025-05-15 09:30:00"]
  2 | B     | ["2025-05-15 08:30:00","2025-05-15 09:00:00"]
  6 | F     | ["2025-05-15 10:00:00","2025-05-15 10:30:00"]
  5 | E     | ["2025-05-15 09:30:00","2025-05-15 10:00:00"]
(4 rows)
```
→開始と終端を**含む**、9時〜10時での検索は上のようになります。

```sql:
calendar=# SELECT *
FROM   calendar_events
WHERE  time_range && '(2025-05-15 09:00, 2025-05-15 10:00)'::tsrange;
 id | title |                  time_range                   
----+-------+-----------------------------------------------
  4 | D     | ["2025-05-15 08:30:00","2025-05-15 09:30:00"]
  5 | E     | ["2025-05-15 09:30:00","2025-05-15 10:00:00"]
(2 rows)
```
→開始と終端を**含まない**、9時〜10時での検索は上のようになります。
開区間、閉区間はデータの挿入時、検索時にも意識する必要があります

## Appendix
では、実際にGoogleカレンダーやOffice365のカレンダーはどうやって開区間、閉区間を定義しているのでしょうか？ <strong>iCalendar（RFC 5545）</strong>の仕様に沿って定義しているようです。

・カレンダー実装での区間の持ち方

| ケース | 典型的な保存形式 | 採用理由 |
| :-- | :-- | :-- |
| **日時付きイベント**<br>(会議・予約など) | **\[start, end)**<br>右端排他 | 隣接イベントが衝突しない／<br>長さ = `end - start` の単純計算 |
| **終日イベント** | **\[start-date, next-date)**<br>例) 5/15 の終日は `DTSTART:20250515`, `DTEND:20250516` | iCalendar (RFC 5545) の標準運用 |

・Google カレンダーの実例（ics 抜粋）

```text
DTSTART:20250515T090000Z   → 9:00 開始
DTEND:20250515T100000Z     → 10:00 は **含まない**
```

:::note info
多くのカレンダー／予約システムは [start, end)（右端排他）がデフォルトで定義されているようです。
:::