时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数。在生产环境中，部署着各种版本的MySQL,包括MySQL 5.5/5.6/5.7三个大版本和N个小版本。由于MySQL在向上兼容性较差，导致相同SQL在不同版本上表现各异。下面从几个方面来详细介绍时间戳数据类型。
一、时间戳数据存取
在MySQL上述三个大版本中，默认时间戳(Timestamp)类型的取值范围为'1970-01-01 00:00:01' UTC 至'2038-01-19 03:14:07' UTC,数据精确到秒级别。该取值范围包含约22亿个数值，因此在MySQL内部使用4个字节INT类型来存放时间戳数据。
1. 存储时间戳数据时，先将本地时区时间转换为UTC时区时间，再将UTC时区时间转换为INT格式的毫秒值(使用UNIX_TIMESTAMP函数),然后存放到数据库中。
2. 读取时间戳数据时，先将INT格式的毫秒值转换为UTC时区时间(使用FROM_UNIXTIME函数),然后再转换为本地时区时间，最后返回给客户端。
在MySQL 5.6.4及之后版本，可以将时间戳类型数据最高精确微秒(百万分之一秒),数据类型定义为timestamp(N),N取值范围为0-6,默认为0。如需要精确到毫秒则设置为Timestamp(3),如需要精确到微秒则设置为timestamp(6)。数据精度提高的代价是其内部存储空间的变大，但仍未改变时间戳类型的最小和最大取值范围。
二、时间戳字段定义
时间戳字段定义主要影响两类操作：插入记录时，时间戳字段包含DEFAULT CURRENT_TIMESTAMP;更新记录时，时间戳字段包含ON UPDATE CURRENT_TIMESTAMP。PS1:CURRENT_TIMESTAMP表示使用CURRENT_TIMESTAMP()函数来获取当前时间，类似于NOW()函数。根据上面两类操作，时间戳列可以有四张组合定义，其含义分别为：
当字段定义为timestamp,表示该字段在插入和更新时都不会自动设置为当前时间。
当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP时，表示该字段仅在插入且未指定值时被赋予当前时间。而在更新时，如果未指定值，则不做修改。
当字段定义为timestamp ON UPDATE CURRENT_TIMESTAMP时，表示该字段在插入且未指定值时被赋值为"0000-00-00 00:00:00",在更新且未指定值时更新为当前时间。
当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP时，表示该字段在插入或更新时未指定值，则被赋值为当前时间。
需要注意的是，在MySQL中执行的建表语句和最终表创建语句会存在差异，建议使用SHOW CREATE TABLE TB_XXX获取已创建表的建表语句。此外，时间戳字段在MySQL各版本的使用差异也需要注意。在MySQL 5.5及之前版本中，仅能对一个时间戳字段定义DEFUALT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP,但在MySQL 5.6和MySQL 5.7版本中取消了该限制。在MySQL 5.6版本中参数explicit_defaults_for_timestamp默认值为1,在MySQL 5.7版本中参数explicit_defaults_for_timestamp默认值为0。在MySQL 5.5和MySQL 5.7版本中timestamp类型默认为NOT NULL,而在MySQL 5.6版本中timestamp类型默认为NULL。
当建表语句中定于c1 timestamp时，在MySQL 5.5中等价于c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;在MySQL 5.6中等价于c1 timestamp NULL DEFAULT NULL;在MySQL 5.7中等价于c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。当建表语句中c1 timestamp default 0时，具体的行为取决于所使用的MySQL版本。
在MySQL 5.5中，等价于c1 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'的语句是：
```sql
ALTER TABLE c1 MODIFY COLUMN c1 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
```
在MySQL 5.6中，等价于c1 timestamp NULL DEFAULT '0000-00-00 00:00:00'的语句是：
```sql
ALTER TABLE c1 MODIFY COLUMN c1 TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00';
```
在MySQL 5.7中，等价于c1 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'的语句是：
```sql
ALTER TABLE c1 MODIFY COLUMN c1 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
```
PS1: MySQL 5.6版本和MySQL 5.7版本中主要差异受参数explicit_defaults_for_timestamp的默认值影响。
PS2:当时间戳列的默认值为'0000-00-00 00:00:00'时，使用“不在时间戳取值范围内”的该默认值并不会产生警告。
在只关心数据最后更新时间的情况下，建议将时间戳列定义为TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;。
在关心创建时间和更新时间的情况下，建议将更新时间设置为时间戳字段，将创建时间定义为DAETIME或TIMESTAMP DEFAULT '0000-00-00 00:00:00',并在插入记录时显式指定创建时间。
建议在表中只定义单个时间戳列，并显式定义DEFAULT和ON UPDATE属性。
虽然在MySQL中可以对时间戳字段赋值或更新，但建议仅在必要的情况下对时间戳列进行显式插入和更新。
建议将time_zone参数设置为system外的值，如中国地区服务器设置为'+8:00'。
建议将MySQL线下测试版本和线上生产版本保持一致。
Timestamp和datetime的异同：
相同点：
1. 可自动更新和初始化，默认显示格式相同YYYY-MM-dd HH:mm:ss。
不同点：
2. timestamp的时间范围是：'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC,自动时区转化，实际存储毫秒数，4字节存储。
3. datetime的时间范围：'1000-01-01 00:00:00' to '9999-12-31 23:59:59',不支持时区，8字节存储。
设置timestamp和date的自动更新时间：当对某条数据进行更新操作时或者插入一条新的数据而没有对date和mydate进行赋值，date和mydate这两个字段会自动默认为当前时间。
2038问题：当timestamp存储的时间大于'2038-01-19 03:14:07' UTC,mysql就会报错，因为这是mysql自身的问题，也就是说timestamp是有上限的，超过了，自然会报错。具体原因查看官方文档：https://dev.mysql.com/doc/refman/8.0/en/datetime.html,部分截图如下。解决方案
时间戳虽然有上限限制，但是它保存的是时间戳，可以不用去考虑时区的问题。如果是需要处理与时区相关的需求，解决2038限制的时候，建议将timestamp改为整数类型，用来保存时间戳，在程序中再进行转换(这个方案没有实施过，仅仅是建议，慎用！!)。
如果不需要考虑时区问题，直接用datetime类型替换timestamp即可，因为datetime的取值范围大很多。下面是替换的思路：
1. 修改原来字段的名字；
2. 新建一个datetime类型的字段(新建一列，用来替换原来的);
3. 将原来字段列的数据拷贝到新的字段列中；
4. 删除原来的列。
完整sql如下(需要注意，原来的timestamp的默认值也需要加上):
```sql
ALTER TABLE table_name MODIFY column_name DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
```
mysql之TIMESTAMP(时间戳)用法
一、TIMESTAMP的变体
TIMESTAMP时间戳在创建的时候可以有多重不同的特性，如：
1. 在创建新记录和修改现有记录的时候都对这个数据列刷新；
2. 在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它；
3. 在创建新记录的时候把这个字段设置为0,以后修改时刷新它；
4. 在创建新记录的时候把这个字段设置为给定值，以后修改时刷新它。
MySQL目前不支持列的Default为函数的形式，如达到你某列的默认值为当前更新日期与时间的功能，你可以使用TIMESTAMP列类型。下面就详细说明TIMESTAMP列类型。
二、TIMESTAMP列类型
TIMESTAMP值可以从1970的某时的开始一直到2037年，精度为一秒，其值作为数字显示。TIMESTAMP值显示尺寸的格式如下表所示：
| TIMESTAMP显示尺寸 | 含义 |
| --- | --- |
| 6 | YYYYMMDD |
| 8 | YYYY-MM-DD |
| 12 | YYYY-MM-DDTHH:MI:SS |
| 14 | YYYY-MM-DDTHH:MI:SS.UUUUuu |
| 完整的TIMESTAMP格式是14位，但TIMESTAMP列也可以用更短的显示尺寸，创造最常见的显示尺寸是6、8、12、和14。你可以在创建表时指定一个任意的显示尺寸，但是定义列长为0或比14大均会被强制定义为列长14。列长在从1～13范围的奇数值尺寸均被强制为下一个更大的偶数。所有的TIMESTAMP列都有同样的存储大小，使用被指定的时期时间值的完整精度(14位)存储合法的值不考虑显示尺寸。不合法的日期，将会被强制为0存储。
虽然在创建表时，你为TIMESTAMP列定义了8个字符的长度(TIMESTAMP(8)),但在实际进行数据插入和更新时，该列实际上保存了14位的数据(包括年月日时分秒)。然而，当你执行查询时，MySQL返回的TIMESTAMP值仅为8位的年月日数据。通过使用ALTER TABLE命令扩展一个较窄的TIMESTAMP列，之前被隐藏的信息将被显示出来。
相反地，缩小一个TIMESTAMP列并不会导致信息丢失，只是在显示时，较少的信息会被显示出来。尽管TIMESTAMP值以完整精度存储，但直接操作这些值的唯一函数是UNIX_TIMESTAMP()。由于MySQL返回的TIMESTAMP列值是经过格式化后的检索值，这意味着在某些情况下，你可能无法使用某些函数(如HOUR()或SECOND())来操作TIMESTAMP列，除非TIMESTAMP值的相关部分包含在格式化的值中。
例如，当一个TIMESTAMP列仅被定义为TIMESTAMP(10)或更高时，TIMESTAMP列的HH部分才会被显示。因此，在较短的TIMESTAMP值上使用HOUR()可能会产生一个不可预测的结果。
不合法的TIMESTAMP值会被转换为适当类型的“零”值(00000000000000)。同样适用于DATETIME和DATE类型。
以下是一个示例，用于验证这一点：
```sql
SELECT NOW();
```
此外，你还可以使用TIMESTAMP列类型自动地用当前的日期和时间标记INSERT或UPDATE操作。如果有多个TIMESTAMP列，只有第一个会自动更新。以下是自动更新第一个TIMESTAMP列的条件：
1. 列值没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。
2. 列值没有明确地在一个UPDATE语句中指定且其他列的值发生了变化。(注意，一个UPDATE语句将一个列为它已经具有的值，这不会触发TIMESTAMP列的更新，因为如果你将一个列为它当前的值，MySQL为了效率而忽略了更改。)
3. 你明确地将TIMESTAMP列为NULL。
4. 除了第一个之外的其他TIMESTAMP列也可以设置为当前的日期和时间，只要将列设为NULL或NOW()。
第一条指令中，date1和date2的值都被设置为NULL,所以它们的值都是当前时间。
第二条指令中，由于没有明确设定date1和date2的列值，第一个TIMESTAMP列date1被更新为当前时间。然而，第二个TIMESTAMP列date2由于日期不合法而变为了"00000000000000"。
这条指令没有明确地设定date2的列值，所以第一个TIMESTAMP列date1将被更新为当前时间。但是，第二个TIMESTAMP列date2因设定了date2=NOW(),所以在更新数据时，date2列值会被更新为当前时间。
此指令等效于：因MySQL返回的 TIMESTAMP 列为数字显示形式，你可以用DATE_FROMAT()函数来格式化 TIMESTAMP 列，如下所示：
```sql
UPDATE your_table SET date1 = DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'), date2 = NOW();
```
在某种程度上，你可以把一种日期类型的值赋给一个不同的日期类型的对象。然而，尤其注意的是：值有可能发生一些改变或信息的损失：
1. 如果你将一个DATE值赋给一个DATETIME或TIMESTAMP对象，结果值的时间部分被设置为'00:00:00',因为DATE值中不包含有时间信息。
2. 如果你将一个DATETIME或TIMESTAMP值赋给一个DATE对象，结果值的时间部分被删除，因为DATE类型不存储时间信息。
3. 尽管DATETIME、DATE和TIMESTAMP值全都可以用同样的格式集来指定，但所有类型不都有同样的值范围。例如，TIMESTAMP值不能比1970早，也不能比2037晚，这意味着，一个日期例如'1968-01-01',当作为一个DATETIME或DATE值时它是合法的，但它不是一个正确TIMESTAMP值！并且如果将这样的一个对象赋值给TIMESTAMP列，它将被变换为0。
四、当指定日期值时，当心某些缺陷：
1. 允许作为字符串指定值的宽松格式能被欺骗。例如，因为“：”分隔符的使用，值'10:11:12'可能看起来像时间值，但是如果在一个日期中使用，上下文将作为年份被解释成'2010-11-12'。值'10:45:15'将被变换到'0000-00-00',因为'45'不是一个合法的月份。
2. 当使用日期/时间函数进行计算时，可能会丢失精度或引入错误。例如，如果你尝试将两个TIMESTAMP列相加，可能会得到一个不正确的结果。
因此，在处理日期和时间类型的数据时，请务必小心并确保你的操作是准确的。
MySQL使用以下规则来解释2位年值：
- 对于00-69范围内的年值，会被转换为2000-2069。
- 对于范围70-99的年值，会被转换为1970-1999。
需要注意的是，由于世纪未知，所以以2位数字指定的年值是模糊的。
此外，本文还推荐了一个Unix时间戳转换工具，该工具支持各种编程语言(包括PHP、MySQL、SQL Server、Java等)的时间戳获取与转换操作技巧。您可以访问以下链接了解更多信息：
Unix时间戳(timestamp)转换工具：http://tools.jb51.net/code/unixtime
