关于timestamp与datetime的一些理解

以下是测试表的表结构

mysql> show create table ttest;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ttest | CREATE TABLE `ttest` (
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dt` datetime DEFAULT NULL,
  `t` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

可见ts列是timestamp 类型，dt是datetime 类型，t是time类型。

mysql> SHOW VARIABLES LIKE '%time_zone%';

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | PDT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
以上是系统的时区，与数据库的时区
可见数据库的时区使用的是系统的时区

mysql> insert into ttest values (now(),now(),now());
Query OK, 1 row affected (0.01 sec)

插入一行数据，全都是now()，看他们在表中的转换情况。

mysql> select * from ttest;
+---------------------+---------------------+----------+
| ts                  | dt                  | t        |
+---------------------+---------------------+----------+
| 2015-08-11 22:31:37 | 2015-08-11 22:31:37 | 22:31:37 |
+---------------------+---------------------+----------+
1 row in set (0.00 sec)
此时看到timestamp与datetime的数据类型返回的时间是一样的。

我们来修改时区

mysql> set time_zone='+08:00';
Query OK, 0 rows affected (0.00 sec)
将时区设置为东8区

mysql> SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | PDT    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select * from ttest;

+---------------------+---------------------+----------+
| ts                  | dt                  | t        |
+---------------------+---------------------+----------+
| 2015-08-12 13:31:37 | 2015-08-11 22:31:37 | 22:31:37 |
+---------------------+---------------------+----------+
1 row in set (0.00 sec)

这时候可以看到时间datetime比timestamp少15个小时。然后time类型与datetime保持一致

官方手册里在时期类型里有这样说明

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time

说timestamp是将日期转换UTC时间进行存储，然后在取出时再转回当前的时区，这个情况并不会发生在datetime这类的类型上。默认的当前时区都是连接的系统时间。

之前入库是按PDT，即是西7区时间入库，此时显示是按东8区的时间显示，所以要入库的时间+15，dt+15h后整好就等于ts的时间！