引入

日期与时间是非常重要的信息，在我们的系统中，几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签，从而进行数据查询、统计和处理。因此，日期与时间类型也是我们最常用到的类型之一，今天就来聊一聊日期与时间类型中的TIMESTAMP类型。

在MySQL中提供的常用的日期类型有：DATE、TIME、DATETIME、TIMESTAMP。

• YEAR 类型通常用来表示年
• DATE 类型通常用来表示年、月、日
• TIME 类型通常用来表示时、分、秒
• DATETIME 类型通常用来表示年、月、日、时、分、秒
• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

在这几种类型中，只有TIMESTAMP能够与实际时区相对应

一、DATE\TIME\DATETIME

DATE、TIME和DATETIME是经常使用到的3种日期类型，以下在三种类型字段中插入相同日期值，来看看显示效果：

    #创建表    mysql> CREATE TABLE t (        -> date DATE,        -> time TIME,        -> datetime DATETIME        -> );    #插入数据    mysql> INSERT INTO t VALUES(        -> now(),now(),now()        -> );    #查看结果    mysql> SELECT * FROM t;    +------------+----------+---------------------+    | date       | time     | datetime            |    +------------+----------+---------------------+    | 2022-07-13 | 09:40:47 | 2022-07-13 09:40:47 |    +------------+----------+---------------------+

可以看到，DATETIME是DATE和TIME的组合。

二、TIMESTAMP

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是 YYYY-MM-DD HH:MM:SS ，需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间。

TIMESTAMP的标准格式为：YY-MM-DD HH:MM:SS

我们首先来看一下explicit_defaults_for_timestamp(5.6版本后引入)参数默认值：

    mysql> show variables like 'explicit%';    +---------------------------------+-------+    | Variable_name                   | Value |    +---------------------------------+-------+    | explicit_defaults_for_timestamp | OFF   |    +---------------------------------+-------+    1 row in set (0.00 sec)

我们创建一个测试表 test_t1,字段tsp为TIMESTAMP类型

    mysql> CREATE TABLE test_t1(        -> tsp TIMESTAMP        -> );    Query OK, 0 rows affected (0.01 sec)        mysql> DESC test_t1;    +-------+-----------+------+-----+-------------------+-----------------------------+    | Field | Type      | Null | Key | Default           | Extra                       |    +-------+-----------+------+-----+-------------------+-----------------------------+    | tsp   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |    +-------+-----------+------+-----+-------------------+-----------------------------+    1 row in set (0.01 sec)

可以发现，系统tm自动创建了默认值CURRENT_TIMESTAMP（系统日期），并且设置了NOT NULL和on update CURRENT_TIMESTAMP属性。

• CURRENT_TIMESTAMP 当要向数据库执行INSERT操作时，如果有个timestamp字段属性设为 CURRENT_TIMESTAMP，则无论这个字段有没有set值，都插入当前系统时间
• ON UPDATE CURRENT_TIMESTAMP当执行update操作是，并且字段有ON UPDATE CURRENT_TIMESTAMP属性。则字段无论值有没有变化，他的值也会跟着更新为当前UPDATE操作时的时间。

现在插入一个null试试：

    mysql> INSERT INTO test_t1 VALUES(null);    Query OK, 1 row affected (0.00 sec)        mysql> SELECT * FROM test_t1;    +---------------------+    | tsp                 |    +---------------------+    | 2022-07-13 11:09:56 |    +---------------------+    1 row in set (0.00 sec)    

果然，test_t1中自动插入了系统的时间

    mysql> ALTER TABLE test_t1 ADD tsp2 TIMESTAMP;    Query OK, 0 rows affected (0.05 sec)    Records: 0  Duplicates: 0  Warnings: 0        mysql> DESC test_t1;    +-------+-----------+------+-----+-------------------+-----------------------------+    | Field | Type      | Null | Key | Default           | Extra                       |    +-------+-----------+------+-----+-------------------+-----------------------------+    | tsp   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |    | tsp2  | timestamp | YES  |     | NULL              |                             |    +-------+-----------+------+-----+-------------------+-----------------------------+    2 rows in set (0.00 sec)

注意MySQL只给表中第一个TIMESTAMP字段设置默认值为系统时间，如果有第二个TIMESTAMP类型，则设置默认值为0

但是这个默认值也是可以修改的，在MySQL5.6之前，如果强行修改是会报错的，但是在MySQL5.6之后，这个限制已经去掉了，可以随意修改，例如：

    mysql> ALTER TABLE test_t1 MODIFY tsp2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;     Query OK, 0 rows affected (0.01 sec)    Records: 0  Duplicates: 0  Warnings: 0        mysql> DESC test_t1;    +-------+-----------+------+-----+-------------------+-----------------------------+    | Field | Type      | Null | Key | Default           | Extra                       |    +-------+-----------+------+-----+-------------------+-----------------------------+    | tsp   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |    | tsp2  | timestamp | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |    +-------+-----------+------+-----+-------------------+-----------------------------+    2 rows in set (0.00 sec)

