本篇文章是基于MySql5.7的。
概述
??MySql中與日期時(shí)間相關(guān)的共有5種類型，分別是DATE，TIME，DATETIME，TIMESTAMP，YEAR，它們分別對(duì)應(yīng)的關(guān)系如下表所示：
| Data Type | “Zero” Value |
|---|---|
DATE |
'0000-00-00' |
TIME |
'00:00:00' |
DATETIME |
'0000-00-00 00:00:00' |
TIMESTAMP |
'0000-00-00 00:00:00' |
YEAR |
0000 |
Year
在舊版本中，Year表示的年，有兩種類型，分別是year(2)，year(4)，表示形式：yy，yyyy。其中4位的即使我們常用的年。
但在MySql5.7中已經(jīng)不支持yy這中形式了，只支持4位的了，所以即使你輸入兩位，數(shù)據(jù)庫也會(huì)自動(dòng)轉(zhuǎn)為4位。而Mysql中year字段的范圍也改為了1901~2155。轉(zhuǎn)換的范圍是：
Year values in the range 70-99 are converted to 1970-1999.
Year values in the range 00-69 are converted to 2000-2069.
datetime和timestamp
其中，datetime和timestamp這兩種類型都是用于表示YYYY-MM-DD HH:MM:SS 這種年月日時(shí)分秒格式的數(shù)據(jù)，但兩者還是有些許不同之處的。
- 存儲(chǔ)范圍不同：datetime的存儲(chǔ)范圍是
1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999，而timestamp的范圍是1970-01-01 00:00:01.000000到2038-01-19 03:14:07.999999；- datetime存儲(chǔ)與時(shí)區(qū)無關(guān)，而timestamp存儲(chǔ)的是與時(shí)區(qū)有關(guān)，這也是兩者最大的不同。MySql在存儲(chǔ)timestamp時(shí)會(huì)先將時(shí)間轉(zhuǎn)為UTC（世界協(xié)調(diào)時(shí)）進(jìn)行存儲(chǔ)，然后查詢的時(shí)候再從UTC轉(zhuǎn)為當(dāng)前的時(shí)區(qū)進(jìn)行返回。也就是說使用timestamp進(jìn)行存儲(chǔ)的時(shí)間返回的時(shí)候會(huì)隨著數(shù)據(jù)庫的時(shí)區(qū)而發(fā)生改變。而datetime的存儲(chǔ)則與時(shí)區(qū)無關(guān)，數(shù)據(jù)是什么，就存儲(chǔ)什么，也就返回什么。
- datetime適用于記錄數(shù)據(jù)的創(chuàng)建時(shí)間，因?yàn)檫@個(gè)時(shí)間是不會(huì)變的。而timestamp有自動(dòng)修改更新的功能，也就是說，我們對(duì)表里的其他數(shù)據(jù)進(jìn)行修改，timestamp修飾的字段會(huì)自動(dòng)更新為當(dāng)前的時(shí)間，這個(gè)特性稱為自動(dòng)初始化和自動(dòng)更新(Automatic Initialization and Updating)。所以timestamp適用于那種記錄數(shù)據(jù)的最后修改時(shí)間的字段。當(dāng)然，我們也可以設(shè)置timestamp不自動(dòng)更新，通過設(shè)置
explicit_defaults_for_timestamp配置，從OFF設(shè)置為ON 來實(shí)現(xiàn)。- 從MySql5.6.5之后，Automatic Initialization and Updating這種特性不但適用于timestamp，也適用于datetime了。并且以前MySql版本要求同一張表中滿足該特性的timestamp只能有一個(gè)字段，而現(xiàn)在也不再限制數(shù)量了。
下面我們通過一個(gè)簡單的例子來查看一下：
我們以表test_date中的create字段和update字段來進(jìn)行比較，并且我們使用命令行來進(jìn)行操作。
- 查看表結(jié)構(gòu)：
mysql> show create table test_date;
+-----------+-------------------------------------------------------+
| Table | Create Table
+-----------+-------------------------------------------------------+
| test_date | CREATE TABLE `test_date` (
`id` int(11) NOT NULL,
`create` datetime DEFAULT NULL,
`update` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+----------------------------------------------------+
1 row in set (0.00 sec)
- 查看表中數(shù)據(jù)：
mysql> select * from test_date;
+----+---------------------+---------------------+
| id | create | update |
+----+---------------------+---------------------+
| 1 | 2018-02-02 22:38:20 | 2018-02-02 22:38:20 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
看可以看到，create和update兩個(gè)字段的值是一樣的。
- 修改當(dāng)前會(huì)話的時(shí)區(qū)：
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | |
| time_zone | +00:00 |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)
mysql> set time_zone='-3:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_date;
+----+---------------------+---------------------+
| id | create | update |
+----+---------------------+---------------------+
| 1 | 2018-02-02 22:38:20 | 2018-02-02 19:38:20 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
注意兩點(diǎn)：
- 時(shí)區(qū)信息是MySql的系統(tǒng)變量，我們可以通過
show variables like '%time_zone%'來獲取mysql的時(shí)區(qū)信息，默認(rèn)值一般是SYSTEM，即服務(wù)器的時(shí)區(qū)。- 修改當(dāng)前會(huì)話的時(shí)區(qū)只會(huì)影響到當(dāng)前連接，如果再開一個(gè)連接，時(shí)區(qū)將仍是mysql默認(rèn)時(shí)區(qū)。如果要修改整個(gè)mysql的時(shí)區(qū)，可以有多種方式，比如修改my.ini配置等，然后記得重啟即可。
從以上例子，我們可以大致看出datetime與timestamp的不同了。
另外在說一點(diǎn)，MySql也提供了查詢當(dāng)前UTC的日期和時(shí)間，可以通過UTC_TIMESTAMP方法來查詢：
mysql> select utc_timestamp(),utc_timestamp()+0;
+---------------------+-------------------+
| utc_timestamp() | utc_timestamp()+0 |
+---------------------+-------------------+
| 2018-02-05 01:08:03 | 20180205010803 |
+---------------------+-------------------+
1 row in set (0.03 sec)
針對(duì)以上一些特性，看一下官方文檔，順便學(xué)一下英文：
- datetime與timestamp范圍：
??the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'；
- datetime與timestamp存儲(chǔ)：
??MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable
- 新版本之后的一些變化：
??As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table. The following notes first describe automatic initialization and updating for MySQL 5.6.5 and up, then the differences for versions preceding 5.6.5.
如要查看更多，請(qǐng)參考MySql官網(wǎng)文檔5.6版本，5.7版本：
https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html
https://dev.mysql.com/doc/refman/5.7/en/datetime.html
