MENU

SQL Serverで開始時刻と終了時刻から経過時間を正確に算出・保存する方法（24:00/日付越え対応・2008 R2対応）

Microsoft

開始時刻と終了時刻が ‘hh:mm’ の文字列で与えられるレガシー設計は、SQL Server 2008 R2 の現場でもいまだに見かけます。この記事では、日付をまたぐケース（23:45→00:05）や開始＝終了を 24:00 とみなす特殊要件まで正しく扱い、更新系でも参照系でも破綻しないT‑SQLの実装と、データ品質・パフォーマンスまで含めた実践的な設計指針をまとめます。

目次

前提と要件の整理（SQL Server 2008 R2）

対象テーブルは次のようなシンプルな構成です。

CREATE TABLE dbo.Test (
    Test_ID       int         NOT NULL PRIMARY KEY,
    Start_Time    char(5),    -- 'hh:mm'（例: '09:05'）
    End_Time      char(5),    -- 同上
    Elapsed_Time  char(5)     -- 計算後に 'hh:mm' を保存
);

求められる要件を要約します。

要件説明例
入力形式24時間表記のゼロ埋め 'hh:mm' 文字列（00〜23時）'07:05', '23:59'
24時間越えなし経過時間は最大 24:00（1440分）まで—
日付越え対応開始 > 終了 の場合は翌日にまたぐと解釈23:45 → 00:05 = 00:20
開始＝終了経過 24:00 とみなす（0分ではない）12:30 → 12:30 = 24:00

結論：最小のSQLで正しく計算する（更新系）

もっともシンプルで安全な実装は「分差を求め、日付越えなら 1440 分を加算し、0分なら '24:00' とする」流れです。
CTEで差分を一度だけ計算し、Elapsed_Time を更新します。

;WITH cte AS (
    SELECT
        Test_ID,
        DATEDIFF(MINUTE,
                 CAST(Start_Time AS TIME),
                 CAST(End_Time   AS TIME))
        + CASE WHEN Start_Time > End_Time THEN 1440 ELSE 0 END AS diff
    FROM dbo.Test
)
UPDATE cte
   SET Elapsed_Time =
       CASE WHEN diff = 0 THEN '24:00'
            ELSE CONVERT(char(5), DATEADD(MINUTE, diff, '19000101'), 108)
       END;
  • 文字列比較で日付越え判定：'hh:mm' はゼロ埋めなので、文字列の > 判定で時刻の先後と一致します。
  • 24:00 の特別扱い：diff = 0 のときだけ '24:00' を返却。そうしないと '00:00' になってしまいます。
  • フォーマット：CONVERT(char(5), DATEADD(MINUTE, diff, '19000101'), 108) は hh:mm:ss を返すため、char(5) で hh:mm を切り出しています。

参照だけで済ませたい（SELECTで即時計算）

テーブルの値を更新せず、都度表示したい場合の定番パターンです。
一度だけ分差を算出し、時:分に整形します。

SELECT
    x.Test_ID,
    x.Start_Time,
    x.End_Time,
    CASE WHEN x.diff = 0 THEN '24:00'
         ELSE RIGHT('0' + CAST((x.diff/60) AS varchar(2)), 2)
              + ':' +
              RIGHT('0' + CAST((x.diff%60) AS varchar(2)), 2)
    END AS Elapsed_Time
FROM (
    SELECT
        Test_ID,
        Start_Time,
        End_Time,
        DATEDIFF(MINUTE, CAST(Start_Time AS TIME), CAST(End_Time AS TIME))
        + CASE WHEN Start_Time > End_Time THEN 1440 ELSE 0 END AS diff
    FROM dbo.Test
) AS x;

注意：diff = 1440 のケースは diff = 0 と同値の扱いにしたいので、冒頭の CASE WHEN x.diff = 0 で先に 24:00 を返しています（このパターンでは diff が 1440 になることはありませんが、要件が拡張された際にも安全です）。

サンプルデータで挙動を検証する

