日付単位で開始日と終了日を設定して値をとりたいなと思いました。
しかし、これが思ったより大変で大なり(>)小なり(<)を使って取得しようとすると長文になってしまい作成するのも大混乱になったためDATERANGEで取得した時のお話です。
DATERANGEについて
DATERANGEはPostgreSQL9.2から追加されたrange type（範囲型）のひとつです。DATERANGEを使うと日付を期間化できます。
range typeについては以下でまとめています。
range type の1つ Date range を使うと期間化した値を取得することができます。
Date Range 取得のイメージ
例えば Date Range を使用すると「 1/2 – 1/8 」の期間の間に期間をもつレコードを取得するということができます。
期間がかぶっている「 1/1 – 1/5 」を取得するというようなことが簡単にできるようになります。
この図の通りに作成した Date Range のサンプルコードをサンプルコードの章で紹介しています。
Date Range の範囲値の書き方
DateRange(下限, 上限, ‘境界’);
下限と上限にはdate型の値が入ります。
境界についてはrange typeについてでまとめていますが、ざっとこんな記号が2つ入ります。
- [‘ = 以上
- ‘]’ = 以下
- ‘(‘ = より大きい
- ‘)’ = 未満
左が下限、右が上限の境界です。試しに書いてみるとこんな感じ。
daterange(columnA, columnB, ‘[]’)
3つめの引数を省略するとデフォルトの「[)」が割り当てられます。
Date Rangeのサンプルコード
それでは先ほど紹介したイメージ通りに動くサンプルコードを作成します。
準備
使用するテーブルです。
| term_id | tittle | term_date_start | term_date_end |
|---|---|---|---|
| 1 | A | 2021-01-02 | 2021-01-08 |
| 2 | B | 2021-01-03 | 2021-01-07 |
| 3 | C | 2021-01-05 | 2021-01-09 |
| 4 | D | 2021-01-01 | 2021-01-05 |
| 5 | E | 2021-01-09 | 2021-01-10 |
| 6 | F | 2020-12-31 | 2021-01-01 |
create文です。
— CREATE文
CREATE TABLE term (
term_id numeric(8, 0) unique not null,
Tittle character varying (20) ,
term_date_start date,
term_date_end date,
primary key(term_id)
);
— INSERT文
insert into term
values(0001, ‘A’, ‘2021/01/02′,’2021/01/08’),
(0002, ‘B’, ‘2021/01/03′,’2021/01/07’),
(0003, ‘C’, ‘2021/01/05′,’2021/01/09’),
(0004, ‘D’, ‘2021/01/01′,’2021/01/5’),
(0005, ‘E’, ‘2021/01/09′,’2021/01/10’),
(0006, ‘F’, ‘2020/12/31′,’2021/01/01’)
テスト環境
SQL FIDDLEのPostgreSQL9.6で動作確認しました。
サンプルコード
SELECT
*
FROM
term
WHERE
(daterange(term_date_start, term_date_end, ‘[]’) && daterange(‘2021/01/02’, ‘2021/01/08’, ‘[]’));
where文で二つのdaterangeで範囲を生成し、&&演算子を使用し重複する部分だけ抜き取っています。
境界は以上以下です。
演算子についてもRange typeについてでまとめています。
結果
| term_id | tittle | term_date_start | term_date_end |
|---|---|---|---|
| 1 | A | 2021-01-02 | 2021-01-08 |
| 2 | B | 2021-01-03 | 2021-01-07 |
| 3 | C | 2021-01-05 | 2021-01-09 |
| 4 | D | 2021-01-01 | 2021-01-05 |
おわり
range type 奥が深すぎてまだまだ追いきれていないですがなんかっこいい。
奥が深いので上記のテーブルで境界や演算子などテストして動きを確認してみると理解しやすくなるかと思います。
