SQL 時間差分の計算について

Question

実現したいこと

SSMSのテーブルに保存されている勤務時間のデータを取り出し、ビジュアルスタジオを使って労働時間を計算するアプリを作成したい。

前提

SSMS内にあるテーブルのデータはすべてtime型で列名以下の通り
start_time-始業時間
end time-終業時間
break_time-休憩時間

ビジュアルスタジオの方に抽出する際「終業時間－始業時間一休憩時間」で計算し
表示、列名を「労働時間」としたいです

該当のソースコード

sql. Appendline (“Select CONVERT(CHAR (5), DATEADD(SECOND,
DATEDIFF (SECOND, start_time, end time), CONVERT (DATETIME, 0)), 108)AS ’労働時間' ")

試したこと

かなりごり押しであることは重々承知しております
上記のコードで「始業時間」と「終業時間」の差分は出ますがDATEDIFF関数で指定できる値は2つまで。ここにさらに休憩時間分の時間を減算したいのです。
もっとスマートにかければ良いのですが、アドバイスをいただければ幸いです

補足情報（FW/ツールのバージョンなど）

アプリの全容としてはComboBoxを使用して氏名を選択し、その選択された人の労働時間がDataGridViewに表示される予定です
参加サイト

Comments

[sk.exe]

> start_time-始業時間
> end time-終業時間

[start_time]が始業「時刻」（≠時間）、[end time]が終業「時刻」（≠時間）、

> break_time-休憩時間

[break_time]が休憩「時間」（≠時刻）、ということでしょうか。

> 上記のコードで「始業時間」と「終業時間」の差分は出ますが
> DATEDIFF関数で指定できる値は2つまで。ここにさらに
> 休憩時間分の時間を減算したいのです。

最終的にどのような時間単位での数値を求めようとされているのでしょうか。
例えば「分単位での時間」を求めるのであれば

・2つの時刻の差（＝時間）を分単位の数値として得る。

・休憩時間を分単位の数値に換算する。

・（両者の時間単位を合わせた上で）前者と後者の差を求める。

という流れの計算を行えばよいのではないでしょうか。

[Flotsam]

私の書き方が不親切でした。
それらはあくまで「xx time」の方がテーブルの列名で「〇〇時間」はそのテーブルの役名として書いたつもりでした。
sql. AppendLine("Select FORMAT(start_time,"hh:mm')As"始業時間
FORMAT (end_time, 'hh:mm') As“終業時間，FORMAT(break_time,”hh:mm”As“休憩時間‥‥
こちらのソースコードに先の一文を入れる予定です。

>最終的にどのような時間単位での数値を求めようとされているのでしょうか
例えば始業時間09：00、終業時間18：00、休憩時間01：00の場合
労働時間を「08:00」と表示したいと考えております。

[Flotsam]

すいません、ダブルコーテーション、シングルコーテーションがチグハグなのは誤字です。

[sk.exe]

> それらはあくまで「xx time」の方がテーブルの列名で
> 「〇〇時間」はそのテーブルの役名として書いたつもりでした。

列の命名の話ではなく、「時刻」と「時間」の意味の違いを踏まえた上で、それぞれの列に格納される値がそのどちらに該当するのかについて確認しています。
本来 time 型は「時間」ではなく「時刻」を格納するためのデータ型ですので。

> 例えば始業時間09：00、終業時間18：00、休憩時間01：00の場合
> 労働時間を「08:00」と表示したいと考えております。

基本的には、既に申し上げたような計算をなさればよいと思います。
その上で 480 （分）といった数値を datetime 型や time 型のデータに変換なさればよいでしょう。
例えば DATEADD 関数によって 00:00:00 から n 分後の日時を求めるなど。

[Flotsam]

・2つの時刻の差（＝時間）を分単位の数値として得る。
・休憩時間を分単位の数値に換算する。
・（両者の時間単位を合わせた上で）前者と後者の差を求める。

式の形としては始業-就業を行っていたDATEDIFF関数内に（（始業－終業）-休憩）となるようDATEDIFF関数を重ねるということでしょうか

Answer 1

テーブルのデータはすべてtime型

・2つの時刻の差（＝時間）を分単位の数値として得る。
・休憩時間を分単位の数値に換算する。
・（両者の時間単位を合わせた上で）前者と後者の差を求める。

その上で 480 （分）といった数値を datetime 型や time 型のデータに変換

労働時間を「08:00」と表示

例えば次のようなクエリと同様の計算を行えばよいと思います。

SELECT t1.[start_time] AS [始業時刻], 
       t1.[end_time] AS [終業時刻],
       DATEDIFF(minute, t1.[start_time], t1.[end_time]) AS [始業時刻と終業時刻の差_分単位],
       DATEDIFF(minute, '00:00:00', t1.[break_time]) AS [休憩時間_分単位], 
       DATEDIFF(minute, t1.[start_time], t1.[end_time]) - DATEDIFF(minute, '00:00:00', t1.[break_time]) AS [労働時間_分単位],
       FORMAT(DATEADD(minute, DATEDIFF(minute, t1.[start_time], t1.[end_time]) - DATEDIFF(minute, '00:00:00', t1.[break_time]), '00:00:00'),'hh:mm') AS [労働時間_hhmm表記]
FROM [dbo].[テーブル名] AS t1;

Comments

[sk.exe]

なお、今回のようなケースにおいて1つ注意すべき点としては「終業時刻の扱い」が挙げられます。
time 型の値の範囲は 0:00:00 から 23:59:59 まで（秒未満の精度は定義による）であり、24時以降の時刻を格納することは出来ません。

したがって、もし終業時刻が翌日の 0 時をまたいだ時刻である場合、データ上では終業時刻が始業時刻よりも早い時刻となってしまう可能性が生じ、何の補正処理も加えなければ当然 DATEDIFF 関数による計算結果が狂ってしまうことになります。

[Flotsam]

ご回答ありがとうございます。
仮に終業時間の時刻が始業時間よりも早くなってしまった場合、If文などでエラーが出るようにしたりするような処理を施すべきでしょうか

[sk.exe]

そのアプリの仕様や導入先企業の就業規則において、0 時以降の深夜/早朝勤務が禁止されているか否かによるでしょう。
明確に禁止されているのであれば、始業時刻よりも早い時刻が終業時刻に入力された際にエラーを返す、もしくは始業時刻よりも早い時刻を終業時刻に入力できないように制限するのが妥当です。

終業時刻が翌日 0 時以降にまたがることを許容し、かつ「1つの勤務日において24時間以上の勤務を禁止する（終業時刻と始業時刻の差は必ず24時間未満となる）」というルールを設けるのであれば、終業時刻が始業時刻よりも早い時刻だった場合は「終業時刻に 1 日分の時間（1日/24時間/1440分/86400秒）を加えた結果（つまり勤務日の翌日の時刻として扱う）」と始業時刻との差を求めるようにする、という方式が挙げられます。

万が一に備えて24時間以上の勤務をも許容するのであれば、時刻レベルではなく日時レベルで記録するようにしなければなりません。