MySQL支持的时间类型有:DATE、TIME、DATETIME、TIMESTAMP、YEAR。它们的区别,主要在于取值范围的不同。此外,TIMESTAMP、DATETIME 还支持自动初始化(插入记录时)与自动更新(更新记录时)。
下文主要包含几方面内容:
不同时间类型的取值范围如下:
当你给时间类型赋了一个非法的值,MySQL会自动将值转成0。不同时间类型的0值如下:
备注:
MySQL在存储、读取时,会对TIMESTAMP进行时区转换(DATETIME不会)
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
:
如前面所说,两次连接,分别进行插入、查询操作,且设置了不同区,那么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)
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');
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');
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');
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、time_stamp分别被设置为DATETIME、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)
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_system_time_zone