前幾天學到新知識 - SQL 的 DateTime 型別雖然可顯示到毫秒(0.001秒),但精確度非 0.001 秒,最後一位會取相近值到 0, 3, 或 7。

想起過去曾遇過時間資料存入 SQL 比對失敗的經驗,當時不知其所以然,也忘了最後怎麼解決,猜想跟此一特性有關。決定做個實驗釐清觀念,強化記憶。

我在 LocalDB 建了一個 MyTable 資料表,TimeKey 欄位使用 SQL DateTime 型別:

我寫了一段程式會抓取當下 DateTime.Now 寫入 MyTable.TimeKey,再馬上用 SELECT WHERE 查詢讀回來,比對寫入資料庫的值與 C# 端的 DateTime 變數是否相符。有幾種可能:

  1. WHERE 條件找不到剛才寫入的資料
  2. 由資料庫讀取還原回 C# DateTime 與原變數完全相等
  3. 資料庫讀取值與原變數到毫秒(ms)都是一致的
  4. 資料庫讀取值與原變數到秒都是一致的
  5. 二者連秒都不一致

程式範例如下:

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

這麼簡單!? 還沒做過,我試試. 謝謝

Post a comment