TRUNCATE TABLE dbo.Test;
INSERT dbo.Test (Test_ID, Start_Time, End_Time) VALUES
(1, '09:00', '09:30'),  -- 00:30
(2, '23:45', '00:05'),  -- 00:20  日付越え
(3, '00:00', '00:00'),  -- 24:00  特別扱い
(4, '12:34', '12:33'),  -- 23:59  日付越え
(5, '07:05', '07:05'),  -- 24:00
(6, '18:00', '17:00'),  -- 23:00  日付越え
(7, '10:59', '11:00'),  -- 00:01
(8, '11:00', '10:59'),  -- 23:59  日付越え
(9, '05:00', '23:00'),  -- 18:00
(10,'23:59', '00:00'),  -- 00:01  日付越え
(11,'00:01', '23:59'),  -- 23:58
(12,'13:00', '13:59');  -- 00:59

前掲の更新クエリを実行した後、次のような結果になります。

Test_IDStart_TimeEnd_TimeElapsed_Time（期待値）
109:0009:3000:30
223:4500:0500:20
300:0000:0024:00
412:3412:3323:59
507:0507:0524:00
618:0017:0023:00
710:5911:0000:01
811:0010:5923:59
905:0023:0018:00
1023:5900:0000:01
1100:0123:5923:58
1213:0013:5900:59

「文字列 > 文字列」で本当に大丈夫？―判定の根拠

  • 'hh:mm' は常に2桁ゼロ埋めのため、辞書順＝時刻順になります（例：'09:30' < '10:00'）。
  • したがって Start_Time > End_Time は「開始が終了より後（＝翌日またぎ）」の判定として正しく機能します。
  • 可変長（例：'9:5'）だと壊れるので、必ず固定長で格納するか、チェック制約で弾きます。

エッジケースの取り扱い

  • 開始＝終了：仕様上、経過 24:00。分差は見かけ上 0 になるため CASE で特別扱い。
  • 23:59 → 23:59：同様に 24:00。
  • NULL の扱い：Start_Time または End_Time が NULL の行は更新対象から除外するのが無難です（下記参照）。
;WITH cte AS (
    SELECT
        Test_ID,
        DATEDIFF(MINUTE, CAST(Start_Time AS TIME), CAST(End_Time AS TIME))
        + CASE WHEN Start_Time &gt; End_Time THEN 1440 ELSE 0 END AS diff
    FROM dbo.Test
    WHERE Start_Time IS NOT NULL AND End_Time IS NOT NULL
)
UPDATE cte
   SET Elapsed_Time =
       CASE WHEN diff = 0 THEN '24:00'
            ELSE CONVERT(char(5), DATEADD(MINUTE, diff, '19000101'), 108)
       END;

データ品質を守るチェック制約（2008 R2対応）

2008 R2 には TRY_CONVERT がないため、LIKE でパターンを縛った上で数値範囲を検査するのが定石です。

ALTER TABLE dbo.Test ADD CONSTRAINT CK_Test_Start_Format
CHECK (
    Start_Time IS NULL
    OR (
        Start_Time LIKE '[0-2][0-9]:[0-5][0-9]' AND
        CAST(LEFT(Start_Time,2) AS int) BETWEEN 0 AND 23 AND
        CAST(RIGHT(Start_Time,2) AS int) BETWEEN 0 AND 59
    )
);

ALTER TABLE dbo.Test ADD CONSTRAINT CK_Test_End_Format
CHECK (
End_Time IS NULL
OR (
End_Time LIKE '[0-2][0-9]:[0-5][0-9]' AND
CAST(LEFT(End_Time,2) AS int) BETWEEN 0 AND 23 AND
CAST(RIGHT(End_Time,2) AS int) BETWEEN 0 AND 59
)
);

これで '25:00' や '12:60' のような異常値を防げます。
（入力で 24:00 を許可しない点が重要。24:00 は結果としてのみ現れます。）

更新負荷を抑える：必要行だけ書き換える

毎回全件UPDATEすると無用な書き込みが増えます。差分更新にしましょう。

