为什么MySQL 使用timestamp可以无视时区问题.
作者:施国鹏 时间:2024-01-13 08:30:05
之前一直有过疑惑为什么MySQL
数据库存timestamp
可以无视时区问题.
在业务中也是一直使用Laravel
框架,内置的Migration
也是使用的timestamp
类型字段, 也没太关心.
开始
查看当前数据库时区
mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | +08:00 |
+------------------+--------+
2 rows in set (0.30 sec)
查看表结构
mysql> desc timestamp_test;
+--------------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| created_time | datetime | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
+--------------+-----------+------+-----+---------+----------------+
3 rows in set (0.26 sec)
插入数据
mysql> insert into timestamp_test(created_time, created_at) values('2020-12-09 08:00:00', '2020-12-09 08:00:00');
Query OK, 1 row affected (0.22 sec)
mysql> select * from timestamp_test;
+----+---------------------+---------------------+
| id | created_time | created_at |
+----+---------------------+---------------------+
| 1 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 |
+----+---------------------+---------------------+
1 row in set (0.06 sec)
这个时间看起来是没问题的, 那么我们尝试修改时区再插入数据
mysql> SET time_zone = "+00:00";
Query OK, 0 rows affected (0.03 sec)
mysql> insert into timestamp_test(created_time, created_at) values('2020-12-09 08:00:00', '2020-12-09 08:00:00');
Query OK, 1 row affected (0.03 sec)
mysql> SET time_zone = "+08:00";
Query OK, 0 rows affected (0.04 sec)
这时候再查看数据, 两条插入的SQL
是一样的,但是发现查询的结果是不一样的
这两条数据created_at
的相差正好是时区的时间差
mysql> select * from timestamp_test;
+----+---------------------+---------------------+
| id | created_time | created_at |
+----+---------------------+---------------------+
| 1 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 |
| 2 | 2020-12-09 08:00:00 | 2020-12-09 16:00:00 |
+----+---------------------+---------------------+
2 rows in set (0.06 sec)
再看一下实际存储的时间戳, 然后我们变化时区, 发现字段时间变化了,但是原始的时间戳数据没变
mysql> select *, unix_timestamp(created_at) from timestamp_test;
+----+---------------------+---------------------+----------------------------+
| id | created_time | created_at | unix_timestamp(created_at) |
+----+---------------------+---------------------+----------------------------+
| 1 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 | 1607472000 |
| 2 | 2020-12-09 08:00:00 | 2020-12-09 16:00:00 | 1607500800 |
+----+---------------------+---------------------+----------------------------+
2 rows in set (0.06 sec)
mysql> SET time_zone = "+00:00";
Query OK, 0 rows affected (0.09 sec)
mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | +00:00 |
+------------------+--------+
2 rows in set (0.08 sec)
mysql> select *, unix_timestamp(created_at) from timestamp_test;
+----+---------------------+---------------------+----------------------------+
| id | created_time | created_at | unix_timestamp(created_at) |
+----+---------------------+---------------------+----------------------------+
| 1 | 2020-12-09 08:00:00 | 2020-12-09 00:00:00 | 1607472000 |
| 2 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 | 1607500800 |
+----+---------------------+---------------------+----------------------------+
2 rows in set (0.18 sec)
因为这一切是MySQL
隐式的帮我们转换了, 让我们不用关心时区的问题
就是数据库实际上会保存 UTC 时间戳,写入的时候先按 Session 时区转成 UTC 时间,读出的时候再按 Session 时区转成当前时区的时间,这些转换都是透明的
假如我们在正八区存储了
2020-12-09 08:00:00
时间的一条数据我们在正八区取出这一条数据, 时间依然是
2020-12-09 08:00:00
这时候我们有一台在零时区的服务器,连接
MySQL
,并且把当前连接的时区设置为+00:00
,再去查数据库这条记录,查到的数据是:2020-12-09 00:00:00
, 正好对应零时区的时间,这样子我们就不用考虑时区的问题.
来源:http://www.shiguopeng.cn/archives/473
标签:MySQL,timestamp,时区
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python Pygame实战之超级炸弹人游戏的实现
2023-07-24 00:56:11
![](https://img.aspxhome.com/file/2023/3/120353_0s.jpg)
基于Vue实现图书管理功能
2024-04-27 16:16:59
![](https://img.aspxhome.com/file/2023/1/132931_0s.jpg)
对numpy和pandas中数组的合并和拆分详解
2021-04-23 22:51:50
python实现水印图片功能
2021-07-04 00:45:45
![](https://img.aspxhome.com/file/2023/3/96463_0s.jpg)
js获取select选中的option的text示例代码
2024-04-19 09:58:48
Python map和reduce函数用法示例
2022-06-21 09:02:25
IE8的一些CSS hack
2008-03-17 13:08:00
![](https://img.aspxhome.com/file/UploadPic/20083/17/2008317132156169s.jpg)
tkinter使用js的canvas实现渐变色
2024-02-24 20:46:47
![](https://img.aspxhome.com/file/2023/4/56394_0s.png)
Pytorch中的图像增广transforms类和预处理方法
2022-09-10 22:21:06
Python实现JSON反序列化类对象的示例
2023-09-03 19:29:51
鼠标放在图片上显示大图的JS代码
2024-04-28 10:20:08
ASP开发10条经验总结
2007-09-30 13:36:00
[JS]用 或 || 来兼容FireFox
2013-06-26 14:50:47
SQL SERVER 2005数据库还原的方法
2024-01-26 12:28:57
![](https://img.aspxhome.com/file/2023/8/102068_0s.jpg)
Golang验证器之validator是使用详解
2024-01-29 23:40:28
![](https://img.aspxhome.com/file/2023/9/107319_0s.png)
Vue实现聊天界面
2024-04-28 09:23:57
![](https://img.aspxhome.com/file/2023/7/133037_0s.jpg)
vue实现在v-html的html字符串中绑定事件
2023-07-02 16:44:08
go格式“占位符”输入输出 类似python的input
2021-04-23 21:32:18
Python进行特征提取的示例代码
2021-04-07 15:59:12
利用python实现后端写网页(flask框架)
2022-06-22 04:20:50
![](https://img.aspxhome.com/file/2023/2/89272_0s.png)