datetimeの精度によるデータ差分が発生したときの対応

背景

• 既存の複数テーブルから必要な情報をまとめて最適化したテーブルを作りたい
• SQLServerのストアドプロシージャで定期的に差分のあったレコードのみをINSERT,UPDATE,DELETEするようにしたい

発生した課題

ストアドプロシージャが実行されるたびに大量更新が入ってしまう事象が発生した

原因

文字列等の比較での差分発生ではなく、datetimeの比較で差分を検知して更新処理が走っているということがわかった。

該当カラムの値

既存テーブル(existing_table)のmodified_at（datetime）：2024/01/30 12:00:00.555
新テーブル(new_table)のmodified_at（datetime2(6)）：2024/01/30 12:00:00.555000

パッと見では同じと判定されてもおかしくないように見える

該当部分のクエリ

OR (COALESCE(new_table.modified_at, DefaultDatetime) <> COALESCE(existing_table
.modified_at, DefaultDatetime))

定義が異なる背景

既存テーブルでは日時カラムにdatetime, datetime2(6)が混在していた。
同じ日時カラムなのに型や小数秒が違う理由は意思統一をはかる前に作られたかどうかで、
意思統一後はdatetime2(6)に統一されていた。

判明した課題

datetimeとdatetime2(6)を比較するときに
datetime: 2024/01/30 12:00:00.555 と datetime2(6) 2024/01/30 12:00:00.555000 を比較すると実際には次の比較となっている。

ぱっと見では一致しているように見えているが実際の比較では一致していないと判断されているということがわかった

datetimeをdatetime2(6)に代入すると次のようになる
2024/01/30 12:00:00.550 → 2024/01/30 12:00:00.550000
2024/01/30 12:00:00.553 → 2024/01/30 12:00:00.553333
2024/01/30 12:00:00.557 → 2024/01/30 12:00:00.556667

対応案と検証

• datetimeとdatetime2(6)を比較する際には、datetime2(6)に変換して比較する (精度の高いほうに合わせる)

  • 2024/01/30 12:00:00.557をdatetime2(6)に変換すると前述の通り、2024-01-30 12:00:00.556667になるので一致しないデータになってしまう

• datetimeとdatetime2(6)を比較する際には、datetimeに変換して比較する (精度の低いほうに合わせる)

  • 2024/01/30 12:00:00.555000をdatetimeに変換すると2024-01-30 12:00:00.557になる
  • 

• datetimeのデータをdatetime2(6)のカラムにINSERTするときは、datetime2(6)に変換してからINSERTする

  • datetime: 2024/01/30 12:00:00.557 をdatetime2(6)に投入すると前述の通り、2024/01/30 12:00:00.556667となるので一致しないデータになる

今回採用した対応方法

datetimeの精度が低い方にCONVERTして比較するようにして対応
精度が高い方にCONVERTすると前述した通り、最後の1桁が丸められてしまうので一度更新したあと再び更新対象になってしまう

OR (COALESCE(CONVERT(new_table.modified_at, datetime), DefaultDatetime) <> COALESCE(existing_table
.modified_at, DefaultDatetime))

結果

データ差分があるときのみ更新が入るようになった