MySQLでtime型データからint型データの減算を行い、減算後のデータで条件検索する方法

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文を作成するケースがあまりないのでその辺は難しいところ。