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;

尚、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;

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

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

所見

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