mysql时区类型读取

概述：

mysql和其他数据库在时区上处理不同，这里讲述一下如何去读mysql的timestamp类型。

1、mysql时区介绍

mysql> create table timestamp_test (id int,name1 datetime,name2 TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into timestamp_test values (1,'1970-01-01 08:00:01','1970-01-01 08:00:01');
Query OK, 1 row affected (0.01 sec)

mysql> select * from timestamp_test;
+------+---------------------+---------------------+
| id   | name1               | name2               |
+------+---------------------+---------------------+
|    1 | 1970-01-01 08:00:01 | 1970-01-01 08:00:01 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

---查询当前时区
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.01 sec)

---修改时区
mysql> set time_zone='+1:00';
Query OK, 0 rows affected (0.00 sec)


mysql> select * from timestamp_test;
+------+---------------------+---------------------+
| id   | name1               | name2               |
+------+---------------------+---------------------+
|    1 | 1970-01-01 08:00:01 | 1970-01-01 01:00:01 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)
---这就是时区类型的魅力所在，根据时区的不同，显示的日期也是不一样的，这正是timestamp类型在MySQL日期类型中独有的时区特点。而datetime不会跟着时区变化而改变