數(shù)據(jù)庫日期類型字段設(shè)計(jì)應(yīng)該怎么選擇

本文小編為大家詳細(xì)介紹“數(shù)據(jù)庫日期類型字段設(shè)計(jì)應(yīng)該怎么選擇”，內(nèi)容詳細(xì)，步驟清晰，細(xì)節(jié)處理妥當(dāng)，希望這篇“數(shù)據(jù)庫日期類型字段設(shè)計(jì)應(yīng)該怎么選擇”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學(xué)習(xí)新知識(shí)吧。

當(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

長(zhǎng)度:占用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ū)里，例如中國(guó)都是使用東八區(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ǎn)單明了的結(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í)可以使用。測(cè)試了 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)確性要求。

讀到這里，這篇“數(shù)據(jù)庫日期類型字段設(shè)計(jì)應(yīng)該怎么選擇”文章已經(jīng)介紹完畢，想要掌握這篇文章的知識(shí)點(diǎn)還需要大家自己動(dòng)手實(shí)踐使用過才能領(lǐng)會(huì)，如果想了解更多相關(guān)內(nèi)容的文章，歡迎關(guān)注億速云行業(yè)資訊頻道。