日付の範囲を BETWEEN を使って書こうと考えたのだが
MySQL で期間が重なっていることを判定するクエリを書くというのが本題。 これについてググっているときに拝見したページなのだが、
日付比較や日付検索が遅い場合の改善方法(BETWEEN,MySQL) : ハードディスクメンテナンス ブログ
最後の方に、次のようなクエリ(の一部)が出てくる。
BETWEEN '2014-01-01' AND '2014-01-01' + INTERVAL 1 DAY;
これは2014-01-02 00:00:00 を含んでいるから2004年1月1日を判定したい場合には厳密には正しくない、という趣旨のことが書かれているが、それはその通りだと思う。INTERVAL は両端を含むという仕様だから。
だから私はいつも、こんな感じで書く。
BETWEEN '2014-01-01' AND '2014-01-01' + INTERVAL 1 DAY - INTERVAL 1 SECOND;
こうすれば 2014-01-02 00:00:00 は含まない。1秒の差は大きい。
mysql> SELECT * FROM datecmp WHERE fromtime -> BETWEEN '2014-01-01' AND '2014-01-01' + INTERVAL 1 DAY; +----+---------------------+---------------------+ | id | fromtime | totime | +----+---------------------+---------------------+ | 1 | 2014-01-01 23:59:59 | 2014-07-19 00:00:00 | | 2 | 2014-01-02 00:00:00 | NULL | +----+---------------------+---------------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM datecmp WHERE fromtime -> BETWEEN '2014-01-01' AND '2014-01-01' + INTERVAL 1 DAY - INTERVAL 1 SECOND; +----+---------------------+---------------------+ | id | fromtime | totime | +----+---------------------+---------------------+ | 1 | 2014-01-01 23:59:59 | 2014-07-19 00:00:00 | +----+---------------------+---------------------+ 1 row in set (0.00 sec)
ちなみに、1か月という範囲で調べたいときは、次のようにする。
+ INTERVAL 1 MONTH - INTERVAL 1 SECOND
ただ、そういえばこの "- INTERVAL 1 SECOND" と書いているコードを他で見た記憶がない。 絶対どこかにあると思うのだが。 今、試しにググってみたが、すぐには見つからない。 普通に INTERVAL 1 MONTH とかやっていて、1 SECOND 引いているのが見つからない。 まあ厳密にやらなくても、ってことかもしれないけど。
もしかしたら、検索を速くするために、00:00:00 には絶対にデータを作らないというコードにするのが常識なのかもしれない。 そういう時はわざと 00:00:01 にすればいい。 そんな常識は見たこともないが。
というのは余談で、ここからが本題。 今回のミッションは日時の範囲が重複していることを高速で調べろというものだ。 type が datetime のカラム、fromtime と totime があって、それが指定した期間に入っていることを調べたいのである。
一般論として、二つの範囲AとBが重複していることの判定は、 範囲Aの開始点が範囲Bの終了点以前であり、かつ、範囲Aの終了点が範囲Bの開始点以後であればいい。 これを説明しようとしたのだが、少し長くなったので今回は省略する。 対偶命題を考えると分かりやすい。
例えば、次のような判定になる。
WHERE fromtime <= '2014-07-31 23:59:59' AND totime >= '2014-07-01 00:00:00';
そういえば、先ほどのページ、
SELECT * FROM `テーブル名` WHERE date(`reg_time`) = '2014-01-01'
が遅いというのだが、もちろん、そこに関しては全く異議はない。ただ、
SELECT * FROM `テーブル名` WHERE `reg_time` >= '2014-01-01 00:00:00' AND `reg_time` <= '2014-01-01 23:59:59'
これだと、どうなんだろ、これも遅いのだろうか?
手元にあまりデカいデータがなくて、比較したらどちらも 0.00sec になってしまうので困ったものだ(笑)。
話を戻して、さっきの、
WHERE fromtime <= '2014-07-31 23:59:59' AND totime >= '2014-07-01 00:00:00';
これを BETWEEN を使って書けるのか、という疑問がわいてくる。 しかしなんともう時間がないので今日はここまで。 続きを書くかどうかは気分次第。
- 2014.07.20 Sunday
- プログラミング
- 02:21
- comments(11)
- trackbacks(0)
- by phinloda