;WITH cte AS (
    SELECT
        t.Test_ID,
        CASE WHEN t.Start_Time IS NULL OR t.End_Time IS NULL THEN NULL
             ELSE DATEDIFF(MINUTE, CAST(t.Start_Time AS TIME), CAST(t.End_Time AS TIME))
                  + CASE WHEN t.Start_Time &gt; t.End_Time THEN 1440 ELSE 0 END
        END AS diff,
        t.Elapsed_Time AS cur
    FROM dbo.Test AS t
)
UPDATE cte
   SET cur =
       CASE WHEN diff IS NULL THEN NULL
            WHEN diff = 0     THEN '24:00'
            ELSE CONVERT(char(5), DATEADD(MINUTE, diff, '19000101'), 108)
       END
 WHERE
       cur IS NULL
    OR CASE WHEN diff IS NULL THEN NULL
            WHEN diff = 0     THEN '24:00'
            ELSE CONVERT(char(5), DATEADD(MINUTE, diff, '19000101'), 108)
       END &lt;&gt; cur;

計算結果が変わらない行は書き換えないため、ロック競合とログ量を抑制できます。

列設計の見直し：数値で持ち、文字列は見せ方に限定

集計や検索のことを考えると、経過分数を整数で保持し、表示にだけ 'hh:mm' を生成する方が扱いやすいです。

ALTER TABLE dbo.Test ADD Elapsed_Minutes int NULL;

;WITH cte AS (
SELECT
Test_ID,
DATEDIFF(MINUTE, CAST(Start_Time AS TIME), CAST(End_Time AS TIME))
+ CASE WHEN Start_Time > End_Time THEN 1440 ELSE 0 END AS diff
FROM dbo.Test
WHERE Start_Time IS NOT NULL AND End_Time IS NOT NULL
)
UPDATE cte
SET Elapsed_Minutes = CASE WHEN diff = 0 THEN 1440 ELSE diff END;

-- 表示時：数値→'hh:mm'
SELECT
Test_ID,
CASE WHEN Elapsed_Minutes = 1440 THEN '24:00'
ELSE RIGHT('0'+CAST((Elapsed_Minutes/60) AS varchar(2)),2)
+ ':' +
RIGHT('0'+CAST((Elapsed_Minutes%60) AS varchar(2)),2)
END AS Elapsed_Time
FROM dbo.Test;

このアプローチなら合計・平均・中央値などの分析が直感的です。

計算列（PERSISTED）で常時一貫性を保つ

アプリやプロシージャが更新を忘れても、計算列なら自動で一貫性を保てます。下記は 2008 R2 でも使える構成です。

-- 1) 入力はそのまま（char(5)）
-- 2) 分差（24:00=1440）を計算列で持つ
ALTER TABLE dbo.Test ADD
    Elapsed_Minutes_Calc AS
        CASE
            WHEN Start_Time IS NULL OR End_Time IS NULL THEN NULL
            WHEN Start_Time = End_Time THEN 1440
            ELSE DATEDIFF(MINUTE, CAST(Start_Time AS TIME), CAST(End_Time AS TIME))
                 + CASE WHEN Start_Time > End_Time THEN 1440 ELSE 0 END
        END PERSISTED;

-- 3) 表示用 'hh:mm' の計算列（24:00 対応）
ALTER TABLE dbo.Test ADD
Elapsed_Time_Calc AS
CASE
WHEN Elapsed_Minutes_Calc IS NULL THEN NULL
WHEN Elapsed_Minutes_Calc = 1440 THEN '24:00'
ELSE RIGHT('0'+CAST((Elapsed_Minutes_Calc/60) AS varchar(2)),2)
+ ':' +
RIGHT('0'+CAST((Elapsed_Minutes_Calc%60) AS varchar(2)),2)
END PERSISTED;

PERSISTED により実値が保持され、インデックス付与も可能になります（範囲検索が高速）。

インデックスとパフォーマンスの考え方

  • 参照中心：計算列 Elapsed_Minutes_Calc に非クラスタ化インデックスを付けると、WHERE Elapsed_Minutes_Calc BETWEEN 300 AND 600 のような条件が効きます。
  • 更新中心：全件再計算を避け、差分更新に徹する（前述のUPDATE条件）。
  • 文字列のLIKE検索回避：'hh:mm' の文字列比較での範囲検索は避け、数値の分で絞り込むとプランが安定します。

