关于MySQL的时间类型，我简单说两句

文章概览

MySQL支持的时间类型有：DATE、TIME、DATETIME、TIMESTAMP、YEAR。它们的区别，主要在于取值范围的不同。此外，TIMESTAMP、DATETIME 还支持自动初始化（插入记录时）与自动更新（更新记录时）。

下文主要包含几方面内容：

不同时间类型的取值范围
存储、查询时的时区转换
常见的时间类型操作，如设置自动更新、找出最新/旧的记录等；

取值范围

不同时间类型的取值范围如下：

DATETIME：'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
DATE：'1000-01-01' to '9999-12-31'
TIME：'-838:59:59' to '838:59:59'（支持0-6位小数）
TIMESTAMP：'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
YEAR：1901 to 2155

当你给时间类型赋了一个非法的值，MySQL会自动将值转成0。不同时间类型的0值如下：

DATETIME：'0000-00-00 00:00:00'
DATE：'0000-00-00'
TIME：'00:00:00'
TIMESTAMP：'0000-00-00 00:00:00'
YEAR：0000

备注：

DATETIME、TIMESTAMP支持0到6位小数（秒，默认是0）
DATETIME、TIMESTAMP 支持自动初始化到当前时间，或者自动更新到当前时间。

存储与查询之间的时区转换

MySQL在存储、读取时，会对TIMESTAMP进行时区转换（DATETIME不会）

存储时：从当前时区，转成UTC
读取时：将UTC转成当前时区

MySQL Server时区是怎么确认的呢？

MySQL Server的时区设置：服务启动时，默认采用服务器的时区设置。（可通过启动参数修改）
连接的时区设置：针对每次连接，可以设置不同的时区。（默认采用MySQL Server的时区设置，也可以动态修改）

查看当前的timezone设置。

mysql> SHOW VARIABLES LIKE "%time_zone";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

system_time_zone vs time_zone：

system_time_zone：MySQL Server采用的时区设置，默认跟MySQL Server所在的宿主机一致。MySQL Server启动的时候可以通过参数自定义；
time_zone：针对每次客户端连接的时区设置，默认跟system_time_zone保持一致；

UTC与CST:

UTC：Coordinated Universal Time，世界协调时间（近似认为是0时区）。
CST：China Standard Time，中国标准时间，东八区，比协调时间早8个小时，记为UTC+8。

时区误差例子

如前面所说，两次连接，分别进行插入、查询操作，且设置了不同区，那么TIMESTAMP读出来的值，跟插入时不同。下面看具体例子。

首先，创建表tb_time_zone

CREATE TABLE `test`.`tb_time_zone` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `createed_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)  COMMENT '');

然后，将当前时区设置为"+8:00"，并插入一条记录，插入时间为2017-08-17 17:55:54。

mysql> SET time_zone="+8:00";
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test.tb_time_zone (id) VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test.tb_time_zone WHERE id = 1;
+----+---------------------+
| id | createed_at         |
+----+---------------------+
|  1 | 2017-08-17 17:55:54 |
+----+---------------------+
1 row in set (0.00 sec)

切换时区为 +9:00，再查看记录，发现从 17:55:54 变成了 18:55:54。

mysql> SET time_zone="+9:00";
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM test.tb_time_zone WHERE id = 1;
+----+---------------------+
| id | createed_at         |
+----+---------------------+
|  1 | 2017-08-17 18:55:54 |
+----+---------------------+
1 row in set (0.00 sec)

修改默认时区

如果不想每次连接都设置time_zone，可以加到启动的配置文件my.cnf里，如下所示：

[mysqld]
default-time-zone = "+8:00"

重启mysql server后，重新连接并查看时区设置

mysql> SHOW VARIABLES LIKE "time_zone";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +08:00 |
+---------------+--------+
1 row in set (0.01 sec)

不同时间类型的例子

DATETIME

CREATE TABLE `test`.`tb_datetime` (
  `id` INT NOT NULL COMMENT '',
  `birth` DATETIME NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');

INSERT INTO test.tb_datetime (id, birth) VALUES (1, '1988-10-27 09:00:00');

DATE

CREATE TABLE `test`.`tb_date` (
  `id` INT NOT NULL COMMENT '',
  `birthday` DATE NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');

INSERT INTO test.tb_date (id, birthday) VALUES (1, '1988-10-27');

TIME

CREATE TABLE `test`.`tb_time` (
  `id` INT NOT NULL COMMENT '',
  `name` VARCHAR(45) NOT NULL COMMENT '',
  `birth_at` TIME(0) NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');

INSERT INTO test.tb_time (id, name, birth_at) VALUES (1, 'chyingp', '09:00:00');

DATETIME默认值

CREATE TABLE `test`.`tb_datetime_default` (
  `id` INT NOT NULL COMMENT '',
  `caller_name` VARCHAR(45) NOT NULL COMMENT '',
  `call_at` DATETIME NOT NULL COMMENT '' DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)  COMMENT '');

INSERT INTO test.tb_datetime_default (id, caller_name) VALUES (1, 'xiaoming');

mysql> SELECT * FROM test.tb_datetime_default;
+----+-------------+---------------------+
| id | caller_name | call_at             |
+----+-------------+---------------------+
|  1 | xiaoming    | 2017-08-17 01:50:50 |
+----+-------------+---------------------+
1 row in set (0.00 sec)

DATETIME、TIMESTAMP的自动初始化、更新

字段datetime、time_stamp分别被设置为DATETIME、TIMESTAMP类型，且：

新增记录时：自动初始化为CURRENT_TIMESTAMP
更新记录时：自动更新为CURRENT_TIMESTAMP

CREATE TABLE `test`.`new_table` (
  `id` INT NOT NULL COMMENT '',
  `date_time` DATETIME(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time_stamp` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `update_column` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)  COMMENT '');

