數(shù)據(jù)庫日期類型字段設(shè)計，應(yīng)該如何選擇？
往期熱門文章：
1、40 個 SpringBoot 常用注解：讓生產(chǎn)力爆表！
3、BigDecimal使用不當(dāng)，造成P0事故！
4、改造BeanUtils，優(yōu)雅實現(xiàn)List數(shù)據(jù)拷貝
5、SpringBoot 啟動時自動執(zhí)行代碼的幾種方式，還有誰不會？？
作者：sp42a
來源：https://zhangxin.blog.csdn.net/article/details/124955363
當(dāng)設(shè)計一個產(chǎn)品，其中很多地方要把日期類型保存到數(shù)據(jù)庫中，如果產(chǎn)品有兼容不同數(shù)據(jù)庫產(chǎn)品的需求，那么，應(yīng)當(dāng)怎樣設(shè)計呢？
當(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”格式表示的時間值
datetime：“yyyy-mm-dd hh:mm:ss”格式
timestamp：“yyyymmddhhmmss”格式表示的時間戳值
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 年的某個時刻 4字節(jié)
year 1901 到 2155 1 字節(jié)
Oracle 數(shù)據(jù)庫：
Date 類型的內(nèi)部編碼為12
長度：占用7個字節(jié)
數(shù)據(jù)存儲的每一位到第七位分別為：世紀(jì)，年，月，日，時，分，秒
TIMESTAMP是支持小數(shù)秒和時區(qū)的日期/時間類型。對秒的精確度更高
TIMESTAMP WITH TIME ZONE 類型是 TIMESTAMP 的子類型，增加了時區(qū)支持，占用13字節(jié)的存儲空間，最后兩位用于保存時區(qū)信息
INTERVAL 用于表示一段時間或一個時間間隔的方法。在前面有多次提過。INTERVAL有兩種類型.
YEAR TO MONTH 能存儲年或月指定的一個時間段.
DATE TO SECOND 存儲天,小時,分鐘,秒指定的時間段.
sql server：datetime 和 smalldatetime
datetime數(shù)據(jù)類型所占用的存儲空間為8個字節(jié)，其中前4個字節(jié)用于存儲1900年1月1日以前或以后的天數(shù)，數(shù)值分正負(fù)，正數(shù)表示在此日期之后的日期，負(fù)數(shù)表示在此日期之前的日期；后4個字節(jié)用于存儲從此日零時起所指定的時間經(jīng)過的毫秒數(shù)。
smalldatetime數(shù)據(jù)類型使用4個字節(jié)存儲數(shù)據(jù)。其中前2個字節(jié)存儲從基礎(chǔ)日期1900年1月1日以來的天數(shù)，后兩個字節(jié)存儲此日零時起所指定的時間經(jīng)過的分鐘數(shù)。
smalldatetime數(shù)據(jù)類型與datetime數(shù)據(jù)類型相似，但其日期時間范圍較小，從1900年1月1日到2079年6月6日。此數(shù)據(jù)類型精度較低，只能精確到分鐘，其分鐘個位為根據(jù)秒數(shù)四舍五入的值，即以30秒為界四舍五入。
如果沒有兼容多種數(shù)據(jù)庫這個要求，我會毫不猶豫的使用數(shù)據(jù)庫的 Date 類型。因為如果使用 Java 框架產(chǎn)生代碼，對數(shù)據(jù)庫中定義為 Date 類型的字段，甚至能在頁面上產(chǎn)生出JS的時間選擇框，的確能節(jié)省很多開發(fā)時間。而兼容不同數(shù)據(jù)庫，就希望產(chǎn)品在由一種數(shù)據(jù)庫，遷移到另外一種數(shù)據(jù)庫時，盡可能小的代價，使用了 Date，看來就很困難了。
有一個疑問，不知道目前流行的ORM對這個處理得是不是好？因為工作不怎么涉及這方面，所以不大了解。
在之前的設(shè)計開發(fā)中，因為有支持多種數(shù)據(jù)庫這種需求，所以首先否定了日期時間這樣的類型。
曾經(jīng)使用過毫秒數(shù)（Java 的 System.currentTimeMillis()）這種方式，但是選用這個方式，考慮的不是使用起來是否方便或者數(shù)據(jù)遷移，而是考慮到下面的原因：
Java 取到的毫秒數(shù)是對時間點的一種準(zhǔn)確描述。定義如下：java.lang.System.currentTimeMillis()，它返回從 UTC 1970 年 1 月 1 日午夜開始經(jīng)過的毫秒數(shù)。
我們可以看到，這個定義，保證了這個時間值能夠被后續(xù)設(shè)計開發(fā)的人員正確和準(zhǔn)確的理解，能夠為所有的應(yīng)用正確理解，能夠在所有時區(qū)上正確反映為正常的時間形式。
當(dāng)時的產(chǎn)品設(shè)計是有海外客戶的，所以當(dāng)時的設(shè)計，在數(shù)據(jù)庫里保存的，應(yīng)該是一個“準(zhǔn)確的時間”。例如“20120926080000”實際上并沒有嚴(yán)格的表示出時間，因為北京時間2012年9月26日8點和格林威治時間2012年9月26日8點顯然是不一樣的。
雖然我們都是在一個確切的時區(qū)里，例如中國都是使用東八區(qū)時間，但是需要考慮的是：
有些產(chǎn)品是可能有海外客戶的
產(chǎn)品所運行的機器，時區(qū)的設(shè)置未必都是東八區(qū)。
在這種情況下，如果數(shù)據(jù)庫里的時間不準(zhǔn)確，會給程序運行帶來問題。這種方式最大的缺點在于：
不方便對時間進(jìn)行分組查詢，比如按月統(tǒng)計、按季 統(tǒng)計
DBA在維護(hù)時，不能直觀的根據(jù)返回的行結(jié)果，看到簡單明了的結(jié)果（看到的是毫秒數(shù)）
使用這種方式的特點是犧牲一點易用性和可理解性（不易于維護(hù)和理解），滿足了查詢結(jié)果的直觀性和準(zhǔn)確性要求，同時最大限度考慮運行效率。為了解決這個問題，我設(shè)計了一個輔助的措施，就是建立一個數(shù)據(jù)庫函數(shù)來進(jìn)行時間轉(zhuǎn)換，把毫秒數(shù)的時間轉(zhuǎn)為制定時區(qū)和格式的時間串，DBA 在維護(hù)時可以使用。測試了 Oracle 和 DB2 上，都可以這樣。例如之前的查詢的時候為：
SELECT username,user_addtime from userinfo
這個查詢顯示的是毫秒數(shù)，使用內(nèi)置函數(shù)后寫成：
SELECT username,date2str(user_addtime) from userinfo這樣返回的就是東八區(qū)、預(yù)先定義好格式的字符串了。
在之后的設(shè)計里，還使用過 YYYYMMDDHHmmSST 格式，其中的“T”指時區(qū)，加入時區(qū)，帶來的影響有：
日期時間字段就不能在使用數(shù)值來存儲了，字符串比數(shù)字存儲和檢索的效率都要低。
應(yīng)用程序需要加上額外的處理
帶來的好處是：
便于 DBA 維護(hù)
到什么時候，即便沒有看到數(shù)據(jù)庫設(shè)計文檔，都能看明白并準(zhǔn)確理解數(shù)據(jù)庫中一條信息中，這個字段保存到確切信息
使用這種方式的特點是犧牲一點效率，滿足了查詢結(jié)果的直觀性和準(zhǔn)確性要求。
總結(jié)一下，字段類型的選擇，還是根據(jù)場景的需要來選擇，從功能、效率要求、持續(xù)開發(fā)的要求、維護(hù)的要求幾個方面綜合考慮。
最近熱文閱讀：
1、40 個 SpringBoot 常用注解：讓生產(chǎn)力爆表！ 2、3種常見的數(shù)據(jù)脫敏方案 3、BigDecimal使用不當(dāng)，造成P0事故！ 4、改造BeanUtils，優(yōu)雅實現(xiàn)List數(shù)據(jù)拷貝 5、讓人上癮的新一代開發(fā)神器，徹底告別Controller、Service、Dao等方法 6、SpringBoot 啟動時自動執(zhí)行代碼的幾種方式，還有誰不會？？ 7、延時消息常見實現(xiàn)方案 8、勁爆！Java 通用泛型要來了。。 9、如何寫出讓同事吐血的代碼？ 10、遭棄用的 Docker Desktop 放大招！宣布支持 Linux 關(guān)注公眾號，你想要的Java都在這里