入力を本来の型へ：time(0) への移行手順

将来的にカラム型を正したい場合の安全な移行ステップです（ダウンタイム最小）。

  1. 追加：Start_Time2 time(0) NULL、End_Time2 time(0) NULL を追加。
  2. 並行書き込み：アプリ側で新旧両カラムに同値を書き込み。
  3. 同期：既存レコードを移送（秒は 0 固定）。
ALTER TABLE dbo.Test ADD Start_Time2 time(0) NULL, End_Time2 time(0) NULL;

UPDATE dbo.Test
SET Start_Time2 = CAST(Start_Time AS time(0)),
End_Time2   = CAST(End_Time   AS time(0))
WHERE Start_Time IS NOT NULL AND End_Time IS NOT NULL;

以降は計算を純粋な時間型で実施できます。

SELECT
    Test_ID,
    CASE
        WHEN Start_Time2 = End_Time2 THEN '24:00'
        ELSE CONVERT(char(5),
                     DATEADD(MINUTE,
                         DATEDIFF(MINUTE, Start_Time2, End_Time2)
                         + CASE WHEN Start_Time2 &gt; End_Time2 THEN 1440 ELSE 0 END,
                         '19000101'), 108)
    END AS Elapsed_Time
FROM dbo.Test;

TIP： 型移行後はチェック制約がシンプルになります（time(0) 自体がバリデーション）。

秒精度・ミリ秒・24時間超…要件拡張への対応指針

  • 秒精度が必要：Elapsed_Seconds int を追加し、DATEDIFF(SECOND,...) を使って同様のロジックで算出。表示は hh:mm:ss を生成。
  • 24時間超を許容：Start_Time/End_Time では表現しきれないため、別途「開始日時/終了日時」を datetime 等で持つ設計へ変更（経過は数値保持）。
  • 丸め規則：5分単位切上げ／切捨て等は diff = ((diff+4)/5)*5 のような整数演算で一貫させます。

MERGEやトリガーは使うべき？

  • MERGE：2008 R2 世代では既知の挙動差で予期しない更新が生じる事例があるため、単純な UPDATE を推奨します。
  • トリガー：自動更新は便利ですが、バルク更新やメンテナンス時の意図しない発火、デバッグ困難などの副作用があります。まずは計算列（PERSISTED）を検討し、どうしても必要な場合のみ採用しましょう。

トランザクションと同時実行制御

  • バッチ更新：大量更新時は TOP (1000) などで分割し、トランザクションを小さく保ってログ膨張とロック保持時間を抑制。
  • 参照一貫性：READ COMMITTED SNAPSHOT が無効な環境では、長大な更新に伴うブロッキングを避けるためにも差分更新が有効です。

ユニットテスト風の自己診断クエリ

要件を満たしているかを簡単に検証するクエリ例です。

-- 24:00 判定
SELECT COUNT(*) AS NG_2400
FROM dbo.Test
WHERE Start_Time = End_Time AND (Elapsed_Time IS NULL OR Elapsed_Time <> '24:00');

-- 日付越え判定
SELECT COUNT(*) AS NG_CrossDay
FROM dbo.Test
WHERE Start_Time > End_Time
AND (Elapsed_Time IS NULL OR Elapsed_Time < '00:01' OR Elapsed_Time > '23:59');

-- 形式チェック（LIKEと数値範囲の両建て）
SELECT COUNT(*) AS NG_Format
FROM dbo.Test
WHERE (Start_Time IS NOT NULL AND
(Start_Time NOT LIKE '[0-2][0-9]:[0-5][0-9]'
OR CAST(LEFT(Start_Time,2) AS int) NOT BETWEEN 0 AND 23
OR CAST(RIGHT(Start_Time,2) AS int) NOT BETWEEN 0 AND 59))
OR (End_Time IS NOT NULL AND
(End_Time NOT LIKE '[0-2][0-9]:[0-5][0-9]'
OR CAST(LEFT(End_Time,2) AS int) NOT BETWEEN 0 AND 23
OR CAST(RIGHT(End_Time,2) AS int) NOT BETWEEN 0 AND 59));

