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

解決済

回答 5

投稿

  • 評価
  • クリップ 0
  • VIEW 1,521

souri-t

score 11

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時を跨ぐ場合の考慮が難しく解決できませんでした。
お分かりの方がいましたら教えて頂けませんか?

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 5

checkベストアンサー

+1

`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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/26 00:43 編集

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

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

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

    キャンセル

  • 2017/07/26 00:46

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

    キャンセル

  • 2017/07/26 08:44 編集

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

    全部ヒットする気が

    キャンセル

  • 2017/07/26 15:51

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

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

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

    キャンセル

+1

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/26 22:40

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

    キャンセル

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/26 22:42

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

    キャンセル

0

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;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

-2

SQLでいいなら

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/25 22:54

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

    キャンセル

  • 2017/07/26 08:37

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.21%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる