LoginSignup
@Sunny-2022

Are you sure you want to delete the question?

Leaving a resolved question undeleted may help others!

タイムスタンプに日数を加算して、現在の日時と比較し残り時間の少ない順で抽出、一行のSQL構文でできるものなのか？

Q&A

Closed

PHPSQL

目的

いつもQiitaで問題可決を図らせて頂いております。
皆様ありがとうございます。
初投稿で初学者です、よろしくお願いいたします。

SQLでDBから取り出す際の構文でご相談させてください。

ルーティン作業を管理する簡単なWeb機能をHPに実装しています
作業をポットイットのようにラベル化して作業を終了したらチエックボックスをクリックして色が変わるものになります。

チエックボックスをクリックした日時をDBにタイムスタンプで保存して
時間経過し、登録したサイクルタイムで今の時間との比較によりラベルの色が
変わるというような仕組みのものを作っております。

例

通常は青、1日前に黄色、当日又はサイクル日数が経過したら赤にラベルのバックグラウンドの色が変わる

　blue.php.png
　yellow.php.png
　red.php.png

MySQL　DBname：cycle_ope
id | 　　 title 　　 | 　　　 up_date_time 　 　| cycle_day | position |

1 | トイレ掃除　　｜　2022-2-20 10：00：00 | 2 | cleaning |
2 | 流し周り掃除　｜　2022-2-21 11：00：00 | 4 | cleaning |
3 | サイト更新　　｜　2022-2-22 09：30：00 | 2 | work |
4 | 事務所内清掃　｜　2022-2-21 09：00：00 | 2 | cleaning |

解決したいこと

この様なDBからカラム名positionの'cleaning'のみを抽出し、タイムスタンプのカラム名：up_date_timeにカラム名：cycle_dayの値を日数として加算して、現在の日時から残り時間の短い順で取り出したいのですがうまくいきません

※画像のタイトルとDBのタイトルの値は同じものを使いますが説明の為タイトル名を省略しています。

現在まで構築できたSQL構文になります。

SELECT * FROM cycle_ope WHERE up_date_time ORDER BY DATE_ADD(up_date_time, INTERVAL cycle_day DAY) IN ( SELECT position = 'cleaning' FROM cycle_ope )

カラム名：cycle_dayにある値をカラム名：up_date_timeに加算するところで止まってしまっています。
現在の日時との残り日数の比較に関する構文はまだたどり着いていません。（ASCの予想）

現在の状況

上記の構文でエラーは出ずにDBからデータを取り出せてはいるのですが
idの順番通りの昇順で出力されてしまいます。

一行のSQL構文でかけるものなのか？

SQL　日時加算　などで検索し方法を探すも
DATE_ADDの加算部分にカラムの値を適用できるのか？
そもそもDATE_ADD関数を利用するべきなのか？
タイムスタンプにサイクルの日数を加算し、その先に、現在の時間と比較して残り時間の少ないものから抽出できるのか？
など根本的にできることなのかがわからず困っております
おわかりになる方がいらっしゃいましたら何卒よろしくお願いいたします。

0 likes

2Answer

baku2san
@baku2san

mysql は知らないですが、ドキュメント見ると DATEDIFF があるのでこれ使えば出来そうですよ。

0Like

Comments

  1. @Sunny-2022

    Questioner

    ご返信が遅くなり申し訳ありません。
    教えていただきありがとうございます。
    DATEDIFF関数を注視するきっかけをいただけました。
    AとBの時間差を算出するという機能を考えていたら、一つの解決策を思いつきました。

    DBのup_date_timeカラムAと、同じくカラムBを作成し、カラムBをカラム名cycle_dayを加算してからUPDATEすることで、SQL出力時にはDATEDIFFでAとBの時間差をソート出力することができますね
    本件の解決方法の一つとして糸口を見出すことができました。
    ご教授いただきましてありがとうございました。
uasi
@uasi

IN ( SELECT position = 'cleaning' FROM cycle_ope ) で絞り込みはできません。また、なぜかは分かりませんが、これのせいで ORDER BY が効かなくなり id 昇順で出力されてしまっているようです。絞り込みの条件は WHERE に AND でつなげて書いてください。

SELECT * FROM cycle_ope
WHERE up_date_time
AND position = 'cleaning'
ORDER BY DATE_ADD(up_date_time, INTERVAL cycle_day DAY);

日付関数についてはその使い方で合っています。結果は予定日が早い順に並ぶので、現在の日付との差分を取らなくても大丈夫です。

差分を取りたいなら

DATEDIFF(DATE_ADD(up_date_time, INTERVAL cycle_day DAY),  NOW())

としてください。ここで DATEDIFF() は 第1引数 - 第2引数 の日数を整数値で返します。
第2引数の方が大きければ結果は負の値になります。

追記：昇順で出力されていた理由が分かりました。

元の SQL 文 ORDER BY DATE_ADD(...) IN (~~) は、 cycle_ope の各行につき、 DATE_ADD(...) した結果が集合 ~~ に含まれるか否かを表す真偽値（真なら1、偽なら0）が小さい順にソートするという意味です。

集合は SELECT position = 'cleaning' FROM cycle_ope で作られます。これは cycle_ope の各行について position = 'cleaning' が返す真偽値を列挙しますから、ようするに1と0からなる集合になります。

結局、 DATE_ADD(...) した結果が集合 (1, 0) に含まれることはあり得ませんから、 ORDER BY 0 しているのと同じであり、すべての行を同順位としてソートしていることになります。同順位の行の並びは不定で、 MySQL が都合のいいように返します。それがたまたま id 昇順になっただけです。

0Like

Comments

  1. @Sunny-2022

    Questioner

    ご返信が遅くなり申し訳ありません。
    教えていただきありがとうございます。
    思惑通りの動きをして頂けました。
    DATE_ADDでの出力と、さらにDATEDIFFをかぶせる出力と
    ともに結果は同じ出力でした。
    しかしプログラミング思考的にはNowを基準点として走らせるDATEDIFFの方を使うべきなんだと感じております。
    コードを見た時軽く鳥肌がでかけました。。
    おかげさまで無事問題解決を図ることができました。
    この度はご教授いただきましてありがとうございました。

Your answer might help someone💌

LoginSign Up