よくある質問（FAQ）

  • Q. DATEDIFF の境界で1分ズレることは？
    A. 今回は time への CAST で秒未満は持っていません。入力が秒を含まない限りズレは発生しません。
  • Q. 文字列比較は照合順序に影響される？
    A. 数字とコロンのみの固定長であれば、一般的な照合順序で辞書順＝時刻順になります。可変長を許すと崩れます。
  • Q. FORMAT() が使えないのはなぜ？
    A. FORMAT() は 2012 以降です。2008 R2 では CONVERT(...,108) を使うのが現実解です。

まとめ：最小実装で正確・安全・拡張可能に

本記事の要点は次の3つです。

  1. 正しい計算：diff = DATEDIFF(MINUTE, Start, End) + CASE WHEN Start > End THEN 1440 END、diff = 0 を '24:00' として扱う。
  2. 品質担保：形式チェックの制約で 'hh:mm' を保証。可能なら time(0) への型移行を検討。
  3. 将来拡張：数値（分/秒）で保持し、表示は生成。計算列（PERSISTED）やインデックス活用で参照・集計を高速化。

この方針に従えば、日付越えや 24:00 特例を含む難所をカバーしつつ、保守性と性能を両立できます。2008 R2 という制約の中でも、堅牢で読みやすいT‑SQLを組み立てることは十分可能です。

付録：一連の手順を実行するスクリプト（再掲）

-- サンプルの再現手順
SET NOCOUNT ON;

IF OBJECT_ID('dbo.Test','U') IS NOT NULL DROP TABLE dbo.Test;
CREATE TABLE dbo.Test (
Test_ID       int         NOT NULL PRIMARY KEY,
Start_Time    char(5)     NULL,
End_Time      char(5)     NULL,
Elapsed_Time  char(5)     NULL
);

ALTER TABLE dbo.Test ADD CONSTRAINT CK_Test_Start_Format
CHECK (Start_Time IS NULL OR (
Start_Time LIKE '[0-2][0-9]:[0-5][0-9]' AND
CAST(LEFT(Start_Time,2) AS int) BETWEEN 0 AND 23 AND
CAST(RIGHT(Start_Time,2) AS int) BETWEEN 0 AND 59
));
ALTER TABLE dbo.Test ADD CONSTRAINT CK_Test_End_Format
CHECK (End_Time IS NULL OR (
End_Time LIKE '[0-2][0-9]:[0-5][0-9]' AND
CAST(LEFT(End_Time,2) AS int) BETWEEN 0 AND 23 AND
CAST(RIGHT(End_Time,2) AS int) BETWEEN 0 AND 59
));

INSERT dbo.Test (Test_ID, Start_Time, End_Time) VALUES
(1, '09:00', '09:30'),
(2, '23:45', '00:05'),
(3, '00:00', '00:00'),
(4, '12:34', '12:33'),
(5, '07:05', '07:05'),
(6, '18:00', '17:00'),
(7, '10:59', '11:00'),
(8, '11:00', '10:59'),
(9, '05:00', '23:00'),
(10,'23:59', '00:00'),
(11,'00:01', '23:59'),
(12,'13:00', '13:59');

;WITH cte AS (
SELECT
Test_ID,
DATEDIFF(MINUTE, CAST(Start_Time AS TIME), CAST(End_Time AS TIME))
+ CASE WHEN Start_Time > End_Time THEN 1440 ELSE 0 END AS diff
FROM dbo.Test
WHERE Start_Time IS NOT NULL AND End_Time IS NOT NULL
)
UPDATE cte
SET Elapsed_Time =
CASE WHEN diff = 0 THEN '24:00'
ELSE CONVERT(char(5), DATEADD(MINUTE, diff, '19000101'), 108)
END;

SELECT * FROM dbo.Test ORDER BY Test_ID;
Microsoft
SQL Server データ型 時間計算 T-SQL

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次