MySQLでtime型データの減算と条件検索

2020/09/12

MySQLでtime型データからint型データの減算を行い、減算後のデータを検索条件に含めたいといったケースがあった。具体例、対応策を以下にメモ。

見出し

1 具体例
2 営業中の店舗のみ表示
3 time型データの減算
4 減算後のデータを検索条件に含める
5 所見

具体例

飲食店の情報を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 |
+----+-----------+------------+----------+------------+

mysql> show columns from shop;

+------------+------------------+------+-----+---------+----------------+

+------------+------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+------------+------------------+------+-----+---------+----------------+

mysql> select * from shop;

+----+-----------+------------+----------+------------+

+----+-----------+------------+----------+------------+

| 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();

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;

//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;

//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

: エラー管理まとめ

■エラーログをとる。 error_reporting(E_ALL ^ E_NOT ...

: MySQLで開始から終了日付内を調べたいとき

等号不等号でもできるけど、BETWEENというのがよさそう。 ■参考サイト ht ...

: MySQLで実行結果の行数取得

DBにデータがあるか調べる際にしばしば使用してたのでメモ。 $sql = " S ...

: プリペアドステートメント

個人的に得た内容として、SQL文だけ先にDBに送って、変数は後で送る。だからS ...

: 1回のSQL文で複数のUPDATE

INSERTはバルクインサートを使えばいいが、 UPDATEはあまり聞かないので ...

: MySQLのエラーログ取得関数

場合によってログに出力させたい値が異なる場合があったのでメモ。 function ...

: PHPからMySQLにシリアライズしたデータを格納

アンケートフォームみたいなのがあって、今後も定期開催し、尚且つ質問内容は増減す ...

: MySQLで加算しつつNULL値は0にする

いくつかのコードに対して投票を行い、その合計値をスコアとして出したい。ただ、 ...

: エックスサーバーでMySQLのサイズに注意

エックスサーバーで自動更新のログをMySQLに保存するようなシステムを動かしてい ...

: adminerでMySQLに接続しようとするとエラー

新しく構築したサーバにadminerを入れて接続しようとすると、「mysqlnd ...

PREV: さくらインターネットでCronを動かすも挙動が変
NEXT: MySQLで特定カラムのbrタグを改行コードに一括置換

MySQLでtime型データの減算と条件検索

具体例

営業中の店舗のみ表示

time型データの減算

減算後のデータを検索条件に含める

所見

関連記事