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

公開日: : 最終更新日:2013/12/10 SQL Server , ,

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()で取得して挿入するべきである。

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

スポンサーリンク
スポンサーリンク
  • このエントリーをはてなブックマークに追加
  • 13 follow us in feedly
  • 関連記事

    SQLで指定された年月日の月末日付を取得する

    指定された年月日の月末日付を取得 SQLserverには指定された年月日の月末日付を取得する関

    記事を読む

    SQLServerで自動で連番を設定する

    カラムに自動で連番を設定する 自動で連番を設定し、レコードを管理する場合に 重宝するカラムの属性

    記事を読む

    sp_pkeys – SQL Serverでテーブルのプライマリキーを取得する

    sp_pkeysを使ってSQL Serverでテーブルのプライマリキーを取得します。

    記事を読む

    MERGEでINSERTとUPDATEを1行で実行する

    MERGE文を使うと行の存在有無を気にせずに1行のSQLでOK あるテ

    記事を読む

    SQLServerのテーブルやカラムにコメントをつける方法

    SQLServerでテーブルやカラムにコメントをつける SQLServerで作成したテーブルやカラ

    記事を読む

    SQLServer

    SQLServerでトリガーを扱ってみる

    INSERT、UPADTE、DELETEトリガーを扱ってみる SQLServerにはテーブルに対し

    記事を読む

    DATENAME関数を使って日付を取得する。

    DATENAME関数を使って日付取得 SQLServerでDATENAME関数を使って日付を取得し

    記事を読む

    SQLServerのテーブルロック状態を取得するSQL

    SQLServerのデッドロックを調査している中で ロックの状態を調べるSQLがあることが 判明

    記事を読む

    SQLServer

    SQLServerで「WITH ROLLUP」を使って小計、総合計を出力する

    「WITH ROLLUP」を使って小計、総合計を出力 SQLServerでは「WITH ROLLU

    記事を読む

    SQLで文字列を3分割する

    SQLで文字列を3分割する どうしてもSQLServerで特定の文字で文字列を3分割する必要が

    記事を読む

    Message

    メールアドレスが公開されることはありません。 * が付いている欄は必須項目です


    *

    列は既にデフォルトにバインドされています。

    テーブルのカラムの属性変更でデフォルト値を変更する テーブルのカラム

    SQLServer
    指定した値と同じ場合はNULLを返すNULLIF関数

    使いどころがいまいち不明な「NULLIF()」 SQLServerに

    テーブル型を戻り値とする関数の作成方法

    SQLServerでテーブル型を戻り値とする関数を作る SQLSer

    SQLServer
    SQLServerで引数の順序を考慮せずに呼び出せるストアドプロシジャを作る

    引数の順序を考慮せずに呼び出せるストアドプロシジャ ストアドプロシジ

    SQLで文字列を3分割する

    SQLで文字列を3分割する どうしてもSQLServerで特定の

    →もっと見る



    PAGE TOP ↑