time/datetime/timestamp这3中数据类型都可以携带timezone保存,在实际存储到数据库中,timezone是以什么形式存放的呢?但是在查询的时候,怎么才能把数据库里面的时间数值和timezone信息一起取到呢?

解决方案 »

  1.   

    通过系统变量 time_zone 可以得到当前数据库的时区设置。 mysql> select @@time_zone;
    +-------------+
    | @@time_zone |
    +-------------+
    | SYSTEM      |
    +-------------+
    1 row in set (0.00 sec)mysql>
      

  2.   

    TIMESTAMP值以UTC格式保存,存储时对当前的时区进行转换,检索时再转换回当前的时区。只要时区设定值为常量,便可以得到保存时的值。如果保存一个TIMESTAMP值,应更改时区然后检索该值,它与你保存的值不同。这是因为在两个方向的转换中没有使用相同的时区。当前的时区可以用作time_zone系统变量的值。
    time/datetime 只是存储时间,没有time zone 的换算。
      

  3.   

    这是数据库设置的时区,
    是否数据库设置了时区,再创建time/datetime/timestamp这三种类型的列的时候,读取记录的时候都要加上数据库时区的偏移量呢?
      

  4.   


    TIMESTAMP 会根据时区重新加上偏移。time/datetime 不变
      

  5.   

    我遇到一种情况是,通过SHOW VARIABLES LIKE '%time_zone'查询数据库时区,得到的是CST(这里的CST是东八区,而GMT-06:00也是CST,出现了歧义),而有的数据库的timezone为空,这种情况下使用SELECT EXTRACT(HOUR FROM TIMEDIFF(NOW() ,UTC_TIMESTAMP())) AS OFFSET是不是得到的时区偏移量更准确呢?
      

  6.   

    这种情况下使用SELECT EXTRACT(HOUR FROM TIMEDIFF(NOW() ,UTC_TIMESTAMP())) AS OFFSET是不是得到的时区偏移量更准确呢?
    也是一种方法。
      

  7.   


    数据库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的时间啊
      

  8.   

    你的试验是如何做的?测试没有你所说的情况啊。如下列出你的试验步骤。mysql> select @@global.time_zone, @@session.time_zone;
    +--------------------+---------------------+
    | @@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>
      

  9.   

    我是通过JDBC执行insert和select语句的
      

  10.   

    用navicat工具查询,也是这个结果
      

  11.   

    看你上面的查询结果,说明datetime和time类型,不是相对UTC时间,而是把时区偏移加进去保存的。
      

  12.   

    2#楼的官方解释已经很清楚了啊?time /datetime 字段没有时区的概念,你存进去什么就是什么!
      

  13.   


    如果从一个数据库迁移到另一个数据库的时候:
    (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
       这个过程呢?
      

  14.   


    首先我们再理解一下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, 那实际在数据库中存的是什么呢? 对照一下这段文字,这儿就不重复了。
      

  15.   

    当数据从-7:00迁移到+8:00时区是,我这样做应该是正确的吧?
    select convert_tz(t3,'-7:00','+8:00'),t2,t1 from tt
      

  16.   


    对于time/datetime 根据你的用户的要求,转则你需要手工用函数转一下,是否正确你一试即知。
    对于timestamp 则不需要,系统会自动根据时区换算。
      

  17.   

    1) 你说的这种情况是从mysql到mysql吧2) 我通过JDBC读timestamp数据的时候,读出来的是数据库当前时区的数据,非UTC数据的。
       如果通过JDBC读数据,再写入到另一个数据库,这样就得需要转换一下了。
      

  18.   

    TIMESTAMP值以UTC格式保存,存储时对当前的时区进行转换,检索时再转换回当前的时区。仔细分析一下这段文字。
      

  19.   

    老实说,我对mysql这个自动加时区的设置很郁闷的说,一不小心就搞错
      

  20.   


    不好意思,我没有说清楚,我是把从mysql数据库的数据迁移到其他数据库的(非mysql的无时区概念的数据库)