为什么MySQL使用timestamp可以无视时区问题?
在MySQL中,使用timestamp类型进行日期和时间的存储,它是一种与时区无关的数据类型。无论你是哪个时区,时间都会以相同的方式存储在timestamp类型字段中。下面分为以下几个方面进行讲解。
- timestamp存储的时间是UTC(协调世界时)
如下面的代码块所示,我们可以使用NOW()函数获取当前时间并插入到一个timestamp类型的字段中。
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `user` (`name`) VALUES ('John');
查询该记录的created_at字段,返回的时间与查询服务器的时区无关,在MySQL内部都是UTC时间。
SELECT `id`, `name`, `created_at`
FROM user
WHERE name = 'John';
返回结果如下:
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 1 | John | 2021-08-31 12:34:56 |
+----+------+---------------------+
- MySQL会自动将本地时间转换为UTC时间进行存储
在MySQL中,无论你在哪个时区将本地时间存储到timestamp类型的字段中,MySQL都会将本地时间转换为UTC时间,然后存储到该字段中。也就是说,时间的存储方式与时区无关,只与UTC有关。下面的代码演示了这一点。
例如在中国的时区设置为CST(中国标准时间),如果你在上海服务器设置一个时间并存储到timestamp类型的字段中,如下所示。
SET time_zone = '+8:00';
SELECT NOW();
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `user` (`name`, `created_at`) VALUES ('John', '2021-08-31 12:34:56');
程序在上海服务器的时区下运行,现在我们要查询北京的时间,将时区设置为东八区,如下所示。
SET time_zone = '+8:00';
SELECT `id`, `name`, `created_at`
FROM user
WHERE name = 'John';
结果如下,可以看到存储到timestamp类型的字段中的时间会被自动转换为UTC时间。
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 1 | John | 2021-08-31 04:34:56 |
+----+------+---------------------+
- MySQL会将timestamp类型的字段转换为本地时间进行显示
当我们在查询timestamp类型字段时,MySQL会自动将该字段的UTC时间转为当前的时区,然后显示在客户端上。这样我们在使用timestamp类型存储时间时,不需要考虑到时区的问题。下面的代码演示了这一点。
例如在中国的时区设置为CST(中国标准时间),我们将一个timestamp类型的字段中存储了UTC时间。查询该记录时,MySQL会将存储的UTC时间自动转为本地时间进行显示,如果你在上海查询,如下所示。
SET time_zone = '+8:00';
SELECT `id`, `name`, `created_at`
FROM user
WHERE name = 'John';
结果如下,可以看到该条记录的时间已经自动转换为中国标准时间(CST),时间显示时区与查询时设置的时区相同。
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 1 | John | 2021-08-31 12:34:56 |
+----+------+---------------------+
- 示例1
我们在数据库中插入一个时间,并将本地的时区设置为东八区,然后查询该时间,并将时区设置为美国洛杉矶的时区。代码如下:
SET time_zone = '+8:00';
SELECT NOW();
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `user` (`name`, `created_at`) VALUES ('Lucy', '2021-09-01 12:34:56');
查询语句:
SET time_zone = '-7:00';
SELECT `id`, `name`, `created_at`
FROM user
WHERE name = 'Lucy';
结果如下,我们查询时的时区是美国洛杉矶的时区,但是查询结果中的时间已经自动转换为美国洛杉矶的本地时间。
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 1 | Lucy | 2021-09-01 05:34:56 |
+----+------+---------------------+
- 示例2
我们在数据库中插入一个时间,并将本地的时区设置为美国洛杉矶的时区,然后查询该时间,并将时区设置为中国上海的时区。代码如下:
SET time_zone = '-7:00';
SELECT NOW();
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `user` (`name`, `created_at`) VALUES ('Lucy', '2021-09-01 12:34:56');
查询语句:
SET time_zone = '+8:00';
SELECT `id`, `name`, `created_at`
FROM user
WHERE name = 'Lucy';
结果如下,我们查询时的时区是中国上海的时区,但是查询结果中的时间已经自动转换为中国上海的本地时间。
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 1 | Lucy | 2021-09-01 03:34:56 |
+----+------+---------------------+
综上所述,MySQL使用timestamp可以无视时区问题,主要是因为它存储的是UTC时间,并且自动将本地时间转换为UTC时间进行存储,同时在查询时会将UTC时间转换为本地时间进行显示,这种机制可以让我们在使用timestamp存储时间时,无需考虑时区的问题。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:为什么MySQL 使用timestamp可以无视时区问题. - Python技术站