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

関連記事

SQLServerでカンマ区切り (CSV) で文字列取得

SQLの取得結果をカンマ区切りで取得 Where句のINにSQLで取得した結果を直接利用したり

記事を読む

SQLServer

テーブルのプライマリキー(PK)を取得する(SQL編)

SQLServerでテーブルのプライマリキー(PK)をSQLで取得する SQLServerでテーブ

記事を読む

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

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

記事を読む

SQLで文字列を反転させる

SQLで文字列を反転させる SQLで文字列を"012345"を後ろから並び変えて"543210"の

記事を読む

SELECTした結果をランダムに並び変える

SQLでSELECTした結果をランダムに並び変えて表示する SQLServerでSELECTした結

記事を読む

SQL Server-登録されているストアドプロシジャーの一覧を取得する

データベースに登録されているストアドプロシジャーの 一覧を取得します。

記事を読む

SQLで改行コードを含む項目を取得する

SQLで改行コードを含む項目を検索する Microsoft SQL Server Managemn

記事を読む

ORDER BYでNULLのカラムを最後に表示

ORDER BYでNULLのカラムを最後に持ってくる SQLで何も考慮せずに「ORDER BY」す

記事を読む

システム日付を取得する

様々な関数でコンピューターのシステム日付を取得する SQLServerのインスタンスを実行している

記事を読む

SQLServerでテーブルの存在を確認する

データベース内のテーブル情報をから存在を確認する プログラムで動的にテーブルを作成したりする場合に

記事を読む

Message

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

*

SQL_VARIANT_PROPERTYを使ってカラムの基本データ型を取得する

カラムの基本データ型取得 SQLサーバーで定義されているテーブルのカ

0で埋めで固定長のデータにする

0で埋めで固定長のデータにする データべース内のデータを0埋めして取

SELECTした結果をランダムに並び変える

SQLでSELECTした結果をランダムに並び変えて表示する SQLS

SQLで順位を取得するRANK()とDENSE_RANK()

SQLで順位を取得する SQLServerで項目の順位付け関数を利用

SQLServerのエラーコード一覧

SQLServerで発生するエラーコードとその内容を表にしてみました。

→もっと見る



PAGE TOP ↑