先日、SQLで「前後２日以内のレコードを取得したい」と思い、datetime型の加減算を行ったところ、想定外の件数が返ってきてサーバーのメモリを食いつぶしかけました・・・
その時の失敗記録です。
n日以内のレコードを取得したかった
スケジュール系のレコードを保持しているテーブルがあり、内容はこんな感じ
| スケジュール名 | 開始日（datetime） | 終了日（datetime） |
|---|---|---|
| 出張1 | 2018-09-20 13:30:00 | 2018-09-25 24:00:00 |
| 展示会 | 2018-09-23 10:00:00 | 2018-09-29 18:00:00 |
| 休暇 | 2018-09-29 00:00:00 | 2018-10-01 24:00:00 |
| 社内研修 | 2018-09-30 09:00:00 | 2018-10-03 18:30:00 |
| 出張2 | 2018-10-03 19:00:00 | 2018-10-06 16:30:00 |
以前、全件取得しようとしたら、あまりの重さに耐えきれず、DBサーバーが落っこちたのです・・・
そこで、「開始日が本日含め前後２日以内」という条件でレコードを取得しようとしました。
なので、本日＝2018/10/01であれば、以下の２レコードが取得できるはず・・・！
| スケジュール名 | 開始日 | 終了日 |
|---|---|---|
| 休暇 | 2018-09-29 00:00:00 | 2018-10-01 24:00:00 |
| 社内研修 | 2018-09-30 09:00:00 | 2018-10-03 18:30:00 |
NGパターン：現在日時curdate()に対して加減算を実行した
select * from xxxTbl
where
開始日 >= (curdate()-2) -- ２日前
and 開始日 <= (curdate()+2) -- ２日後
;
最初に書いたのが、上記
のSQLです。
この書き方の場合、curdate()が2018/10/1だと、２日前の日付が正しく取得できていませんでした。
内部ではこのように動いていたようです
select * from xxxTbl
where
開始日 >= (20181001-2) -- 開始日 >= 20180999
and 開始日 <= (20181001+2) -- 開始日 <= 20181003
;
数値として計算してしまったので、20180999となってしまい、ありえない日付になってしまいました。
その結果、取得できていたのがこちら
| スケジュール名 | 開始日（datetime） | 終了日（datetime） |
|---|---|---|
| 出張1 | 2018-09-20 13:30:00 | 2018-09-25 24:00:00 |
| 展示会 | 2018-09-23 10:00:00 | 2018-09-29 18:00:00 |
| 休暇 | 2018-09-29 00:00:00 | 2018-10-01 24:00:00 |
| 社内研修 | 2018-09-30 09:00:00 | 2018-10-03 18:30:00 |
| 「２日前」の判定が出来なくて、過去データをすべて取得していました。 | ||
| そりゃあ、DBサーバー落っこちるわ（－－； |
だけど、今思うと、datetime型に対して±２って、日付なのか時間なのか、時間だとしても分なのか秒なのかさっぱりわからないですね・・・。
さらにいうと、このSQLを作っていたのが2018/09/15のような月のど真ん中だったため、curdate()±2でも正しく数値計算が出来てしまっていたのです。
OKパターン：datediffを使って現在日時curdate()との差分を取得する
select * from xxxTbl
where
datediff(curdate(), 開始日) <= 2 -- ２日前
and datediff(curdate(), 開始日) >= -2 -- ２日後
datediff() 関数は、２つの日付の差を求める関数です。
第１引数の日付の方が大きければ正の値が返ります。
datediff()を使うと、想定通りの結果が取得できました
datediff()関数の実行結果の具体例
実際にdatediff()を実行してみると以下のようになります
月をまたいでいても、正しく計算されていることがわかります。
| 第１引数 | 第２引数 | 結果 |
|---|---|---|
| 2018-10-13 | 2018-09-30 00:00:00 | 13 |
| 2018-10-13 | 2018-10-11 00:00:00 | 2 |
| 2018-10-13 | 2018-10-12 00:00:00 | 1 |
| 2018-10-13 | 2018-10-13 00:00:00 | 0 |
| 2018-10-13 | 2018-10-14 00:00:00 | -1 |
| 2018-10-13 | 2018-10-15 00:00:00 | -2 |
おまけ：テスト不足
こういう「前後２日以内」みたいな場合は、境界値テストを必ず実施するはずです。
私も、「３日前のレコードは取得できない」など、入念に確認しました。
しかし、月をまたいだ場合・年をまたいだ場合のテストは全く実施していませんでした・・・ 
そもそもの話、テストケースの洗い出しが不十分だったというわけです。
例え、実装の時にこういうバグを仕込んでしまっても、テストで月またぎ・年またぎのケースを実施していれば、気づけたはずだったなぁと。
教訓
- MySQLで日付計算をする時は
datediff()を使う。 - 日付計算をする時は、月や年をまたいだパターンもテストすること。