MySQL日期、時間相關內容

歡迎和大家交流技術相關問題：

MySQl中有多種表示日期和時間的資料類型。 其中YEAR表示年份， DATE表示日期， TIME表示時間， DATETIME和TIMESTAMP表示日期和時間。 它們的對比如下：

YEAR ， 位元組數為1， 取值範圍為“1901——2155”DATE， 位元組數為4， 取值範圍為“1000-01-01——9999-12-31”TIME， 位元組數為3， 取值範圍為“-838:59:59——838:59:59”DATETIME， 位元組數為8， 取值範圍為“1000-01-01 00:00:00——9999-12-31 23:59:59”TIMESTAMP， 位元組數為4， 取值範圍為“19700101080001——20380119111407”

當插入值超出有效取值範圍時， 系統會報錯， 並將零值插入到資料庫中。

YEAR類型

給YEAR類型賦值可以有三種方法。 第一種是直接插入4位元字串或者4位元數位。 第二種是插入2位元字串， 這種情況下如果插入‘00’~‘69’， 則相當於插入2000~2069；如果插入‘70’~‘99’， 則相當於插入1970~1999。

第二種情況下插入的如果是‘0’， 則與插入‘00’效果相同， 都是表示2000年。 第三種是插入2位元數字， 它與第二種（插入兩位元字串）不同之處僅在於：如果插入的是一位元數字0， 則表示的是0000， 而不是2000年。 所以在給YEAR類型賦值時， 一定要分清0和‘0’， 雖然兩者相差個引號， 但實際效果確實相差了2000年。

TIME類型

TIME類型表示為“時：分：秒”， 儘管小時範圍一般是0~23， 但是為了表示某些特殊時間間隔， MySQL將TIME的小時範圍擴發了， 而且支持負值。 對TIME類型賦值， 標準格式是‘HH：MM：SS’， 但不一定非要是這種格式。 如果插入的是‘D HH：MM：SS’格式， 則類似插入了‘（D*24+HH）：MM：SS’。 比如插入‘2 23:50:50’， 相當於插入了‘71:50:50’。 如果插入的是‘HH：MM’或‘SS’格式， 則效果是其他未被表示位的值賦為零值。 比如插入‘30’， 相當於插入了‘00:00:30’；如果插入‘11:25’， 相當於插入了‘11:25:00’。

另外也可以插入‘D HH’和‘D HH：MM’， 效果按上面的例子可以推理出來了吧。 在MySQl中， 對於'HHMMSS'格式， 系統能夠自動轉化為標準格式。 如果我們想插入當前系統的時間， 則可以插入CURRENT_TIME或者NOW。 TIME類型只占3個位元組， 如果只是存儲時間資料， 它最合適了。

DATE類型

MySQL是以YYYY-MM-DD格式來顯示DATE類型的值， 插入資料時， 資料可以保持這種格式。 另外， MySQL還支援一些不嚴格的語法格式， 分隔符號“-”可以用“@”、“.”等眾多富豪來替代。 在插入資料時， 也可以使用“YY-MM-DD”格式， YY轉化成對應的年份的規則與YEAR類型類似。 如果我們想插入當前系統的時間， 則可以插入CURRENT_DATE或者NOW。

DATETIME

標準格式為“YYYY-MM-DD HH：MM：SS”， 具體賦值方法與上面各種類型的方法相似。

DATETIME列可以設置為多個， 默認可為null， 可以手動設置其值。 DATETIME列不可設定預設值， 但是可以變相的設定預設值， 比如通過觸發器、或者在插入資料時候， 將DATETIME欄位值設置為now， 這樣可以做到了， 尤其是後者， 在程式開發中常常用到。

一般建表時候， 創建時間用datetime， 更新時間用timestamp。

CREATE TABLE user ( id bigint(20) NOT NULL AUTO_INCREMENT, name varchar(20) CHARACTER SET gbk NOT NULL, sex tinyint(1) DEFAULT ‘1’, state smallint(2) DEFAULT ‘1’, createtime datetime NOT NULL, updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1TIMESTAMP

TIMESTAMP的取值範圍比較小， 沒有DATETIME的取值範圍大， 因此輸入值時一定要保證在TIMESTAMP的範圍之內。 它的插入也與插入其他日期和時間資料類型類似。

那麼TIMESTAMP類型如何插入當前時間？第一， 可以使用CURRENT_TIMESTAMP；第二， 輸入NULL， 系統自動輸入當前的TIMESTAMP；第三， 無任何輸入， 系統自動輸入當前的TIMESTAMP。

另外有很特殊的一點：TIMESTAMP的數值是與時區相關。

TIMESTAMP列必須有預設值， 預設值可以為“0000-00-00 00:00:00”， 但不能為null。 TIMESTAMP列不可以設置值， 只能由資料庫自動去修改。 一個表可以存在多個TIMESTAMP列， 但只有一個列會根據資料更新而改變為資料庫系統當前值。 因此， 一個表中有多個TIMESTAMP列是沒有意義， 實際上一個表只設定一個TIMESTAMP列。 TIMESTAMP列的預設值是CURRENT_TIMESTAMP常量值。 當紀錄資料發生變化的時候， TIMESTAMP列會自動將其值設定為CURRENT_TIMESTAMP。 TIMESTAMP列創建後的格式如下：
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
這個語句含義， 欄位的預設值是CURRENT_TIMESTAMP， 當紀錄更新時候， 自動將欄位的值設置為CURRENT_TIMESTAMP。 另外， 下面的定義從語法角度是對的， 但是沒有意義， 因為該欄位的值不可更改， 永遠只能為預設值。
timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,日期相關操作示例select STR_TO_DATE('2010-03-03 16:41:16', '%Y-%m-%d %H:%i:%s'); -- 字串轉日期select DATE_FORMAT('2010-03-03 16:41:16', '%Y-%m-%d %H:%i:%s'); -- 日期轉字串select TIMESTAMP('2010-03-03 16:41:16'); select DATE('2010-03-03 16:41:16'); select YEAR('2010-03-03 16:41:16'); select MONTH('2010-03-03 16:41:16'); select DAY('2010-03-03 16:41:16'); select TIME('2010-03-03 16:41:16'); select CURTIME; select CURDATE; select CURRENT_DATE; select CURRENT_TIME; select CURRENT_TIMESTAMP;SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);-- 日期比較and update_time > '2010-03-02 16:48:41' and update_time <= '2010-03-03 16:51:58'