DATETIMEデータ型のミリ秒丸め現象の対応方法

記事内に広告が含まれています。

DATETIMEデータ型のミリ秒には丸めがあり、利用する場合には注意が必要です。

DATETIMEデータ型の精度は時刻範囲が 00:00:00 ~ 23:59:59.997 であり、ミリ秒の精度が .000、.003、.007 であるためです。.000、.003、.007 以外の値は .000、.003、.007 に丸められます。

なので、DATETIMEデータ型をキーにレコードを更新しようとするとマッチしない場合が発生します。

DATETIMEデータ型のミリ秒には丸めがあります

SQLServerのDATETIMEデータ型のユニークキーを設定し、システム時間を挿入してあるテーブルを管理していたのですが、どうもDATETIMEデータ型のユニークキーで更新に行くと「更新レコードがない」という現象に見舞われました。

処理としてはレコード挿入時のシステム時間を取得して、そのシステム時間をキー情報にレコードを挿入し、その後その取得したシステム時間にレコード更新しにいくというもの。

基本的には「挿入エラー」が無い限りレコードは更新対象レコードは1件あるはず。

複数の端末から同時挿入以外は「挿入エラー」はまずない。

しかも使用するユーザー数が少ないため、まず衝突することはないはず。

しかしながら「更新レコードがない」という現象が日に何度か発生。

挿入した日付と日付が挿入されていることが判明

試しにテスト用のテーブルを作成しデータを挿入してみます。

CREATE TABLE test_table ( no INT, hiduke DATETIME) 

INSERT into test_table VALUES (  0, '2013/11/25 23:59:59.990' ) 
INSERT into test_table VALUES (  1, '2013/11/25 23:59:59.991' ) 
INSERT into test_table VALUES (  2, '2013/11/25 23:59:59.992' ) 
INSERT into test_table VALUES (  3, '2013/11/25 23:59:59.993' ) 
INSERT into test_table VALUES (  4, '2013/11/25 23:59:59.994' ) 
INSERT into test_table VALUES (  5, '2013/11/25 23:59:59.995' ) 
INSERT into test_table VALUES (  6, '2013/11/25 23:59:59.996' ) 
INSERT into test_table VALUES (  7, '2013/11/25 23:59:59.997' ) 
INSERT into test_table VALUES (  8, '2013/11/25 23:59:59.998' ) 
INSERT into test_table VALUES (  9, '2013/11/25 23:59:59.999' ) 

で、挿入したデータの「no=1」のデータの取得を目指してミリ秒が「990」の行を選択してみます。

SELECT no, hiduke FROM test_table WHERE hiduke = '2013/11/25 23:59:59.990'

結果は

no          hiduke
0    2013-11-25 23:59:59.990
1    2013-11-25 23:59:59.990

2件取得。

noが「0」と「1」になっているので、どうやら「990」と「991」は同じになっている模様。

試しに「991」で検索してみると

no          hiduke
0    2013-11-25 23:59:59.990
1    2013-11-25 23:59:59.990

2件取得。
結果は同じなので「990」=「991」と扱われているようです。

「992」で検索すると

SELECT no, hiduke FROM test_table WHERE hiduke = '2013/11/25 23:59:59.992'

結果は

no          hiduke
2    2013-11-25 23:59:59.993
3    2013-11-25 23:59:59.993
4    2013-11-25 23:59:59.993

と3件ヒット。

「992」で検索すると

SELECT no, hiduke FROM test_table WHERE hiduke = '2013/11/25 23:59:59.993'

結果は

no          hiduke
2    2013-11-25 23:59:59.993
3    2013-11-25 23:59:59.993
4    2013-11-25 23:59:59.993

と結果は同じ3件。

どうやらミリ秒は丸められているようです。

ミリ秒の丸めについて調べてみると

datetime データ型の時刻範囲が 00:00:00 ~ 23:59:59.997 であり、ミリ秒の精度が .000、.003、.007 であるためです。.000、.003、.007 以外の値は .000、.003、.007 に丸められます。

という記述をつけた。

実験してみると

.990->990
.991->990
.992->993
.993->993
.994->993
.995->997
.996->997
.997->997
.998->997
.999->000(※日付が次の日になる)

にそれぞれ丸めが発生する。

結果、挿入したレコードの日付と更新しようとした日付でミリ秒単位でずれが発生し「更新レコードがない」という現象が発生することが判明。

ミリ秒単位で丸めが発生しないようにするには

このようなミリ秒単位で丸めが発生しないようにするには「datetime」型をやめ「datetime2」型にすることで解決する。

CREATE TABLE test_table ( no INT, hiduke DATETIME2) 

INSERT into test_table VALUES (  0, '2013/11/25 23:59:59.990' ) 
INSERT into test_table VALUES (  1, '2013/11/25 23:59:59.991' ) 
INSERT into test_table VALUES (  2, '2013/11/25 23:59:59.992' ) 
INSERT into test_table VALUES (  3, '2013/11/25 23:59:59.993' ) 
INSERT into test_table VALUES (  4, '2013/11/25 23:59:59.994' ) 
INSERT into test_table VALUES (  5, '2013/11/25 23:59:59.995' ) 
INSERT into test_table VALUES (  6, '2013/11/25 23:59:59.996' ) 
INSERT into test_table VALUES (  7, '2013/11/25 23:59:59.997' ) 
INSERT into test_table VALUES (  8, '2013/11/25 23:59:59.998' ) 
INSERT into test_table VALUES (  9, '2013/11/25 23:59:59.999' ) 
 
SELECT no, hiduke FROM test_table ORDER BY no

実行結果は

no          hiduke
0    2013-11-25 23:59:59.9900000
1    2013-11-25 23:59:59.9910000
2    2013-11-25 23:59:59.9920000
3    2013-11-25 23:59:59.9930000
4    2013-11-25 23:59:59.9940000
5    2013-11-25 23:59:59.9950000
6    2013-11-25 23:59:59.9960000
7    2013-11-25 23:59:59.9970000
8    2013-11-25 23:59:59.9980000
9    2013-11-25 23:59:59.9990000

でミリ秒の単位での丸めは発生していない。

本来はユニークキーを独自に生成するにはUNIQUEIDENTIFIER型

本来はこのような場合は同時挿入の衝突を避けて、ユニークキーを独自に生成するにはUNIQUEIDENTIFIER型を利用し、挿入する値をNEWID()で取得して挿入するべきである。

そうすればこのような丸めの問題にぶち当たることもなく、トラブルを未然に防げる。

タイトルとURLをコピーしました