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」までの期間の間に期間をもつレコードを取得するということができます。
TS Rangeのサンプルコード
上記のイメージをサンプルコードで取得してみます。
準備
使用するテーブルです。
| term_id | tittle | term_date_start | term_date_end |
|---|---|---|---|
| 1 | A | 2021-01-02T00:00:00Z | 2021-01-08T18:45:00Z |
| 2 | B | 2021-01-03T15:30:00Z | 2021-01-07T06:15:00Z |
| 3 | C | 2021-01-05T08:00:00Z | 2021-01-09T18:50:00Z |
| 4 | D | 2021-01-01T12:30:00Z | 2021-01-05T05:00:00Z |
| 5 | E | 2021-01-08T18:46:00Z | 2021-01-10T00:00:00Z |
| 6 | F | 2020-12-31T00:00:00Z | 2021-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_id | tittle | term_date_start | term_date_end |
|---|---|---|---|
| 1 | A | 2021-01-02T00:00:00Z | 2021-01-08T18:45:00Z |
| 2 | B | 2021-01-03T15:30:00Z | 2021-01-07T06:15:00Z |
| 3 | C | 2021-01-05T08:00:00Z | 2021-01-09T18:50:00Z |
| 4 | D | 2021-01-01T12:30:00Z | 2021-01-05T05:00:00Z |
おわり
DATE型からTIMESTAMPにしただけなのにTO_TIMESTAMPにしたり型を指定したり、フォーマットを考えたりで簡単にはいきませんでした。
取得した結果も綺麗にしたかったのでこちらでまとめました。
