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類型表示為「時：分：秒」，儘管小時範圍一般是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個位元組，如果只是存儲時間數據，它最合適了。

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

標準格式為「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=latin1

TIMESTAMP

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"

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！