我们也可以将explicit_defaults_for_timestamp设置为ON，则默认值、not null和on update CURRENT_TIMESTAMP属性都不会自动设置，需要手动操作,具体如下：

    mysql> SET explicit_defaults_for_timestamp=on;    Query OK, 0 rows affected (0.00 sec)        mysql> CREATE TABLE test_t2 (notsp TIMESTAMP);    Query OK, 0 rows affected (0.01 sec)        mysql> DESC test_t2;    +-------+-----------+------+-----+---------+-------+    | Field | Type      | Null | Key | Default | Extra |    +-------+-----------+------+-----+---------+-------+    | notsp | timestamp | YES  |     | NULL    |       |    +-------+-----------+------+-----+---------+-------+    1 row in set (0.00 sec)    

​ 当你每次INSERT和UPDATE操作的时候，会自动记录当前操作的时间：

INSERT操作

    mysql> CREATE TABLE temp_test1(         -> timestamp TIMESTAMP,        -> name VARCHAR(10)        -> );    Query OK, 0 rows affected (0.02 sec)        mysql> insert into temp_test1 values(null,'小江');    Query OK, 1 row affected (0.01 sec)        mysql> SELECT * FROM temp_test1;    +---------------------+--------+    | timestamp           | name   |    +---------------------+--------+    | 2022-07-13 16:00:40 | 小江   |    +---------------------+--------+    1 row in set (0.00 sec)    

UPDATE操作

    mysql> UPDATE temp_test1 SET name='小张';    Query OK, 1 row affected (0.00 sec)    Rows matched: 1  Changed: 1  Warnings: 0        mysql> SELECT * FROM temp_test1;    +---------------------+--------+    | timestamp           | name   |    +---------------------+--------+    | 2022-07-13 16:02:34 | 小张   |    +---------------------+--------+    1 row in set (0.00 sec)    

三、TIMESTAMP和DATETIME的区别：

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
• TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能 反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

查看当前时区：

    mysql> SHOW VARIABLES LIKE 'time_zone';    +---------------+--------+    | Variable_name | Value  |    +---------------+--------+    | time_zone     | SYSTEM |    +---------------+--------+    1 row in set (0.00 sec)

可以看到，时区值为'SYSTEM'，这个值默认是和主机的时区一致的，因为我们在中国，这里的'SYSTEM'实际是东八区（+8:00）

下面创建表temp_time，包含字段datetime和timestamp，设置两个表的目的是为了做对比

    mysql> CREATE TABLE temp_time(         -> datetime DATETIME,        -> timestamp TIMESTAMP        -> );    Query OK, 0 rows affected (0.02 sec)        mysql> INSERT INTO temp_time         -> VALUES('2022-7-2 15:54:52','2022-7-2 15:54:52');     Query OK, 1 row affected (0.01 sec)        mysql>     mysql> INSERT INTO temp_time         -> VALUES(NOW(),NOW());    Query OK, 1 row affected (0.01 sec)        mysql> SELECT *         -> FROM temp_time;    +---------------------+---------------------+    | datetime            | timestamp           |    +---------------------+---------------------+    | 2022-07-02 15:54:52 | 2022-07-02 15:54:52 |    | 2022-07-13 14:18:29 | 2022-07-13 14:18:29 |    +---------------------+---------------------+    2 rows in set (0.00 sec)

用now()函数插入的时间完全相同，现在修改时区为东九区，再次查看表中情况：

    mysql> SET time_zone = '+9:00';    Query OK, 0 rows affected (0.00 sec)        mysql> SELECT * FROM temp_time;    +---------------------+---------------------+    | datetime            | timestamp           |    +---------------------+---------------------+    | 2022-07-02 15:54:52 | 2022-07-02 16:54:52 |    | 2022-07-13 14:18:29 | 2022-07-13 15:18:29 |    +---------------------+---------------------+    2 rows in set (0.00 sec)    

在结果中发现，timestamp比datetime的值快了1小时，也就是说，东九区的人看到的'2022-07-13 15:18:29'是当地时区的实际时间，也就是东八区的'2022-07-13 14:18:29'如果还是以'2022-07-13 14:18:29'理解时间，必然造成时间误差。

四、TIMESTAMP总结

• TIMESTAMP占用4个字节。
• 允许为空值，但是不可以自定义值，所以为空值时没有任何意义。
• TIMESTAMP值的下限是1970上限是2037。例如’1968-01-01’，虽然对于DATETIME或DATE类型是有效的，但对于TIMESTAMP值却无效，如果插入这样一个值将被转换为0。
• 默认值为CURRENT_TIMESTAMP()，也就是当前的系统时间。
• 数据库会自动修改其值，所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值，你只需要在设计表的时候添加一个timestamp字段即可，插入后该字段的值会自动变为当前系统时间。
• 默认情况下以后任何时间修改表中的记录时，对应记录的timestamp值会自动被更新为当前的系统时间。
• 如果需要可以设置不自动更新。通过设置explicit_defaults_for_timestamp 可以实现。
• 推荐日期类型使用DATETIME，而不是TIMESTAMP。