勉強したことのメモ

webプログラマが勉強したことのメモ。

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

  関連記事

文字コードの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%から下がらな ...