PostgreSQLの範囲型

PostgreSQLには範囲型という型が用意されています。

数値や日付の範囲型もありますが、今回は日時の範囲型であるtsrangeを使ってみます。

TABLEの作成

いつもと同じように、テーブルを作成するときの型にtsrangeを指定するだけです。

postgres=# CREATE TABLE hoge (
postgres(#     id INT PRIMARY KEY,
postgres(#     duration TSRANGE NOT NULL
postgres(# );
CREATE TABLE

INSERTはどうするか

こちらもいつもと同じようにINSERTを実行すれば大丈夫です。

postgres=# INSERT INTO hoge (id, duration)
postgres-# VALUES (1, '[2018-01-01, 2018-02-01)');
INSERT 0 1

SELECTはどうするか

ためしにそのままSELECTを試します。

postgres=# SELECT * FROM hoge;
 id |                   duration
----+-----------------------------------------------
  1 | ["2018-01-01 00:00:00","2018-02-01 00:00:00")

大丈夫そうです。

下限や上限だけ取りたい

組み込み関数を使うと上限や下限が取得できます

postgres=# SELECT LOWER(duration) FROM hoge;
        lower
---------------------
 2018-01-01 00:00:00
(1 row)

postgres=# SELECT UPPER(duration) FROM hoge;
        upper
---------------------
 2018-02-01 00:00:00
(1 row)

任意の日時が範囲内か知りたい

範囲型に対して@>を使って範囲内か確認できます

postgres=# SELECT * FROM hoge WHERE duration @> '2018-01-01 0:0:0' :: TIMESTAMP;
 id |                   duration
----+-----------------------------------------------
  1 | ["2018-01-01 00:00:00","2018-02-01 00:00:00")
(1 row)

postgres=# SELECT * FROM hoge WHERE duration @> '2018-02-01 0:0:0' :: TIMESTAMP;
 id | duration
----+----------
(0 rows)

検索条件が範囲内のときのみレコードを取得できました。

今回はすべての機能は試していませんが、他にもたくさん関数や演算子が用意されているので便利に使えそうです。

MPのご利用は計画的に

だいたい自分用のメモ

PostgreSQLで日付の範囲型を使う