SQL Server

DATETIMEデータ型のミリ秒丸め現象

投稿日:2013/11/27 更新日:

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

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

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

基本的には「挿入エラー」が無い限りレコードは更新対象レコードは
1件あるはず。
複数の端末から同時挿入以外は「挿入エラー」はまずない。
しかも使用するユーザー数が少ないため、まず衝突することは
ないはず。

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

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

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

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

結果は

2件取得。

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

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

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

「992」で検索すると

結果は

と3件ヒット。

「992」で検索すると

結果は

と結果は同じ3件。

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

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

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


という記述をつけた。

実験してみると

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

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

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

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

実行結果は

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

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

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

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

当サイトのおすすめ記事

パソコン 1

当サイトは約5年間、さくらのレンタルサーバで運用させて頂きました。 何かトラブルがあったわけではないのですが、WordPressの表示速度を速くしたくてSSD搭載のレンタルサーバーで運用してみることに ...

プログラミング 2

Contents1 SQLでIFを使わずに条件分岐する1.1 1.テーブルとデータの準備1.2 2.条件を分岐させて検索1.3 3.解説 SQLでIFを使わずに条件分岐する SQLでIFを書けばWHE ...

SQLServer 3

SQLServerで発生するエラーコードをSQLで取得する SQLServerで発生したエラーコードの内容を メッセージが定義されているテーブルから取得します。 エラーコードを取得するSQL SQLs ...

4

Contents1 SQLServerでテーブル型を戻り値とする関数を作る1.1 テーブル型を戻り値とする関数の作成1.2 呼び出しかた SQLServerでテーブル型を戻り値とする関数を作る SQL ...

5

Contents1 アフィリエイトで即効成果を出す1.1 自分で買えば確実に成果が上がる1.2 A8.netでは自分で購入してもOKなものがある1.2.1 A8.netにサイトを登録する1.2.2 承 ...

-SQL Server
-, ,

Copyright© ソフトウェア開発日記 , 2017 AllRights Reserved.