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

2020-06-28 00:28:10 浏览数 (1)

文章概览

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

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

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

取值范围

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

  • 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

备注：

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

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

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

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

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

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

查看当前的timezone设置。

代码语言：javascript复制
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

代码语言：javascript复制
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。

代码语言：javascript复制
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。

代码语言：javascript复制
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里，如下所示：

代码语言：javascript复制
[mysqld]
default-time-zone = " 8:00"

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

代码语言：javascript复制
mysql> SHOW VARIABLES LIKE "time_zone";
 --------------- -------- 
| Variable_name | Value  |
 --------------- -------- 
| time_zone     |  08:00 |
 --------------- -------- 
1 row in set (0.01 sec)

不同时间类型的例子

DATETIME

代码语言：javascript复制
CREATE TABLE `test`.`tb_datetime` (
  `id` INT NOT NULL COMMENT '',
  `birth` DATETIME NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');
代码语言：javascript复制
INSERT INTO test.tb_datetime (id, birth) VALUES (1, '1988-10-27 09:00:00');

DATE

代码语言：javascript复制
CREATE TABLE `test`.`tb_date` (
  `id` INT NOT NULL COMMENT '',
  `birthday` DATE NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');
代码语言：javascript复制
INSERT INTO test.tb_date (id, birthday) VALUES (1, '1988-10-27');

TIME

代码语言：javascript复制
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 '');
代码语言：javascript复制
INSERT INTO test.tb_time (id, name, birth_at) VALUES (1, 'chyingp', '09:00:00');

DATETIME默认值

代码语言：javascript复制
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 '');
代码语言：javascript复制
INSERT INTO test.tb_datetime_default (id, caller_name) VALUES (1, 'xiaoming');
代码语言：javascript复制
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类型，且：

  1. 新增记录时：自动初始化为CURRENT_TIMESTAMP
  2. 更新记录时：自动更新为CURRENT_TIMESTAMP
代码语言：javascript复制
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 '');

插入一条记录：

代码语言：javascript复制
INSERT INTO test.tb_auto_update (id, update_column) VALUES (1, 'hello');

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

代码语言：javascript复制
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被自动更新为当前时间。

代码语言：javascript复制
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表

代码语言：javascript复制
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条记录

代码语言：javascript复制
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');

查看记录

代码语言：javascript复制
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)
代码语言：javascript复制
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)

找出最新/最旧的记录

假设记录如下：

代码语言：javascript复制
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)

找出最新的那条记录

代码语言：javascript复制
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)

同理，找出最旧的记录

代码语言：javascript复制
SELECT * FROM test.tb_compare_datetime WHERE  datetime = (SELECT MIN(datetime) FROM test.tb_compare_datetime);

查看当前时间

代码语言：javascript复制
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

云数据库SQLServer sqlserver 数据库 sql python

0 人点赞