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で作ったテンポラリテーブルが見つからず無効になってしまう

SQLServerでテンポラリテーブルを利用して処理を行う時に 「オブジェクト名 '#tmp_ta

記事を読む

日付の重複をSQLで検索する

SQLを使って期間の重複を見つけ出す SQLを使って、開始日付、終了日付を持っているレコードの

記事を読む

SQLサーバ

SQLServerでROW_NUMBER()を使って連番を採番する

ROW_NUMBER()で連番を取得する SQLServerでROW_NUMBER()関数を使うと

記事を読む

SQLServerでsp_renameを使ってテーブルの列名を変更する

SQLServerで列名を変更するには sp_renameを使って列名を変更 SQLServ

記事を読む

SQLで当月の第何週目の何曜日か取得する

SQLで今日は第何週、何曜日か取得する SQLServerでは関数で今日は当月の第何週目か取得する

記事を読む

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

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

記事を読む

SQLServer上にあるストアドプロシージャーの更新日時を取得する。

ストアドプロシージャーの更新日時を取得する ここ数日間で変更されたSQLServer上にあるストア

記事を読む

SQLServerのダミーテーブル

SQLServerでダミーテーブルを使う データベースサーバから現在時刻などを取得する場合など

記事を読む

SQLで存在しない行を生成し固定行数で処理したい時に役立つ方法

帳票データなどで登録されているデータ数に左右されず、 固定行数でデータを取得したい場合があります。

記事を読む

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

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

記事を読む

Message

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


*

指定された月の日数をSQLで求める

指定された月の日数をSQLで求める SQLServerには該当月

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

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

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

ORDER BYでNULLのカラムを最後に持ってくる SQLで何も考

SQLで指定月のカレンダー作成

ストアドプロシージャでカレンダーを作成する SQLServerでスト

SQLで文字列を3分割する

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

→もっと見る



PAGE TOP ↑