通过系统变量 time_zone 可以得到当前数据库的时区设置。 mysql> select @@time_zone; +-------------+ | @@time_zone | +-------------+ | SYSTEM | +-------------+ 1 row in set (0.00 sec)mysql>
TIMESTAMP值以UTC格式保存,存储时对当前的时区进行转换,检索时再转换回当前的时区。只要时区设定值为常量,便可以得到保存时的值。如果保存一个TIMESTAMP值,应更改时区然后检索该值,它与你保存的值不同。这是因为在两个方向的转换中没有使用相同的时区。当前的时区可以用作time_zone系统变量的值。 time/datetime 只是存储时间,没有time zone 的换算。
我遇到一种情况是,通过SHOW VARIABLES LIKE '%time_zone'查询数据库时区,得到的是CST(这里的CST是东八区,而GMT-06:00也是CST,出现了歧义),而有的数据库的timezone为空,这种情况下使用SELECT EXTRACT(HOUR FROM TIMEDIFF(NOW() ,UTC_TIMESTAMP())) AS OFFSET是不是得到的时区偏移量更准确呢?
这种情况下使用SELECT EXTRACT(HOUR FROM TIMEDIFF(NOW() ,UTC_TIMESTAMP())) AS OFFSET是不是得到的时区偏移量更准确呢? 也是一种方法。
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
1 row in set (0.00 sec)mysql>
time/datetime 只是存储时间,没有time zone 的换算。
是否数据库设置了时区,再创建time/datetime/timestamp这三种类型的列的时候,读取记录的时候都要加上数据库时区的偏移量呢?
TIMESTAMP 会根据时区重新加上偏移。time/datetime 不变
也是一种方法。
数据库time_zone='-7:00'CREATE TABLE `tt` (
`t1` time DEFAULT NULL,
`t2` datetime DEFAULT NULL,
`t3` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
)
执行时时间为 GMT+8:00 即东八区的 2010-02-25 09:26:21
insert into tt (t1,t2,t3) values (CURTIME(), now(), CURRENT_TIMESTAMP);
查询后:
t1 = 18:26:21
t2 = 2010-02-24 18:26:21
t3 = 2010-02-24 18:26:21看上面的结果,time和datetime类型存入的不是UTC时间,而是相对GMT-7:00的时间啊
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)mysql> select UTC_TIMESTAMP(),CURRENT_TIMESTAMP();
+---------------------+---------------------+
| UTC_TIMESTAMP() | CURRENT_TIMESTAMP() |
+---------------------+---------------------+
| 2010-02-25 02:33:34 | 2010-02-25 10:33:34 |
+---------------------+---------------------+
1 row in set (0.00 sec)mysql> CREATE TABLE `tt` (
-> `t1` time ,
-> `t2` datetime ,
-> `t3` timestamp
-> );
Query OK, 0 rows affected (0.16 sec)mysql> insert into tt (t1,t2,t3) values (CURTIME(), now(), CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.03 sec)mysql> select * from tt;
+----------+---------------------+---------------------+
| t1 | t2 | t3 |
+----------+---------------------+---------------------+
| 10:34:54 | 2010-02-25 10:34:54 | 2010-02-25 10:34:54 |
+----------+---------------------+---------------------+
1 row in set (0.00 sec)mysql> set @@session.time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)mysql> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | +00:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)mysql> select UTC_TIMESTAMP(),CURRENT_TIMESTAMP();
+---------------------+---------------------+
| UTC_TIMESTAMP() | CURRENT_TIMESTAMP() |
+---------------------+---------------------+
| 2010-02-25 02:35:15 | 2010-02-25 02:35:15 |
+---------------------+---------------------+
1 row in set (0.00 sec) -- 注意只有t3 的时间显示有变化。 ACMAIN
mysql> select * from tt;
+----------+---------------------+---------------------+
| t1 | t2 | t3 |
+----------+---------------------+---------------------+
| 10:34:54 | 2010-02-25 10:34:54 | 2010-02-25 02:34:54 |
+----------+---------------------+---------------------+
1 row in set (0.00 sec)mysql> set @@session.time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.00 sec)mysql> select * from tt;
+----------+---------------------+---------------------+
| t1 | t2 | t3 |
+----------+---------------------+---------------------+
| 10:34:54 | 2010-02-25 10:34:54 | 2010-02-25 10:34:54 |
+----------+---------------------+---------------------+
1 row in set (0.00 sec)mysql>
如果从一个数据库迁移到另一个数据库的时候:
(1)database(GMT+8:00) ----> database database(GMT+8:00)
这种情况日期时间字段是无需转换的(2)database(GMT-07:00) ----> database database(GMT+8:00)
这种情况,是不是需要把time/datetime/timestamp都先由GMT-07:00到GMT+00:00再到GMT+8:00
这个过程呢?
首先我们再理解一下2#楼这段文字TIME/DATEIME 如果INSERT是 '2010-02-25 10:34:54' ,那它实际存的就是 '2010-02-25 10:34:54' ,你可以把它想象为一个存了一个字符串或者数字。 这样,不管你如何操作,当你读的时候还是 '2010-02-25 10:34:54' TIMESTAMP值以UTC格式保存,存储时对当前的时区进行转换,检索时再转换回当前的时区。只要时区设定值为常量,便可以得到保存时的值。如果保存一个TIMESTAMP值,应更改时区然后检索该值,它与你保存的值不同。TIMESTAMP 当你插入'2010-02-25 10:34:54' 的时候,如果现在时区是 +8, 那实际在数据库中存的是什么呢? 对照一下这段文字,这儿就不重复了。
select convert_tz(t3,'-7:00','+8:00'),t2,t1 from tt
对于time/datetime 根据你的用户的要求,转则你需要手工用函数转一下,是否正确你一试即知。
对于timestamp 则不需要,系统会自动根据时区换算。
如果通过JDBC读数据,再写入到另一个数据库,这样就得需要转换一下了。
不好意思,我没有说清楚,我是把从mysql数据库的数据迁移到其他数据库的(非mysql的无时区概念的数据库)