エスプリフォートでは、業務内でのシステム開発以外でも、日々アンテナを張りながら仲間同士お互いに技術力を磨き合っています。
そして高めた技術力を顧客のために如何に生かしていくのかということを考え、日々の開発に取り入れることで顧客から頼られる存在としてあり続けています。
今回はPostgreSQLが持つ機能拡張のうち、tstzrange型をご紹介いたします。
はじめに
DB操作言語であるSQLの標準規格改訂は度々行われており、本記事を執筆中の最新規格は「SQL:2023」となっているようです。（SQL - Wikipedia）
また標準規格だけではなく、独自の機能拡張をしているDB製品も数多くあります｡
そこで今回は、PostgreSQLが持つ機能拡張のうち、tstzrange型を試してみようと思います｡
tstzrange型とは
tstzrange型は、PostgreSQLで用意されているデータ型のひとつで、「値の範囲を表わす」ことができます（詳細はこちら）。
今回試してみること
とある日のとある予約サイトで、予約状況が以下のようになっている時、Cさんが「この時間帯で予約できるか」を返すSQLを考えてみたいと思います｡
準備
予約状況を登録する「予約テーブル」を以下のように作成します｡
| ID | ユーザ | 予約期間 |
|---|---|---|
| 1 | A | 2025-01-13 10:15 ～ 2025-01-13 10:45 |
| 2 | B | 2025-01-13 11:00 ～ 2025-01-13 11:15 |
-- テーブル作成
CREATE TABLE reservations (
id SERIAL PRIMARY KEY,
user_name TEXT NOT NULL,
reservation_time tstzrange NOT NULL
);
-- レコード追加
INSERT INTO reservations (id, user_name, reservation_time) VALUES
(1, 'A', tstzrange('2025-01-13 10:15:00+09','2025-01-13 10:45:00+09','[)')),
(2, 'B', tstzrange('2025-01-13 11:00:00+09','2025-01-13 11:15:00+09','[)'))
ちなみに、 レコード追加SQLの "[" と ")" は、以下を表わします（詳細はこちら）｡
- [ ... 期間に含まれる（閉じた下限境界）
- ) ... 期間に含まれない（開いた上限境界）
予約できるかできないか
ではさっそく、SQLを書いてみましょう｡
10:30～11:00で予約できるかを検証 => できないはず
まずは、Cさんが10:30～11:00に予約できるか、SQLで書いてみます｡
返却結果は、0：予約不可能、1:予約可能、とします。
正しい結果としては、Aさんの予約と重複するため、0：予約不可能、が返却されるはずです｡
select
case
when count(*) > 0
then 1
else 0
end as enable_reservation
from
reservations
where
not exists (
select
1
from
reservations
where
reservation_time && tstzrange(
'2025-01-13 10:30:00+09'
, '2025-01-13 11:00:00+09'
, '[)'
)
)
| enable_reservation |
|---|
| 0 |
想定通りの結果が取得できました。
10:45～11:00で予約できるかを検証 => できるはず
続いて、Cさんが10:45～11:00に予約できるかどうか、SQLで書いてみます｡
正しい結果としては、どの予約とも重複しないため、1：予約可能、が返却されるはずです｡
select
case
when count(*) > 0
then 1
else 0
end as enable_reservation
from
reservations
where
not exists (
select
1
from
reservations
where
reservation_time && tstzrange(
'2025-01-13 10:45:00+09'
, '2025-01-13 11:00:00+09'
, '[)'
)
)
| enable_reservation |
|---|
| 1 |
こちらも想定通りの結果が取得できました。
まとめ
というわけで、今回はPostgreSQLのtstzrange型を試してみました｡
一般的に「期間」を扱う場合は「開始時間」や「終了時間」といった列をテーブル上に定義する事が多いのですが、この時、例えば「終了時間が未定義の場合をどう扱うか、NULL値にするか、なんらかの固定値を埋め込むか」といった考慮が必要になりますし、またその分、SQLも煩雑になりがちです｡
tstzrange型を使えば、スッキリしますね。
DBがPostgreSQL限定にはなってしまいますが、適用できる場合はぜひ検討してみてください。