时间戳
时间不分东西南北、在地球的每一个角落,每一个国家都是一样的。
在中国时间过去5秒钟,美国同样也是过去了5秒钟,在地球的任何一个角度都是过去了5秒钟,时间戳就是相对于格林威治时间1970年01月01日00时00分00秒起至现在过去的总秒数,这个总秒数在世界上的任何地方都是相同,也就是说时间戳在世界上的任何地方都是相同的,时间戳没有时区的概念。
时间戳强调的是过去时,相对1970年01月01日00时00分00秒起过去了多长时间。
时区
我们知道了在任何地方时间戳都是相同的。当我们问现在是什么时候,我们总不能说相对于1970年01月01日00时00分00秒过去了n秒,太模糊了,于是就有了时间的定义,我们可以说现在是2020年03月15号23点53分,方便我们记忆和理解。
时间和实践戳想比较,时间强调的是现在时,现在是何年何月何日,何时何分何妙,而时间戳强调的是过去时,过去了多少秒。
通常我们将每天太阳在最高点的时候定义为12点,而由于地球的自转,导致每个地方太阳出现在最高点的时间又不尽相同,为了统一时间(太阳最高点为12点)于是就出现了时区的概念。
我们中国处于东+8区,美国处于西-5区
最后总结一个表达式:实践戳+时区=当前时间
mysql的时区
查看mysql的时区
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
可以看到Variable_name有俩个变量,system_time_zone是指系统默认的时区,time_zone指当前时区。
可以看到系统默认时区为CST,而当前时区是system,也就是当前时区为系统默认时区,也就是当前时区为CST。
那么CST时区是什么呢?
修改mysql的时区
有俩种方式
命令行修改
set global time_zone = '+8:00'; ##修改mysql全局时区为北京时间,即我们所在的东8区
set time_zone = '+8:00'; ##修改当前会话时区
flush privileges; #立即生效
配置文件my.conf修改
##在[mysqld]区域中加上
default-time_zone = '+8:00'
## 重启mysql
systemctl restart mysql
连接mysql客户端的时区
mysql有时区,连接mysql的客户端同样也有时区的问题,例如php,java,我们以php为例介绍
查看php当前时区
echo date_default_timezone_get()
在中国默认的时区是RPC,也就是+8:00。
修改php时区
date_default_timezone_set('Europe/Paris');
php可用的时区,请看这篇文章。
操作系统时区
操作系统也是有时区的,输入date -R查看操作系统时区。看到后面是+0800,也就是代表北京时间。
[root@m1 ~]# date -R
Tue, 17 Mar 2020 11:41:46 +0800
操作系统时区影响的是依附操作系统获取时间的操作,例如在shell脚本中获取时间-time=$(date "+%Y%m%d-%H%M%S")。
还有我们最常用的crontab定时任务,crontab中的时分秒肯定是依附于操作系统时区的。
以下俩点需要注意系统时区问题。
datetime和timestamp的区别
占用空间
类型
占据字节
表现形式
datetime
8字节
yyyy-mm-dd hh:mm:ss
timestamp
4字节
yyyy-mm-dd hh:mm:ss
表示范围
由于datetime和timestamp占据的字节不一样,以至于表示的存储范围也肯定不一样。
类型
表现范围
datetime
1000-01-01 00:00:00.000000 - 9999-12-31 23:59:59.999999
timestamp
1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999
存储形式
datetime类型的字段值写入的是什么,存储的就是什么,与时区没有关系。比如写入的是2020-03-10 08:09:30,存储的也是2020-03-10 08:09:30。
timestamp类型的字段值本质存储的是时间戳。比如写入的是2020-03-10 08:09:30,且mysql时区为+8,所以存储的是UNIX_TIMESTAMP(2020-03-10 00:09:30)的值,与时区有关。
表现形式
datetime和timestamp类型的字段,表现的都是yyyy-mm-dd hh:mm:ss,即用户从数据库获取到的数据都是yyyy-mm-dd hh:mm:ss类型的,不会因为timestamp本质存储的是时间戳,而用户获取的就是时间戳,他俩的表现形式是一样的。
timestamp类型的表现形式过程:FROM_UNIXTIME(value)+时区=时间,最终把时间展示出来。
写入形式
datetime和timestamp的写入形式都是一样的,都必须是时间格式的,不能因为timestamp本质存储的是时间戳,然后我们就可以直接写入时间戳。
timestamp类型的写入形式过程:UNIX_TIMESTAMP(时间-时区)=时间戳,然后把该时间戳存起来。
timestamp是不能直接写入时间戳的,实验如下,created_time是timestamp格式的。
INSERT INTO users(name,created_time) values('ff',1584417730);
1054 - Unknown column 'created_time' in 'field list'
mysql> INSERT INTO users(name,created_at) values('ff','2020-03-10');
Query OK, 1 row affected (0.01 sec)
时区
通过上面的分析我们知道了
类型
时区影响
datetime
没有影响
timestamp
有影响
通过上面的分析,我们知道了mysql的时区大概作用于以下俩点
1. timestamp类型的数据,mysql实际存储的是时间戳,而表现形式却是时间,时间戳转化为时间肯定需要时区的转化。时间戳+时区=时间。
2. mysql中的函数FROM_UNIXTIME()(把时间戳转化为时间)和UNIX_TIMESTANMP()(把时间转化为时间戳)也会根据mysql时区进行的转换
mysql存储时间时到底选择datetime,timestamp还是int类型?
datetime和timestamp比大小
datetime类型字段
datetime类型字段存储的值的格式为时间格式,所以按照时间格式来比较大小即可。
比如获取2020-03-01以后新注册的用户
select * from users where created_at > `2020-03-01`
比如获取2020年以后新注册的用户
select * from users where created_at >= `2020`
### 或者
select * from users where year(created_at) > '2020'
timestamp类型字段
timestamp类型的字段对外表现形式和datetime类型是完全一致,所以比较时间大小和datetime完全一致。
可能有的人认为timestamp存储形式是时间戳,那么能不能直接拿时间戳进行比较呢?比如如下sql,发现筛选的数据不对,所以timestamp类型的字段不能直接拿时间戳进行比较的
select * from users where created_at > 21313131
一次线上迁库发生的事故
我们的数据库是使用timestamp类型来存储时间的,当前的数据库的时区为+8:00。
有一次我们需要迁库,迁移的那个库没太注意时区,迁移过去之后发现所有用户的每天获取的积分都不对了,获取积分的时间都少了8个小时。
最后反应过来可能是新的数据库的时区与老数据库不一致,查看后发现新数据库的时区果然不是+8:00,时区修改完之后就和之前的时间对上了。
所以存储时间的类型是很重要的,选择时需要考虑清楚。
总结
如果数据库中有timestamp类型的字段,mysql数据库不管是迁库,还是集群,都一定要保证时区的相同。如果mysql集群中的数据库时区不一致,timestamp的字段将会造成数据不一致的情况发生。在迁移库或者搭建集群时一定检查时区,保证时区的相同。
中国时区默认是+8,所以不管是单节点mysql,还是mysql集群,我们第一件事就是应该将当前时区time_zone设置为+8:00。
你可能会问连接mysql的客户端的时区(php)与mysql时区不一致会有影响吗?
首先这个问题肯定是针对timestamp类型的,datetime类型没有这个问题。
其次我们要知道timestamp类型的写入形式也是时间格式,也就是说,在php客户端写入数据库时,timestamp字段对应的值必须是datetime类型的。
就算php客户端和mysql时区不一致,也肯定不会出现以下这种情况:
php写入的是2020-02-10 03:10:20,而数据库里面写入的是2020-02-10 11:10:20,因为timestamp的写入形式就是时间格式。简单理解就是php的
如果发现数据库中的时间相比于now少了或者多了几个小时,此时肯定是php客户端时区的问题,与mysql时区没关系,理解了下面的俩个过程,肯定就理解了上面所说的了。
timestamp类型的表现形式过程:FROM_UNIXTIME(value)+时区=时间,最终把时间展示出来,存储格式才是时间戳。
timestamp类型的写入形式过程:UNIX_TIMESTAMP(php写入时间-时区)=时间戳,然后把该时间戳存起来。
其实能够发现,只要保证php的时区正确,mysql存入的时间就肯定正确,与mysql的时区并没有关系。mysql时区问题只有在迁移库或者在集群中才会体现。