插入一条记录：

INSERT INTO test.tb_auto_update (id, update_column) VALUES (1, 'hello');

查看刚插入的记录：time_stamp、date_time被设置为记录插入时的时间。

mysql> SELECT * FROM test.tb_auto_update;
+----+---------------------+---------------------+---------------+
| id | date_time           | time_stamp          | update_column |
+----+---------------------+---------------------+---------------+
|  1 | 2017-08-17 16:53:08 | 2017-08-17 16:53:08 | hello         |
+----+---------------------+---------------------+---------------+
1 row in set (0.00 sec)

修改update_column，再查看结果。可以看到，date_time、time_stamp被自动更新为当前时间。

mysql> UPDATE test.tb_auto_update SET update_column="world" WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM test.tb_auto_update;
+----+---------------------+---------------------+---------------+
| id | date_time           | time_stamp          | update_column |
+----+---------------------+---------------------+---------------+
|  1 | 2017-08-17 16:55:34 | 2017-08-17 16:55:34 | world         |
+----+---------------------+---------------------+---------------+
1 row in set (0.00 sec)

常见的时间类型操作

时间比较

首先，创建tb_compare_datetime表

CREATE TABLE `tb_compare_datetime` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `extra` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
)

先后插入3条记录

INSERT INTO test.tb_compare_datetime (extra) VALUES ('a');
INSERT INTO test.tb_compare_datetime (extra) VALUES ('b');
INSERT INTO test.tb_compare_datetime (extra) VALUES ('c');

查看记录

mysql> SELECT * FROM test.tb_compare_datetime;
+----+---------------------+-------+
| id | datetime            | extra |
+----+---------------------+-------+
|  1 | 2017-08-17 17:05:28 | a     |
|  3 | 2017-08-17 17:05:49 | b     |
|  4 | 2017-08-17 17:08:03 | c     |
+----+---------------------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test.tb_compare_datetime WHERE datetime > "2017-08-17 17:06:00";
+----+---------------------+-------+
| id | datetime            | extra |
+----+---------------------+-------+
|  4 | 2017-08-17 17:08:03 | c     |
+----+---------------------+-------+
1 row in set (0.01 sec)

找出最新/最旧的记录

假设记录如下：

mysql> SELECT * FROM test.tb_compare_datetime;
+----+---------------------+-------+
| id | datetime            | extra |
+----+---------------------+-------+
|  1 | 2017-08-17 17:05:28 | a     |
|  3 | 2017-08-17 17:05:49 | b     |
|  4 | 2017-08-17 17:08:03 | c     |
+----+---------------------+-------+
3 rows in set (0.00 sec)

找出最新的那条记录

mysql> SELECT * FROM test.tb_compare_datetime WHERE  datetime = (SELECT MAX(datetime) FROM test.tb_compare_datetime);
+----+---------------------+-------+
| id | datetime            | extra |
+----+---------------------+-------+
|  4 | 2017-08-17 17:08:03 | c     |
+----+---------------------+-------+
1 row in set (0.00 sec)

同理，找出最旧的记录

SELECT * FROM test.tb_compare_datetime WHERE  datetime = (SELECT MIN(datetime) FROM test.tb_compare_datetime);

查看当前时间

mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2017-08-17 00:04:12 |
+---------------------+
1 row in set (0.00 sec)