TSRANGEを使って日時の期間で値を取得してみよう!PostgreSQL

2021-02-08

DATERANGEで開始日と終了日を期間化して値をとっていたら時間も指定しなければいけなくなってしまいました。そのため日時を期間化したのでその時の備忘録です。

TSRANGEについて

TSRANGEはPostgreSQL9.2から追加されたrange type(範囲型)のひとつです。
range typeについては以下でまとめています。

range type の1つ TSrange を使って期間化した値を扱うことができます。TSはTIMESTAMPの略だと思われます。

TSRange の範囲値の書き方

TSRange(下限, 上限, '境界');

下限と上限にはTIMESTAMP型の値が入ります。
境界についてはrange typeについてでまとめていますが、ざっとこんな記号が2つ入ります。

  • [' = 以上
  • ']' = 以下
  • '(' = より大きい
  • ')' = 未満

左が下限、右が上限の境界です。試しに書いてみるとこんな感じ。

TSrange(columnA, columnB, '[]')

3つめの引数を省略するとデフォルトの「[)」が割り当てられます。

TS RANGE 取得のイメージ

例えば TS Range を使用すると「 1/2 0:00 - 1/8 18:45」までの期間の間に期間をもつレコードを取得するということができます。

TSRANGE_IMAGE

TS Rangeのサンプルコード

上記のイメージをサンプルコードで取得してみます。

準備

使用するテーブルです。

term_idtittleterm_date_startterm_date_end
1A2021-01-02T00:00:00Z2021-01-08T18:45:00Z
2B2021-01-03T15:30:00Z2021-01-07T06:15:00Z
3C2021-01-05T08:00:00Z2021-01-09T18:50:00Z
4D2021-01-01T12:30:00Z2021-01-05T05:00:00Z
5E2021-01-08T18:46:00Z2021-01-10T00:00:00Z
6F2020-12-31T00:00:00Z2021-01-01T23:59:00Z

create文です。

-- CREATE文
CREATE TABLE term (
term_id numeric(8, 0) unique not null,
Tittle character varying (20) ,
term_date_start TIMESTAMP,
term_date_end TIMESTAMP,
primary key(term_id)
);
-- INSERT文
insert into term
values(0001, 'A', '2021/01/02 0:00','2021/01/08 18:45'),
(0002, 'B', '2021/01/03 15:30','2021/01/07 6:15'),
(0003, 'C', '2021/01/05 8:00','2021/01/09 18:50'),
(0004, 'D', '2021/01/01 12:30','2021/01/5 5:00'),
(0005, 'E', '2021/01/08 18:46','2021/01/10 0:00'),
(0006, 'F', '2020/12/31 0:00','2021/01/01 23:59')

テスト環境

SQL FIDDLEのPostgreSQL9.6で動作確認しました。

サンプルコード

SELECT
*
FROM
term
WHERE (TSRange(term_date_start, term_date_end, '[]')
&& TSrange(TO_TIMESTAMP('2021/01/02 00:00', 'YYYY/MM/DD HH24 : MI')::timestamp,
TO_TIMESTAMP('2021/01/08 18 : 45', 'YYYY/MM/DD HH24 : MI')::timestamp, '[]'));

where文で二つのTSrangeで範囲を生成し、&&演算子を使用し重複する部分だけ抜き取っています。
演算子についてもRange typeについてでまとめています。
範囲を決めるのに文字列をTO_TIMESTAMP()でSTAMP型にしています。::timestampでは明示的にこれはTIMESTAMPの文字列ですと書いています。
境界は、以上以下で設定しています。0:00をまたぐと日も変わるので以上以下が自然かなと思います。

結果

term_idtittleterm_date_startterm_date_end
1A2021-01-02T00:00:00Z2021-01-08T18:45:00Z
2B2021-01-03T15:30:00Z2021-01-07T06:15:00Z
3C2021-01-05T08:00:00Z2021-01-09T18:50:00Z
4D2021-01-01T12:30:00Z2021-01-05T05:00:00Z

おわり

DATE型からTIMESTAMPにしただけなのにTO_TIMESTAMPにしたり型を指定したり、フォーマットを考えたりで簡単にはいきませんでした。
取得した結果も綺麗にしたかったのでこちらでまとめました。

PostgreSQL, SQL

Posted by Nakamoto