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 が必要でクエリが冗長

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

Hand-on

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

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

開区間、閉区間を学ぶ

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

ID	区間表記 (tsrange 風)	開始	終了	端点	9:00–10:00 と 重複?
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	閉/閉	✖

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

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

→データを準備します。

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時での検索は上のようになります。

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のカレンダーはどうやって開区間、閉区間を定義しているのでしょうか？ iCalendar（RFC 5545）の仕様に沿って定義しているようです。

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

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

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

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

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