こんにちは。
田中です。
あるプロジェクトでOracleDBでSQL上で時間計算をしていたときだ。
内容としては、時間の差分を出して、それを分単位で四捨五入して値を出すというもの。
「あれ？これって最大１分の誤差が出ない？」
「え？」
私はその指摘を受けて確認すると場合によっては、誤差が出てしまうことに気づいた。
今回は、その誤差が発生してしまったのか、備忘録をかけて解説する。
OracleのDATE型は、以下の情報を内部的に保持する。
一見すると十分そうですが、小数秒（ミリ秒以下）を保持できない。
そのため、アプリケーション側や他のDBから受け取った高精度な時刻をDATE型に格納した時点で、秒未満は切り捨て（または丸め）られてしまう。
そのために、誤差が生じる原因となっている。
Oracleでは、DATE型同士を引き算すると「日数（小数）」が返却される。
これを分や秒に換算するケースはよくあるケースだ。
入会申込
ログインサンプル①：DATE型同士の差分計算
SELECT
(end_time - start_time) * 24 * 60 AS diff_minutes
FROM (
SELECT
TO_DATE('2024-01-01 10:00:30', 'YYYY-MM-DD HH24:MI:SS') AS start_time,
TO_DATE('2024-01-01 10:01:29', 'YYYY-MM-DD HH24:MI:SS') AS end_time
FROM dual
);
この例では、実際の差は 59秒 ですが、計算結果は 0.9833… 分 になる。
これを ROUND や TRUNC すると、0分や1分になる可能性があり、最大で1分の誤差として現れてしまう。
最も確実な対策は、DATE型ではなくTIMESTAMP型を使う。
TIMESTAMP型は小数秒まで保持できるため、精度の高い時間計算が可能。
SELECT EXTRACT(SECOND FROM (end_ts - start_ts)) AS diff_seconds FROM ( SELECT TIMESTAMP '2024-01-01 10:00:30.500' AS start_ts, TIMESTAMP '2024-01-01 10:01:29.900' AS end_ts FROM dual );
これにより、秒単位の誤差を最小限に抑えられる。
DATE型を使わざるを得ない場合は、ROUND / TRUNC / CEIL の使い分けを明確にすることが重要。
TRUNC((end_time - start_time) * 24 * 60)
「切り捨てなのか」「切り上げなのか」を仕様として明文化しておくことで、想定外のズレを防げる。
Oracleの時間計算では、DATE型の仕様によって最大1分程度の誤差が発生する可能性があることを知った。
特に分単位での判定や集計を行う場合は、以下を意識するようにする。
日常であれば、まぁ1分ぐらいいっかと思うことはあるが、これがシステムになると「いやいや！ヤバいヤバい！」と大騒ぎになる事案である。
私のときは、開発段階で気づけたからよかったが、バグ報告上がったら沼りそうな事案だったなと思う。
私自身も注意して扱っていこうと思う。
