mysql中的 datetime与timestamp

datetime与timestamp

一个完整的日期格式如下：YYYY-MM-DD HH:MM:SS[.fraction]，

它可分为两部分：date部分和time部分，

其中，

date部分对应格式中的“YYYY-MM-DD”，

time部分对应格式中的“HH:MM:SS[.fraction]”。

对于date字段来说，它只支持date部分，如果插入了time部分的内容，它会丢弃掉该部分的内容，并提示一个warning。

1、相同点：

 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期

2、不同点：

2.1、存储方式不一样

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。

而对于DATETIME，不做任何改变，基本上是原样输入和输出。

科普：

世界时UT即格林尼治平太抄阳时间，是指格林尼治所在地的标准时间，也是表示地球自转速率的一种形式。以地球自转为基础的时间计量系统。对于世界上发生的重大事件，都以格林尼治的地方时间记录下来。一旦知道了格林尼治时间，人们就很容易推算出相当的本地时间。

 

CST 时区是一个很混乱的时区，有四种含义：

 

美国中部时间 Central Standard Time (USA) UTC-06:00

澳大利亚中部时间 Central Standard Time (Australia) UTC+09:30

中国标准时 China Standard Time UTC+08:00

古巴标准时 Cuba Standard Time   UTC-04:00

2.2、存储时间范围不同

timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。

datetime所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

 

总结：

TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适。

 

3、datatime、timestamp自动初始化和更新

 

mysql 中有这样的一个默认行为，如果一行数据中某些列被更新了，如果这一行中有timestamp类型的列，那么这个timestamp列的数据也会被自动更新到 更新操作所发生的那个时间点；

这个操作是由explicit_defaults_for_timestamp这个变更控制的.

​​​​​​​3.1、开关

         -- 查询Automatic Initialization and Updating开关状态

          show variables like '%explicit_defaults_for_timestamp%';

 

         -- 把全局的设置为on 那么新的连接就会被设置成on

        set  global explicit_defaults_for_timestamp=on;

 

        -- 把当前连接explicit_defaults_for_timestamp设置为on

        set  session explicit_defaults_for_timestamp=on;

 

​​​​​​​    3.2、如何让自动更新失效：

explicit_defaults_for_timestamp 变量会直接影响表结构；

explicit_defaults_for_timestamp的作用时间是在表定义的时候；

表结构在关闭前创建的，你的update | insert 想通过它去改变timestamp字段值行为已经太晚了！

此时解决办法:改表结构

    alter table tableName modify column columnName  timestamp null default null;

​​​​​​​3.3、版本说明

在MySQL 5.6.5版本之前，Automatic Initialization and Updating只适用于TIMESTAMP，而且一张表中，最多允许一个TIMESTAMP字段采用该特性。从MySQL 5.6.5开始，Automatic Initialization and Updating同时适用于TIMESTAMP和DATETIME，且不限制数量。