當(dāng)設(shè)計(jì)一個(gè)產(chǎn)品，其中很多地方要把日期類型保存到數(shù)據(jù)庫中，如果產(chǎn)品有兼容不同數(shù)據(jù)庫產(chǎn)品的需求，那么，應(yīng)當(dāng)怎樣設(shè)計(jì)呢？

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

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

范圍：

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

Oracle 數(shù)據(jù)庫：

Date 類型的內(nèi)部編碼為12

長度:占用7個(gè)字節(jié)
數(shù)據(jù)存儲(chǔ)的每一位到第七位分別為：世紀(jì)，年，月，日，時(shí)，分，秒

TIMESTAMP是支持小數(shù)秒和時(shí)區(qū)的日期/時(shí)間類型。對(duì)秒的精確度更高

TIMESTAMP WITH TIME ZONE 類型是 TIMESTAMP 的子類型，增加了時(shí)區(qū)支持，占用13字節(jié)的存儲(chǔ)空間，最后兩位用于保存時(shí)區(qū)信息

INTERVAL 用于表示一段時(shí)間或一個(gè)時(shí)間間隔的方法。在前面有多次提過。INTERVAL有兩種類型.

• YEAR TO MONTH 能存儲(chǔ)年或月指定的一個(gè)時(shí)間段.
• DATE TO SECOND 存儲(chǔ)天,小時(shí),分鐘,秒指定的時(shí)間段.

sql server：datetime 和 smalldatetime

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

如果沒有兼容多種數(shù)據(jù)庫這個(gè)要求，我會(huì)毫不猶豫的使用數(shù)據(jù)庫的 Date 類型。因?yàn)槿绻褂?Java 框架產(chǎn)生代碼，對(duì)數(shù)據(jù)庫中定義為 Date 類型的字段，甚至能在頁面上產(chǎn)生出JS的時(shí)間選擇框，的確能節(jié)省很多開發(fā)時(shí)間。而兼容不同數(shù)據(jù)庫，就希望產(chǎn)品在由一種數(shù)據(jù)庫，遷移到另外一種數(shù)據(jù)庫時(shí)，盡可能小的代價(jià)，使用了 Date，看來就很困難了。

有一個(gè)疑問，不知道目前流行的ORM對(duì)這個(gè)處理得是不是好？因?yàn)楣ぷ鞑辉趺瓷婕斑@方面，所以不大了解。

在之前的設(shè)計(jì)開發(fā)中，因?yàn)橛兄С侄喾N數(shù)據(jù)庫這種需求，所以首先否定了日期時(shí)間這樣的類型。

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

Java 取到的毫秒數(shù)是對(duì)時(shí)間點(diǎn)的一種準(zhǔn)確描述。定義如下：java.lang.System.currentTimeMillis()，它返回從 UTC 1970 年 1 月 1 日午夜開始經(jīng)過的毫秒數(shù)。

我們可以看到，這個(gè)定義，保證了這個(gè)時(shí)間值能夠被后續(xù)設(shè)計(jì)開發(fā)的人員正確和準(zhǔn)確的理解，能夠?yàn)樗械膽?yīng)用正確理解，能夠在所有時(shí)區(qū)上正確反映為正常的時(shí)間形式。

當(dāng)時(shí)的產(chǎn)品設(shè)計(jì)是有海外客戶的，所以當(dāng)時(shí)的設(shè)計(jì)，在數(shù)據(jù)庫里保存的，應(yīng)該是一個(gè)“準(zhǔn)確的時(shí)間”。例如“20120926080000”實(shí)際上并沒有嚴(yán)格的表示出時(shí)間，因?yàn)楸本r(shí)間2012年9月26日8點(diǎn)和格林威治時(shí)間2012年9月26日8點(diǎn)顯然是不一樣的。

雖然我們都是在一個(gè)確切的時(shí)區(qū)里，例如中國都是使用東八區(qū)時(shí)間，但是需要考慮的是：

• 有些產(chǎn)品是可能有海外客戶的
• 產(chǎn)品所運(yùn)行的機(jī)器，時(shí)區(qū)的設(shè)置未必都是東八區(qū)。

在這種情況下，如果數(shù)據(jù)庫里的時(shí)間不準(zhǔn)確，會(huì)給程序運(yùn)行帶來問題。這種方式最大的缺點(diǎn)在于：

• 不方便對(duì)時(shí)間進(jìn)行分組查詢，比如按月統(tǒng)計(jì)、按季 統(tǒng)計(jì)
• DBA在維護(hù)時(shí)，不能直觀的根據(jù)返回的行結(jié)果，看到簡單明了的結(jié)果（看到的是毫秒數(shù)）

使用這種方式的特點(diǎn)是犧牲一點(diǎn)易用性和可理解性（不易于維護(hù)和理解），滿足了查詢結(jié)果的直觀性和準(zhǔn)確性要求，同時(shí)最大限度考慮運(yùn)行效率。為了解決這個(gè)問題，我設(shè)計(jì)了一個(gè)輔助的措施，就是建立一個(gè)數(shù)據(jù)庫函數(shù)來進(jìn)行時(shí)間轉(zhuǎn)換，把毫秒數(shù)的時(shí)間轉(zhuǎn)為制定時(shí)區(qū)和格式的時(shí)間串，DBA 在維護(hù)時(shí)可以使用。測試了 Oracle 和 DB2 上，都可以這樣。例如之前的查詢的時(shí)候?yàn)椋?/p>

SELECT username,user_addtime from userinfo

這個(gè)查詢顯示的是毫秒數(shù)，使用內(nèi)置函數(shù)后寫成：

SELECT username,date2str(user_addtime) from userinfo

這樣返回的就是東八區(qū)、預(yù)先定義好格式的字符串了。

在之后的設(shè)計(jì)里，還使用過 YYYYMMDDHHmmSST 格式，其中的“T”指時(shí)區(qū)，加入時(shí)區(qū)，帶來的影響有：

• 日期時(shí)間字段就不能在使用數(shù)值來存儲(chǔ)了，字符串比數(shù)字存儲(chǔ)和檢索的效率都要低。
• 應(yīng)用程序需要加上額外的處理

帶來的好處是：

• 便于 DBA 維護(hù)
• 到什么時(shí)候，即便沒有看到數(shù)據(jù)庫設(shè)計(jì)文檔，都能看明白并準(zhǔn)確理解數(shù)據(jù)庫中一條信息中，這個(gè)字段保存到確切信息

使用這種方式的特點(diǎn)是犧牲一點(diǎn)效率，滿足了查詢結(jié)果的直觀性和準(zhǔn)確性要求。

總結(jié)一下，字段類型的選擇，還是根據(jù)場景的需要來選擇，從功能、效率要求、持續(xù)開發(fā)的要求、維護(hù)的要求幾個(gè)方面綜合考慮。

到此這篇關(guān)于淺談數(shù)據(jù)庫日期類型字段設(shè)計(jì)應(yīng)該如何選擇的文章就介紹到這了,更多相關(guān)數(shù)據(jù)庫日期類型字段設(shè)計(jì)內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！