背景: 最近在工作中，處理了幾個(gè)線上的問題，過程中涉及到了mysql中timestamp datetime這個(gè)兩個(gè)字段的處理，記得當(dāng)看到其中某個(gè)服務(wù)函數(shù)的參數(shù)對(duì)象中涉及到時(shí)間相關(guān)的字段時(shí)，當(dāng)時(shí)內(nèi)心感覺到有些惶恐，感覺寫sql無從下手，不知道時(shí)間相關(guān)的字段該如何來表示。因此這里記錄mysql中時(shí)間相關(guān)字段的處理方法。
mysql中TIME, DATE, DATETIME, and TIMESTAMP字段值的多種格式
先記錄這幾種時(shí)間相關(guān)字段在sql語句和java代碼中的實(shí)際使用方式，稍后在對(duì)比它們的異同點(diǎn)。
字符串和數(shù)值類型的格式
DATETIME and TIMESTAMP
DATETIME 和 TIMESTAMP在具體使用時(shí)(sql、java)有字符串和數(shù)值兩種表現(xiàn)形式，詳細(xì)看下文中的例子。
- 字符串形式：'YYYY-MM-DD HH:MM:SS' 和 'YY-MM-DD HH:MM:SS'這兩種表現(xiàn)形式，mysql支持任何標(biāo)點(diǎn)作為字符串格式中的分隔符，比如：'2012-12-31 11:30:45','2012/12/31 11 * 30 * 45', '2012 @ 12@31 11 ^ 30 ^ 45'在mysql中最終表現(xiàn)的效果一樣
- 字符串格式也可以不使用分隔符，像'YYYYMMDDHHMMSS' 和 'YYMMDDHHMMSS'，比如，
'20070523091528' and '070523091528' 等同于 '2007-05-23 09:15:28'這種帶分隔符的表現(xiàn)形式，但是mysql會(huì)將'071122129015' 這種數(shù)據(jù)當(dāng)作錯(cuò)誤數(shù)據(jù)。 - 這兩種字段的date和time部分之間的分隔符可以用
T來替換空格 - 數(shù)值形式： YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS，mysql會(huì)自動(dòng)的讀取這種數(shù)值類型的時(shí)間數(shù)據(jù)，比如：19830905132800 和 830905132800 等同于 '1983-09-05 13:28:00'.
sql實(shí)際測(cè)試?yán)樱?/em>
#1 建一張測(cè)試表，僅僅包含本文中涉及到的4種類型的字段（這里先忽略前兩個(gè)字段，主要是驗(yàn)證后兩個(gè)字段），在下面的sql實(shí)例中，年月日時(shí)分秒之間可以用上面介紹的任何標(biāo)點(diǎn)符號(hào)進(jìn)行代替。
CREATE TABLE `time_relate_test` (
`c1` date,
`c2` time ,
`c3` datetime ,
`c4` TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
## 以字符格式的數(shù)據(jù)進(jìn)行字段的插入
insert into time_relate_test
values('2018-09-18', '17:51:04', '2018-09-18 17:51:04', '2018-09-18 17:51:04')
insert into time_relate_test
values('18-09-18', '17:51:04', '18-09-18T17:51:04', '18-09-18T17:51:04')
## 以數(shù)值格式的數(shù)據(jù)進(jìn)行字段的插入，這里
insert into time_relate_test
values(19980908, 175104, 19980908175104, 19980908175104)
insert into time_relate_test
values(980908, 175104, 980908175104, 980908175104)
----------
# 進(jìn)行查詢（重點(diǎn)），由于之前很少寫sql，并且?guī)缀鯖]有寫過時(shí)間相關(guān)字段的sql，因此這次在解決線上case的過程中，
剛開始感覺到恐懼的絕大部分原因是由此引起的。
## 這里以timestamp字段舉例，由于上面介紹了有字符串和數(shù)值兩種形式，因此這里直接寫sql從數(shù)據(jù)庫中獲取數(shù)據(jù)
select *
from time_relate_test
where c4 > 20000101010101
select *
from time_relate_test
where c4 > 000101010101
select *
from time_relate_test
where c4 > '00-01-01 01:01:01'
select *
from time_relate_test
where c4 > '2000-01-01 01:01:01'
Java代碼中測(cè)試樣例：
todo
在mysql 5.6.4版本之后，DATETIME和TIMESTAMP字段的精度可以精確到微秒，比如：'2010-12-10 14:12:09.019473'，小數(shù)點(diǎn)之后就是精確到微秒的數(shù)據(jù)，由于當(dāng)前工作很大可能不會(huì)涉及到微秒的精度，因此不對(duì)其作過多介紹，具體可參考官方文檔。
以上兩個(gè)時(shí)間相關(guān)字段的具體數(shù)值的年份中可以不指定世紀(jì)，mysql中處理的邏輯如下：
- 年份在70-99就轉(zhuǎn)化為1970-1999.
- 年份在00-69就轉(zhuǎn)化為2000-2069.
DATE and TIME
DATE加TIME字段可以看作DATETIME字段的組成部分，因此上面介紹的TIMESTAMP和DATE的規(guī)則其實(shí)都適用于DATE和TIME，
同理TIME字段可以有小數(shù)部分。具體的sql實(shí)例可以參考上面中的例子。
- 字符串形式：'YYYY-MM-DD'/'YY-MM-DD'(DATE)和'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', or 'SS'(TIME)
- 數(shù)值形式： YYYYMMDD or YYMMDD(DATE)和HHMMSS(TIME)。
對(duì)于字符串形式的時(shí)間值，像月份、日、時(shí)、分、秒等數(shù)據(jù)，如果可以用一位來表示，那就沒必要用兩位(填充0的方式)，比如：
'2015-6-9'和'2015-06-09'一樣，'2015-10-30 1:2:3'和'2015-10-30 01:02:03'一樣。
對(duì)于數(shù)值型的時(shí)間值，通常他們應(yīng)該是6, 8, 12, or 14位等長(zhǎng)度，如果是8 或者 14位的話，那么此數(shù)據(jù)就代表YYYYMMDD or YYYYMMDDHHMMSS，如果是6 or 12位，那么就是YYMMDD or YYMMDDHHMMSS這樣的形式，也就是年份只用了兩位來表示。如果數(shù)值型時(shí)間值不是上述的這些位數(shù)的話，比標(biāo)準(zhǔn)位數(shù)少就會(huì)在左端填充0進(jìn)行對(duì)齊，如果位數(shù)多的話，mysql會(huì)報(bào)錯(cuò)。
如果數(shù)值型的數(shù)值8或者14位的話，mysql默認(rèn)將前四位字符當(dāng)作年份，否則的話mysql讀取數(shù)值型的數(shù)值順序是依次讀取year, month, day, hour, minute, and second 。因此，我們的數(shù)據(jù)不應(yīng)該少于6位，否則就會(huì)導(dǎo)致插入報(bào)錯(cuò)，比如用'9903'，來寫插入或者查詢，mysql視其為0值。
DATETIME, and TIMESTAMP 異同點(diǎn)
DATETIME包含DATE和TIME這兩個(gè)部分，它表示的時(shí)間范圍'1000-01-01 00:00:00' to '9999-12-31 23:59:59'，而TIMESTAMP表示的時(shí)間范圍是'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC。同時(shí)TIMESTAMP的數(shù)據(jù)在存儲(chǔ)時(shí)，會(huì)轉(zhuǎn)換為UTC（世界統(tǒng)一時(shí)間），再取出的時(shí)候又會(huì)轉(zhuǎn)換成當(dāng)前時(shí)區(qū)的時(shí)間。
結(jié)論:
當(dāng)遇到未知的問題時(shí)，靜下心來，從問題的源頭找原因。