MySQLでtime型データの減算と条件検索
MySQLでtime型データからint型データの減算を行い、減算後のデータを検索条件に含めたいといったケースがあった。具体例、対応策を以下にメモ。
具体例
飲食店の情報をMySQLで取り扱う案件で営業開始時間(time型)、営業終了時間(time型)、営業終了から○分前に注文受付終了になるラストオーダー(int型)のデータを格納した。以下がサンプルとなる。
mysql> show columns from shop; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | start_time | time | YES | | NULL | | | end_time | time | YES | | NULL | | | last_order | int(11) unsigned | NO | | NULL | | +------------+------------------+------+-----+---------+----------------+ mysql> select * from shop; +----+-----------+------------+----------+------------+ | id | name | start_time | end_time | last_order | +----+-----------+------------+----------+------------+ | 1 | 牛角 | 10:00:00 | 22:00:00 | 30 | | 2 | 叙々苑 | 09:00:00 | 19:00:00 | 15 | +----+-----------+------------+----------+------------+
営業中の店舗のみ表示
営業中の店舗のみ表示させたい場合、よくあるパターンだと以下になるかと思われる。
SELECT * FROM shop WHERE start_time <= now() AND end_time >= now();
ただ今回は営業終了時間ではなく、注文受付終了時の時間を軸にしたかった。つまり「営業終了時間 - ラストオーダーの分数」を抽出し、その値を検索条件に含めたい。
time型データの減算
//MySQL5.1の場合 SELECT *, SEC_TO_TIME ( TIME_TO_SEC(end_time) - ( last_order * 60) ) AS last_time FROM shop; //MySQL5.7の場合 SELECT *, ( end_time - INTERVAL last_order MINUTE ) AS last_time FROM shop;
上記SQL文を打つとlast_timeカラムに減算後の時間が入る。
尚、MySQLのバージョンによって対応方法が異なるっぽい。ver5.7の記述を5.1で行ってもNULLが返ってくるので注意。
5.1だとend_timeとlast_orderを秒数に変換した後に減算し、その結果をtime型に変換しなおすといった非常に面倒くさい手順となる。
減算後のデータを検索条件に含める
//MySQL5.1の場合 SELECT *, SEC_TO_TIME ( TIME_TO_SEC(end_time) - ( last_order * 60) ) AS last_time, IF( start_time <= now() && SEC_TO_TIME ( TIME_TO_SEC(end_time) - ( last_order * 60) ) >= now(), 1, 0 ) AS open_flg FROM shop HAVING open_flg = 1; //MySQL5.7の場合 SELECT *, ( end_time - INTERVAL last_order MINUTE ) AS last_time, IF( start_time <= now() && ( end_time - INTERVAL last_order MINUTE ) >= now(), 1, 0 ) AS open_flg FROM shop HAVING open_flg = 1;
「現在時刻がstart_timeより大きい(営業開始時間を過ぎている)」かつ「現在時刻がlast_timeより小さい(注文受付終了時間前)」の場合はopen_flgカラムは1、それ以外の場合は0となる。
尚且つHAVINGでopen_flgが1のものを抽出している。WHEREだと抽出できないので注意する。IF~の参考演算子の部分は過去記事参考。
尚、HAVINGをつけずにORDER BYにopen_flgを指定することで営業時間中のものを優先表示、といったことも対応が可能となる。
所見
MySQLでif構文を使う経験がほとんど無かったので「サブクエリでなんとかならないか」「面倒くさいからPHP側で処理しちゃおうか」と紆余曲折し、解決まで非常に時間がかかった。今後に活かしたいとは思うが難解なSQL文を作成するケースがあまりないのでその辺は難しいところ。
関連記事
-
-
MySQLでtext型カラムに入っている数値をint型としてソートする
MySQLでtext型として指定されているカラムがあり、その中には文字列であった ...
-
-
htmlspecialcharsを通してMySQLに格納したものを元に戻して出力
MySQLにHTMLタグを入れたくない場合にhtmlspecialcharsを使 ...
-
-
MySQLでスレッドの実行状況を表示
データベースサーバが重たく、原因を調査する機会があった。サーバ自体のメモリやCP ...
-
-
MySQLでREGEXPについて
SQL文にREGEXPというのを見てこれ何? となったのでメモ。 ■参考サイト ...
-
-
MySQLで1対多のデータ結合と条件検索
MySQLで飲食店舗テーブルと休業日テーブルがあり、1店舗に対して複数行の休業日 ...
-
-
MySQLで加算しつつNULL値は0にする
いくつかのコードに対して投票を行い、 その合計値をスコアとして出したい。 ただ、 ...
-
-
お名前SDサーバにMySQLデータのインポート
お名前.comのレンタルサーバにそこそこ大きいサイズのMySQLデータを移行した ...
-
-
文字コードのutf8_general_ciとutf8_unicode_ciの違い
MySQLで設定する時に使っているサーバーによってデフォルトの文字コードがutf ...
-
-
InnoDBのオートインクリメント
MySQLのInnoDBでIDというカラムにオートインクリメント設定していたが、 ...
-
-
MySQLのREGEXPで半角記号を検索したい場合
MySQLのREGEXPで半角記号を検索したい場合 \\を付けてエスケープしない ...