Craftworks

設計している DB に時間のデータを絡める必要があったので色々調べてみた。やりたいことは、ある時刻を入力してその時刻に営業している店舗を検索するという感じです。

PostgreSQL には、INTERVAL 型と OVERLAPS 演算子というものがあり、OVERLAPS 演算子はこんなことができるらしい。

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
Result: true

これなら簡単にやりたいことが実現できそうだ、と一瞬感動する。

しかし、よくドキュメントを読んでみると、INTERVAL 型は開始時間と終了時間を入力するとかそういうモノではなく、単純に1時間とか、1日間とか時間幅を受け付けるデータ型らしい。今回は出番はなさそうです。

そして、実際にテーブルを組んでみる。10時は営業時間内か、問い合わせたい場合はこんな感じでしょう。

SELECT * FROM test WHERE (open_time, close_time) OVERLAPS ('10:00', '10:00);

上手く行きました。今度は、日付をまたぐ営業時間（19:00～26:00など）のデータから問い合わせてみます。結果は期待した通りにはなりませんでした。
ググってみたところ、どうやら TIME 型は日付情報を持たないので、開始時間より終了時間が見た目に若いとOVERLAPS が期待する動作をしてくれないらしいそうです。open_time などが TIMESTAMP 型だと勿論そんなことはないです。

結局、下記のようにしました。なんか釈然としない。

SELECT * FROM test WHERE CASE WHEN open_time < close_time THEN
open_time <= '12:00' AND '12:00' <= close_time ELSE
open_time <= '12:00' OR '12:00' <= close_time END;

※今回の教訓
・TIME 型への OVERLAPS は日付けをまたげない。

2005/07/30 13:23 追記

と、思ったらもっとスマートに書けました。WHERE 以降は下記です。

(close_time - open_time)::time >= ('12:00'::time - open_time::interval)

tips PostgreSQL