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文を作成するケースがあまりないのでその辺は難しいところ。
関連記事
-
-
文字コードのutf8_general_ciとutf8_unicode_ciの違い
MySQLで設定する時に使っているサーバーによってデフォルトの文字コードがutf ...
-
-
MySQLでユニークキーをはる
MySQLでseqとcodeみたいなものをあわせて ユニークキーをはりたかった。 ...
-
-
MySQLで指定の順にデータをソートする方法(ORDER BY FIELD)
MySQLで指定した順にソートしてデータを取り出したい。通常だと主キーだったり何 ...
-
-
MySQLで同一サーバーの異なるDB内のテーブルをJOINする
同一のMySQLサーバーでdb1データベースのtable1テーブルと、db2デー ...
-
-
権限が無い場合のエラー
SQLでINSERTする際、見たことに無いエラーに遭遇した。 INSERT co ...
-
-
PHPからMySQLデータを取得した際に一括で配列に入れる方法
MySQLからデータを取得して、PHP側で配列に入れる際に今まで一個ずつ書いてい ...
-
-
MySQLでカラム内容によって条件分岐させたい
MySQLでカラムの内容によって条件を分岐させたかった。またPHPで処理するので ...
-
-
市区町村のMySQL用データが欲しい
都道府県と市区町村が連携したセレクトメニューを作成したかった。具体的には大阪府を ...
-
-
MySQLのクエリキャッシュ設定
以前クエリキャッシュを設定していたけど、 これだとmysqlを再起動した時に再度 ...
-
-
phpmyadminからプロセスの停止
重たいSQL文を叩いた際、どうにも処理できず メモリ使用率が100%から下がらな ...