【MySQL】不同时区如何解析timestamp, 较datetime、date、time的区别

时间戳的定义

时间戳是指格林威治时间1970年01月01日00时00分00秒起至现在的总秒数。通俗的讲， 时间戳是一份能够表示一份数据在一个特定时间点已经存在的完整的可验证的数据。

• 系统时间

2019-11-29 10:00:15

• 带时区的系统时间

2019-11-29 10:00:15 UTC+8:00

• 系统时间的格林威治时间 —— 时间戳（Timestamp）

2019-11-29 02:00:15 UTC+0:00 ## 相差八小时

系统存储当前时间是不记录时区信息的记录的是 格林威治时间 至 当前系统时间 经过的秒数
2019-11-29 10:00:15 UTC+8:00 ~ 2019-11-29 02:00:15 UTC+0:00
共有 1574964015 秒 , 1574964015就是时间戳

解析时间戳的过程

• 日本时区(东九区)的操作系统读时间戳
  1574964015 读成格林威治时间, 同一时刻，中国的MySQL也是看到这一串数字

2019-11-29 02:00:15 UTC+0:00 

• 但是如果加上时区信息后：转换成带时区的时间 - 得到日本时区(东九区)

2019-11-29 11:00:15 UTC+9:00 

时间戳的原理

使用同一的时间标准（当前格林威治时间），使所有时区读到的时间都是一致的。
换言之，就是保持一个绝对时间，时区作为偏移量，这个偏移量由运行在各个国家的MySQL自己维护。

MySQL 中的 datetime 和 timestamp

datetime

• 占用字节
  8个字节
• 取值范围
  1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• 时区相关性
  时区不明感 -> 切换数据库时区，存储的值不变

timestamp

• 占用字节
  4个字节，mysql 实际上是用int类型存储timestamp的值
• 取值范围
  1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07
• 时区相关性
  时区明感 -> 切换数据库时区，存储的时间戳不变, 但是显示的时间变化

CREATE TABLE test(
    col_datetime DATETIME,
    col_timestamp TIMESTAMP
);
INSERT INTO test VALUES(NOW(), NOW());

SET time_zone = '+8:00';
SELECT * FROM test;

SET time_zone = '+5:00';
SELECT * FROM test;

timestamp 的附加价值

默认记录 插入（创建）、更新 的时间

ALTER TABLE test ADD col_2_timestamp TIMESTAMP;
SELECT * FROM test;

默认情况下, 一个表的第一个数据类型为TIMESTAMP的字段，
对应行的更新会触发该字段记录更新时间

## 只修改col_datatime, col_timestamp也会写入当前日期， col_2_timestamp 未变化
UPDATE test SET col_timestamp = now();

观察TIMESTAMP的默认建表语句

SHOW CREATE TABLE test;

CREATE TABLE `test` (
  `col_datetime` DATETIME DEFAULT NULL,
  `col_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `col_2_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=INNODB DEFAULT CHARSET=utf8

第一个TIMESTAMP自动补上DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
表示更新和插入都自动写入当前时间

第二个TIMESTAMP自动补上DEFAULT '0000-00-00 00:00:00'
表示只赋予默认值，没有其他附属功能

UPDATE test SET col_timestamp = '2019-11-29 11:12:06';

• 以上对该字段直接写入日期，就不会触发自动写入当前时间

使用两个timestamp，分别记录数据创建时间和更新时间

mysql 5.5 版本会报错

ALTER TABLE test ADD col_default TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

>  Error Code: 1293
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON 	UPDATE clause

解决方案：

## 一个列用于记录创建时间
ALTER TABLE test MODIFY col_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
## 使用触发器记录数据更新时间
CREATE TRIGGER `update_trigger` BEFORE UPDATE ON `test`FOR EACH ROW SET NEW.`col_2_timestamp` = NOW();

## 三条数据初始记录为2019-11-29 15:03:27
INSERT INTO test VALUES(NOW(), NOW(), NOW());
## 修改第一列，观察结果
UPDATE test SET col_datetime = '2000-11-11 11:11:11';
SELECT * FROM test;

mysql 5.6 版本后可以直接建表，不报错

ALTER TABLE test MODIFY col_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE test MODIFY col_2_timestamp TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

mysql 5.6 新增的 date 和 time

基本属性

• 占用字节
  都为3个字节
• 取值范围
  date : 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  time : HH:MM:SS，可以通过定义宽度显示毫秒时间
• 时区相关性
  时区不明感 -> 切换数据库时区，存储的值不变

date 和 time 都只用3个字节就可以保存日期数据，同时能够参与mysql的日期处理
datetime 8 个字节
date 3 个字节
time 3 个字节
timestamp 4 个字节 取值范围较小

如果只存储生日信息，可以使用date，他可以记录1970前的时间，并且体积比timestamp更小