淺談數據庫日期類型字段設計應該如何選擇

Posted on by

當設計一個產品，其中很多地方要把日期類型保存到數據庫中，如果產品有兼容不同數據庫產品的需求，那麼，應當怎樣設計呢？

當然，首先想到的是，使用數據庫的 Date 或 DateTime 類型，可是看看不同數據庫這些類型間的區別吧，真讓人望而止步。Mysql 數據庫：它們分別是 date、datetime、time、timestamp 和 year。

  • date ：“yyyy-mm-dd”格式表示的日期值
  • time ：“hh:mm:ss”格式表示的時間值
  • datetime： “yyyy-mm-dd hh:mm:ss”格式
  • timestamp： “yyyymmddhhmmss”格式表示的時間戳值
  • year： “yyyy”格式的年份值。

范圍：

  • date “1000-01-01” 到 “9999-12-31” 3字節
  • time “-838:59:59” 到 “838:59:59” 3字節
  • datetime “1000-01-01 00:00:00” 到 “9999-12-31 23:59:59” 8字節
  • timestamp 19700101000000 到 2037 年的某個時刻 4字節
  • year 1901 到 2155 1 字節

Oracle 數據庫：

Date 類型的內部編碼為12

長度:占用7個字節
數據存儲的每一位到第七位分別為：世紀，年，月，日，時，分，秒

TIMESTAMP是支持小數秒和時區的日期/時間類型。對秒的精確度更高

TIMESTAMP WITH TIME ZONE 類型是 TIMESTAMP 的子類型，增加瞭時區支持，占用13字節的存儲空間，最後兩位用於保存時區信息

INTERVAL 用於表示一段時間或一個時間間隔的方法。在前面有多次提過。INTERVAL有兩種類型.

  • YEAR TO MONTH 能存儲年或月指定的一個時間段.
  • DATE TO SECOND 存儲天,小時,分鐘,秒指定的時間段.

sql server：datetime 和 smalldatetime

  • datetime數據類型所占用的存儲空間為8個字節，其中前4個字節用於存儲1900年1月1日以前或以後的天數，數值分正負，正數表示在此日期之後的日期，負數表示在此日期之前的日期；後4個字節用於存儲從此日零時起所指定的時間經過的毫秒數。
  • smalldatetime數據類型使用4個字節存儲數據。其中前2個字節存儲從基礎日期1900年1月1日以來的天數，後兩個字節存儲此日零時起所指定的時間經過的分鐘數。
  • smalldatetime數據類型與datetime數據類型相似，但其日期時間范圍較小，從1900年1月1日到2079年6月6日。此數據類型精度較低，隻能精確到分鐘，其分鐘個位為根據秒數四舍五入的值，即以30秒為界四舍五入。

如果沒有兼容多種數據庫這個要求，我會毫不猶豫的使用數據庫的 Date 類型。因為如果使用 Java 框架產生代碼，對數據庫中定義為 Date 類型的字段，甚至能在頁面上產生出JS的時間選擇框，的確能節省很多開發時間。而兼容不同數據庫，就希望產品在由一種數據庫，遷移到另外一種數據庫時，盡可能小的代價，使用瞭 Date，看來就很困難瞭。

有一個疑問，不知道目前流行的ORM對這個處理得是不是好？因為工作不怎麼涉及這方面，所以不大瞭解。

在之前的設計開發中，因為有支持多種數據庫這種需求，所以首先否定瞭日期時間這樣的類型。

曾經使用過毫秒數（Java 的 System.currentTimeMillis()）這種方式，但是選用這個方式，考慮的不是使用起來是否方便或者數據遷移，而是考慮到下面的原因：

Java 取到的毫秒數是對時間點的一種準確描述。定義如下：java.lang.System.currentTimeMillis()，它返回從 UTC 1970 年 1 月 1 日午夜開始經過的毫秒數。

我們可以看到，這個定義，保證瞭這個時間值能夠被後續設計開發的人員正確和準確的理解，能夠為所有的應用正確理解，能夠在所有時區上正確反映為正常的時間形式。

當時的產品設計是有海外客戶的，所以當時的設計，在數據庫裡保存的，應該是一個“準確的時間”。例如“20120926080000”實際上並沒有嚴格的表示出時間，因為北京時間2012年9月26日8點和格林威治時間2012年9月26日8點顯然是不一樣的。

雖然我們都是在一個確切的時區裡，例如中國都是使用東八區時間，但是需要考慮的是：

  • 有些產品是可能有海外客戶的
  • 產品所運行的機器，時區的設置未必都是東八區。

在這種情況下，如果數據庫裡的時間不準確，會給程序運行帶來問題。這種方式最大的缺點在於：

  • 不方便對時間進行分組查詢，比如按月統計、按季 統計
  • DBA在維護時，不能直觀的根據返回的行結果，看到簡單明瞭的結果（看到的是毫秒數）

使用這種方式的特點是犧牲一點易用性和可理解性（不易於維護和理解），滿足瞭查詢結果的直觀性和準確性要求，同時最大限度考慮運行效率。為瞭解決這個問題，我設計瞭一個輔助的措施，就是建立一個數據庫函數來進行時間轉換，把毫秒數的時間轉為制定時區和格式的時間串，DBA 在維護時可以使用。測試瞭 Oracle 和 DB2 上，都可以這樣。例如之前的查詢的時候為：

SELECT username,user_addtime from userinfo

這個查詢顯示的是毫秒數，使用內置函數後寫成：

SELECT username,date2str(user_addtime) from userinfo

這樣返回的就是東八區、預先定義好格式的字符串瞭。

在之後的設計裡，還使用過 YYYYMMDDHHmmSST 格式，其中的“T”指時區，加入時區，帶來的影響有：

  • 日期時間字段就不能在使用數值來存儲瞭，字符串比數字存儲和檢索的效率都要低。
  • 應用程序需要加上額外的處理

帶來的好處是：

  • 便於 DBA 維護
  • 到什麼時候，即便沒有看到數據庫設計文檔，都能看明白並準確理解數據庫中一條信息中，這個字段保存到確切信息

使用這種方式的特點是犧牲一點效率，滿足瞭查詢結果的直觀性和準確性要求。

總結一下，字段類型的選擇，還是根據場景的需要來選擇，從功能、效率要求、持續開發的要求、維護的要求幾個方面綜合考慮。

到此這篇關於淺談數據庫日期類型字段設計應該如何選擇的文章就介紹到這瞭,更多相關數據庫日期類型字段設計內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：