MySQLでtime型データからint型データの減算を行い、減算後のデータで条件検索する方法
2024/01/16
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で指定の順にデータをソートする方法(ORDER BY FIELD)
MySQLで指定した順にソートしてデータを取り出したい。通常だと主キーだったり何 ...
-
-
MySQLでランダムにデータを取得しつつページング機能も実装する方法
MySQLからデータを持ってくる際にランダムな表示を行ってほしいと言われた。ただ ...
-
-
異なるVPS間でMySQLの接続を行う方法
異なるVPS間でMySQL接続を行った際のメモ。 目次1 対応方法 ...
-
-
MySQLでホスト指定しても正常に接続できない場合
MySQL自体は動いており、ユーザーも登録されているのに、ホスト名を指定しても正 ...
-
-
MySQLのREGEXPで半角記号を検索したい場合の対応方法
MySQLのREGEXPで半角記号を検索したい場合\\を付けてエスケープしないと ...