SQL DateTime 型別陷阱
6 |
前幾天學到新知識 - SQL 的 DateTime 型別雖然可顯示到毫秒(0.001秒),但精確度非 0.001 秒,最後一位會取相近值到 0, 3, 或 7。
想起過去曾遇過時間資料存入 SQL 比對失敗的經驗,當時不知其所以然,也忘了最後怎麼解決,猜想跟此一特性有關。決定做個實驗釐清觀念,強化記憶。
我在 LocalDB 建了一個 MyTable 資料表,TimeKey 欄位使用 SQL DateTime 型別:
我寫了一段程式會抓取當下 DateTime.Now 寫入 MyTable.TimeKey,再馬上用 SELECT WHERE 查詢讀回來,比對寫入資料庫的值與 C# 端的 DateTime 變數是否相符。有幾種可能:
- WHERE 條件找不到剛才寫入的資料
- 由資料庫讀取還原回 C# DateTime 與原變數完全相等
- 資料庫讀取值與原變數到毫秒(ms)都是一致的
- 資料庫讀取值與原變數到秒都是一致的
- 二者連秒都不一致
程式範例如下:
static void Main(string[] args)
{
using (var cn = GetConnection())
{
cn.Execute("DELETE FROM MyTable");
Enumerable.Range(1, 32).ToList().ForEach(i =>
{
Thread.Sleep(100);
var d = DateTime.Now;
Console.Write($"{i:00}. TimeKey={d:yyyy/MM/dd HH:mm:ss.fff} : ");
cn.Execute("INSERT INTO MyTable (TimeKey,SeqNo) VALUES (@d, @i)",
new { d, i });
var res = cn.Query("SELECT * FROM MyTable WHERE TimeKey=@d", new { d }).SingleOrDefault();
if (res == null)
{
Console.WriteLine("NOT FOUND");
return;
}
var dateTimeFromSql = (DateTime)res.TimeKey;
Debug.WriteLine(JsonConvert.SerializeObject(d) + " vs " + JsonConvert.SerializeObject(dateTimeFromSql));
if (d.CompareTo(dateTimeFromSql) == 0)
{
Console.WriteLine("完全相等");
}
else if (d.ToString("yyyy/MM/dd HH:mm:ss.fff") == dateTimeFromSql.ToString("yyyy/MM/dd HH:mm:ss.fff"))
{
Console.WriteLine($"相符至亳秒:{d.ToString("yyyy/MM/dd HH:mm:ss.fff")}");
}
else if (d.ToString("yyyy/MM/dd HH:mm:ss") == dateTimeFromSql.ToString("yyyy/MM/dd HH:mm:ss"))
{
Console.WriteLine($"相符至秒:{d.ToString("yyyy/MM/dd HH:mm:ss")}");
}
else
{
Console.WriteLine($"不相等[{dateTimeFromSql:yyyy/MM/dd HH:mm:ss.fff}]");
}
});
Console.ReadLine();
}
}
執行結果印證了 SQL 端 DateTime 毫秒只有 0,3,7 三種尾數的說法,當尾數為 0,3,7 時相符到毫秒,否則只相等到秒。由於 C# 的 DateTime 的最小單位是 Tick (1/10,000,000 秒),SQL DateTime 只到 ms (1/1,000 秒),除非 Tick 值剛好可被 10000 整除(最後四碼均為 0),毫秒尾數又是 0,3,7,二者才會完全相等,
SQL DateTime2 型別提供與 C# DateTime 相當的精準度,可以改善這個問題。所以我將 MyTable.TimeKey 換成 DateTime2:
天真以為換成 SQL DateTime2 型別什麼都不用做就百病消,但事實不然!
程式噴出一堆 WHERE 條件查不到錯誤。推究原因應是 Dapper 處理 INSERT 及 WHERE 時 @d 對應成 SQL DateTime 型別而非 DateTime2 來回處理過程產生誤差。一個簡單的解決方法是加入 SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.DateTime2); 指定將 C# DateTime 對應成 SQL DateTime2:
static void Main(string[] args)
{
using (var cn = GetConnection())
{
cn.Execute("DELETE FROM MyTable");
Enumerable.Range(1, 32).ToList().ForEach(i =>
{
Thread.Sleep(100);
var d = DateTime.Now;
Console.Write($"{i:00}. TimeKey={d:yyyy/MM/dd HH:mm:ss.fff} : ");
SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.DateTime2);
cn.Execute("INSERT INTO MyTable (TimeKey,SeqNo) VALUES (@d, @i)",
//...略...
重測一次,完全相等。
Tips of difference in precisions between SQL DateTime and DateTime2 type when using them in C#.
Comments
# by Huang
感謝,阱陷蠻大的,無奈時間是類比不是數位,在數位電腦2^n只能靠越大的儲存精度去逼進,不過TSQL這招也真夠!#@$#%,javascript也有類似的陷阱XD,
# by Book
請問,這是用 MS. SQL 做的實驗嗎?
# by Jeffrey
to Book, 對,這是 MS SQL相關議題。
# by Book
請問,你知不知道該如何遠端存取資料庫? 一般 local m.s. sql 資料庫的存取方式,書裡面都有範例。遠端的資料庫存取方式不知道要如何寫連結字串。
# by Jeffrey
to Book, 如果是 MSSQL,寫 Data Source=IP;User Id=xxx;Password=xxx;Initial Catalog=DBName 連不上?
# by Book
這麼簡單!? 還沒做過,我試試. 謝謝