MYSQLで、データ取得をする際に「条件」をつけて検索することがあります。
例えば、該当フラグが立っているもの、キーワードが含まれているものなどです。
今回、「当日・現在時刻より未来のもの」という条件に出会いました。
PHPならば当日取得して、if文で比較すればOKですが、ふと思いました。
MYSQLのwhere指定で、当日・現在時刻比較する方法ってあるの？
と。
PHPで当日・現在時刻を取得し、whereに「文字」として盛り込む方法がありますが、
できれば、MYSQLだけで比較したい。
何か方法がないものかと、いつものごとくググり調べ、対応することができました。
忘備録として記録いたします。
MYSQLだけで当日・現在時刻を求める予約語
MYSQLで、当日・現在時刻を求める方法がないものか調べてみたら、すぐ見つかりました。
次の予約語で求められます。
- CURRENT_DATE・・当日が取得できます。
- CURRENT_TIME・・現在時刻が取得できます。
- CURRENT_TIMESTAMP・・当日＋現在時刻が取得できます。
実際にこの予約語を使用して実験してみました。
当日・現在時刻を求める方法がわかれば、あとは簡単です。
実際にwhereに当日・現在時刻条件を盛り込んで実験してみました。
テストで使用するテーブル構造
今回テストでは、以下のテーブルを使用しました。
日付のみ、時刻のみ、日付＋時刻の3パターンを盛り込んだテーブルです。
次のデータを入力しました
過去日と未来日、過去時間と未来時間を様々なパターンで入れてみました。
未来日だけど過去時間、過去日だけど未来時間というデータも盛り込んでいます。
テスト（１）：未来日を取得（日付のみ比較）
日付欄のみを使用し、未来日取得をしてみました。
SQL文は以下の通りです。
SELECT * FROM `date_test` WHERE date >= CURRENT_DATE実行結果
予定通りに未来日だけを取得することができました。
テスト（２）：未来日を取得（日付＋時間）
次に、日付＋時間（date_time）を使って比較をしてみました。
SQL文は以下の通りです。
SELECT * FROM `date_test` WHERE date_time >= CURRENT_TIMESTAMP実行結果
日付＋時刻の変数でもうまくいきました。
テスト（３）：未来時間を取得（時間のみ）
では、最後に時間のみで未来時間比較を行ってみました。
SQL文は以下の通りです。
SELECT * FROM `date_test` WHERE time >= CURRENT_TIME実行結果
原因は時刻のみの比較であるため
時刻のみを比較した場合は、確かに未来時間を取得できましたが、過去日のデータもヒットしました。
これは、時刻のみを比較しているため、未来日か過去日かを比較していないためです。
動きとしては正しいのですが、あまり現実的な使い方ではありません。
何故ならば、現在時間を比較するということは、未来日であることが前提になるためです。
過去日付がヒットしては困るのです。
試行錯誤（１）：未来日だけを取得できないか実験してみました。
そもそもdatetimeを使用すれば良い話なのですが、あえて日付を時間を分離して使用するケースもあります。その場合の未来日、未来時間が取得できないかと思いました。
ぱっと思いついたの以下のSQL文で試してみました。
SELECT * FROM `date_test` WHERE (date >= CURRENT_DATE) AND (time >= CURRENT_TIME) 未来日かつ未来時間で検索すればよいだろうと考えました・・・・
実行結果
未来日のデータは2つあったのに、１つしかヒットしませんでした。
なぜでしょう？
原因は日付と時刻をバラバラに比較しているため
未来日 AND 未来時間 という条件で検索しましたが、じつは落とし穴がありました。
これだと、
「未来日 過去時間」がヒットしません。
未来日だからそもそも未来時間なのですが、日付と時刻を分離して比較しているため過去時間扱いとなるのです。
何とかするためには、日付と時刻を合体させて比較する必要があります。
でも、そんなことが可能なのでしょうか?
これは思い付きですが、MYSQLには項目を合体させる関数があります。
それを使えばうまくいくのではないかと考え、実践してみました。
試行錯誤（２）：concat()関数で日付と時刻を合体させて比較させてみました
concat()関数は、複数の要素を一つにまとめてくれます。
日付と時間をバラバラに比較するのがだめならば、まとめてしまえばよいと考えました。
SQL文はこちら
SELECT * FROM `date_test` WHERE concat(date, time) >= concat(CURRENT_DATE, CURRENT_TIME)結構力業な合体です。
予約語同士も合体させています。
実行結果
予定通りの動きをしてくれました。
日付と時間を合体させて比較する方法は大丈夫なようです。
MYSQLは閃きと試行錯誤で何とかなります。
今回は「未来抽出」を目的としていましたが、思わぬ落とし穴に出くわしました。
しかし、意外な発想で対処することができました。
プログラムもそうですが、SQLも柔軟な発想と試行錯誤を繰り返せば、解決方法が見つかるものだと改めて感じました。
そのためには、様々な経験を積むことも重要です。
なぜならば、今回のconcat()を使う発想は、以前文字結合の際に使ったことがあったから閃くことができました。
答えをみて作ることは最速ですが、つまづくとそこで止まってしまいます。
回り道しても自力で試行錯誤すれば、つまづいたときに解決策を見出すことができます。
よい教訓になりました。