【MySQL】最も近い時刻を取得する

Question

MySQLで以下のテーブルを作成しました。

create TABLE `Timer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` time NOT NULL
  PRIMARY KEY (`id`)
)

レコードには以下が入っています。

id	time
1	20:00
2	21:00
3	0:00
4	1:00

ここで、現在時刻から未来の時刻に対して、最も近いレコードを取り出したいです。
例えば現在時刻が「20:30」であれば「id=2,time='21:00'」のレコードを取りだし、
現在時刻が「23:30」であれば「id=3,time='0:00'」の方を取り出したいです。

いろいろ試してみましたが、0時を跨ぐ場合の考慮が難しく解決できませんでした。
お分かりの方がいましたら教えて頂けませんか？

Answer 1

内容が他の方と被っているのですが、LIMIT 1 の回答ばかりでtimeの同値考慮がなかったので。

SELECT * FROM Timer a
 WHERE IF(a.`time` >= TIME_FORMAT(NOW(),'%H:%i')
 , SUBTIME(a.`time`,TIME_FORMAT(NOW(),'%H:%i'))
 , SUBTIME(ADDTIME(a.`time`,'24:00'),TIME_FORMAT(NOW(),'%H:%i'))) =
(SELECT MIN(IF(b.`time` >= TIME_FORMAT(NOW(),'%H:%i')
 , SUBTIME(b.`time`,TIME_FORMAT(NOW(),'%H:%i'))
 , SUBTIME(ADDTIME(b.`time`,'24:00'),TIME_FORMAT(NOW(),'%H:%i'))))
   FROM Timer b);

Comments

[souri-t]

ありがとうございます。他とは違うアプローチですね。
こちらも期待の抽出ができました。

Answer 2

実質的にA.Ichiさんと同じですが、

SELECT *
FROM Timer
ORDER BY IF(TIME_FORMAT(NOW(),'%H:%i:00') > time, ADDTIME(time,'24:00'), time) ASC
LIMIT 1;

Comments

[souri-t]

ありがとうございます。とてもシンプルながら期待のレコードを抽出できました。

Answer 3

`time` time NOT NULL

の後に[,]がないです。

試行錯誤して、答えが出なかったのであれば、
そもそも0:00ではなく24:00としてデータ登録しても良いとは思いますが。
0:00にこだわるのであれば、以下のSQLで抽出できます。

		SELECT	0 as time_sort
			,	T1.id
			,	T1.time
			,	DATE_FORMAT( NOW(), '%H:%i' ) as nwo_time
		FROM	Timer T1
-- 		WHERE	CAST( DATE_FORMAT( NOW(), '%H%i' ) as SIGNED ) <= CAST( DATE_FORMAT( T1.time, '%H%i' ) as SIGNED )
		WHERE	CAST( '2330' as SIGNED ) <= CAST( DATE_FORMAT( T1.time, '%H%i' ) as SIGNED )
;

create TABLE `Timer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` time NOT NULL,
  PRIMARY KEY (`id`)
);
insert into Timer
values
 (1,'20:00')
,(2,'21:00')
,(3,'0:00')
,(4,'1:00')
;

SELECT	V0.id
	,	V0.time
    ,	V0.now_time
FROM	
(
	(
		SELECT	0 as time_sort
			,	T1.id
			,	T1.time
			,	DATE_FORMAT( NOW(), '%H:%i' ) as now_time
		FROM	Timer T1
-- 		WHERE	CAST( DATE_FORMAT( NOW(), '%H%i' ) as SIGNED ) <= CAST( DATE_FORMAT( T1.time, '%H%i' ) as SIGNED )
		WHERE	CAST( '1000' as SIGNED ) <= CAST( DATE_FORMAT( T1.time, '%H%i' ) as SIGNED )
        ORDER BY T1.time
	)
	UNION ALL
	(
		SELECT	1 as time_sort
			,	T1.id
			,	T1.time
			,	DATE_FORMAT( NOW(), '%H:%i' ) as now_time
		FROM	Timer T1
		WHERE	0 <= CAST( DATE_FORMAT( T1.time, '%H%i' ) as SIGNED )
        ORDER BY T1.time
		LIMIT 1
	)
) V0
ORDER BY	V0.time_sort
	,	V0.time
LIMIT 1

Comments

[souri-t]

回答ありがとうございます。
確かに0:00を24:00に置き換えた方が扱いやすかったですね。
これでも解決できそうな気がします。思いつかなかったです。

ちなみに記述頂いたSQL文ですが、
「2330」の箇所を変えていろいろ実行しましたが、
抽出できたレコードが常に「0:00」のものになりました。

コメント行にされているnow()の部分もマシンが「0:32」の時刻に実行してみましたが、
同じ結果でした。

[tomari_perform]

回答の中で修正しておきました。

[ahodana]

二つ目のサブクエリ
WHERE句不要では？

全部ヒットする気が

[tomari_perform]

正直、どちらでも良いです。

仕様次第なんですが、24:00以降の記載をお勧めしたので、
-01:00等でも大丈夫なように記載しただけです。

time型で登録できる範囲をご確認ください。
https://dev.mysql.com/doc/refman/5.6/ja/time.html

Answer 4

date系関数で足し引きしてみました

SELECT *,
  CASE WHEN `time` >= TIME_FORMAT(now(),'%H:%i')
     THEN SUBTIME(`time`,TIME_FORMAT(now(),'%H:%i'))
     ELSE SUBTIME( ADDTIME(`time`,'24:00'),TIME_FORMAT(now(),'%H:%i'))
  END timegap
FROM `Timer`
ORDER BY 3 LIMIT 1;

Answer 5

SQLでいいなら

select * from Timer where 現在時刻<=time order by time limit 1

Comments

[takasima20]

timeには時分以外も入ってる前提ですが、もしや…

[ahodana]

日付跨ぎ
22:00過ぎは翌日の最も早い時間が抜けているからマイナスなのかと