勉強したことのメモ

webプログラマが勉強したことのメモ。

MySQLでtime型データの減算と条件検索

   

MySQLでtime型データからint型データの減算を行い、減算後のデータを検索条件に含めたいといったケースがあった。具体例、対応策を以下にメモ。

 

具体例

飲食店の情報をMySQLで取り扱う案件で営業開始時間(time型)、営業終了時間(time型)、営業終了から○分前に注文受付終了になるラストオーダー(int型)のデータを格納した。以下がサンプルとなる。

 

営業中の店舗のみ表示

営業中の店舗のみ表示させたい場合、よくあるパターンだと以下になるかと思われる。

ただ今回は営業終了時間ではなく、注文受付終了時の時間を軸にしたかった。つまり「営業終了時間 - ラストオーダーの分数」を抽出し、その値を検索条件に含めたい。

 

time型データの減算

上記SQL文を打つとlast_timeカラムに減算後の時間が入る。

尚、MySQLのバージョンによって対応方法が異なるっぽい。ver5.7の記述を5.1で行ってもNULLが返ってくるので注意。

5.1だとend_timeとlast_orderを秒数に変換した後に減算し、その結果をtime型に変換しなおすといった非常に面倒くさい手順となる。

 

減算後のデータを検索条件に含める

「現在時刻がstart_timeより大きい(営業開始時間を過ぎている)」かつ「現在時刻がlast_timeより小さい(注文受付終了時間前)」の場合はopen_flgカラムは1、それ以外の場合は0となる。

尚且つHAVINGでopen_flgが1のものを抽出している。WHEREだと抽出できないので注意する。IF~の参考演算子の部分は過去記事参考。

尚、HAVINGをつけずにORDER BYにopen_flgを指定することで営業時間中のものを優先表示、といったことも対応が可能となる。

 

所見

MySQLでif構文を使う経験がほとんど無かったので「サブクエリでなんとかならないか」「面倒くさいからPHP側で処理しちゃおうか」と紆余曲折し、解決まで非常に時間がかかった。今後に活かしたいとは思うが難解なSQL文を作成するケースがあまりないのでその辺は難しいところ。

 - MySQL

  関連記事

エラー管理まとめ

■エラーログをとる。 error_reporting(E_ALL ^ E_NOT ...

MySQLで開始から終了日付内を調べたいとき

等号不等号でもできるけど、BETWEENというのがよさそう。 ■参考サイト ht ...

MySQLで実行結果の行数取得

DBにデータがあるか調べる際にしばしば使用してたのでメモ。 $sql = " S ...

プリペアドステートメント

個人的に得た内容として、SQL文だけ先にDBに送って、 変数は後で送る。だからS ...

1回のSQL文で複数のUPDATE

INSERTはバルクインサートを使えばいいが、 UPDATEはあまり聞かないので ...

MySQLのエラーログ取得関数

場合によってログに出力させたい値が異なる場合があったのでメモ。 function ...

PHPからMySQLにシリアライズしたデータを格納

アンケートフォームみたいなのがあって、 今後も定期開催し、尚且つ質問内容は増減す ...

MySQLで加算しつつNULL値は0にする

いくつかのコードに対して投票を行い、 その合計値をスコアとして出したい。 ただ、 ...

エックスサーバーでMySQLのサイズに注意

エックスサーバーで自動更新のログをMySQLに保存するようなシステムを動かしてい ...

adminerでMySQLに接続しようとするとエラー

新しく構築したサーバにadminerを入れて接続しようとすると、「mysqlnd ...