淺談資料庫日期型別欄位設計應該如何選擇

當設計一個產品，其中很多地方要把日期型別儲存到資料庫中，如果產品有相容不同資料庫產品的需求，那麼，應當怎樣設計呢？

當然，首先想到的是，使用資料庫的 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 維護
• 到什麼時候，即便沒有看到資料庫設計檔案，都能看明白並準確理解資料庫中一條資訊中，這個欄位儲存到確切資訊

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

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

到此這篇關於淺談資料庫日期型別欄位設計應該如何選擇的文章就介紹到這了,更多相關資料庫日期型別欄位設計內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！