关于mysql数据库时区问题

missyesterday 2010-02-24 05:02:23
time/datetime/timestamp这3中数据类型都可以携带timezone保存，在实际存储到数据库中，timezone是以什么形式存放的呢？

但是在查询的时候，怎么才能把数据库里面的时间数值和timezone信息一起取到呢？

...全文
685 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
missyesterday 2010-02-26
  • 打赏
  • 举报
 回复
引用 20 楼 flairsky 的回复:
老实说，我对mysql这个自动加时区的设置很郁闷的说，一不小心就搞错


我是在测试数据库分区迁移时，发现数据不一致，也被搞晕了。。。
missyesterday 2010-02-26
  • 打赏
  • 举报
 回复
引用 19 楼 acmain_chm 的回复:
引用2) 我通过JDBC读timestamp数据的时候，读出来的是数据库当前时区的数据，非UTC数据的。TIMESTAMP值以UTC格式保存，<span style="color:#FF0000">存储时</span>对当前的时区进行转换，<span style="color:#FF0000">检索时</span>再转换回当前的时区。

仔细分析一下这段文字。


不好意思，我没有说清楚，我是把从mysql数据库的数据迁移到其他数据库的（非mysql的无时区概念的数据库）
flairsky 2010-02-26
  • 打赏
  • 举报
 回复
老实说，我对mysql这个自动加时区的设置很郁闷的说，一不小心就搞错
ACMAIN_CHM 2010-02-26
  • 打赏
  • 举报
 回复
引用
2) 我通过JDBC读timestamp数据的时候，读出来的是数据库当前时区的数据，非UTC数据的。
TIMESTAMP值以UTC格式保存，存储时对当前的时区进行转换，检索时再转换回当前的时区。

仔细分析一下这段文字。
missyesterday 2010-02-26
  • 打赏
  • 举报
 回复
引用 17 楼 acmain_chm 的回复:
引用(2)database(GMT-07:00) ----> database database(GMT+8:00)
这种情况，是不是需要把time/datetime/timestamp都先由GMT-07:00到GMT+00:00再到GMT+8:00
这个过程呢？

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

1) 你说的这种情况是从mysql到mysql吧

2) 我通过JDBC读timestamp数据的时候，读出来的是数据库当前时区的数据，非UTC数据的。
如果通过JDBC读数据，再写入到另一个数据库，这样就得需要转换一下了。
ACMAIN_CHM 2010-02-25
  • 打赏
  • 举报
 回复
2#楼的官方解释已经很清楚了啊？

time /datetime 字段没有时区的概念，你存进去什么就是什么！
missyesterday 2010-02-25
  • 打赏
  • 举报
 回复
看你上面的查询结果，说明datetime和time类型，不是相对UTC时间，而是把时区偏移加进去保存的。
missyesterday 2010-02-25
  • 打赏
  • 举报
 回复
用navicat工具查询，也是这个结果
missyesterday 2010-02-25
  • 打赏
  • 举报
 回复
我是通过JDBC执行insert和select语句的
ACMAIN_CHM 2010-02-25
  • 打赏
  • 举报
 回复
你的试验是如何做的？测试没有你所说的情况啊。如下列出你的试验步骤。

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>
missyesterday 2010-02-25
  • 打赏
  • 举报
 回复
引用 2 楼 acmain_chm 的回复:
TIMESTAMP值以UTC格式保存，存储时对当前的时区进行转换，检索时再转换回当前的时区。只要时区设定值为常量，便可以得到保存时的值。如果保存一个TIMESTAMP值，应更改时区然后检索该值，它与你保存的值不同。这是因为在两个方向的转换中没有使用相同的时区。当前的时区可以用作time_zone系统变量的值。


time/datetime 只是存储时间，没有time zone 的换算。


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

ACMAIN_CHM 2010-02-25
  • 打赏
  • 举报
 回复
引用
(2)database(GMT-07:00) ----> database database(GMT+8:00)
这种情况，是不是需要把time/datetime/timestamp都先由GMT-07:00到GMT+00:00再到GMT+8:00
这个过程呢？


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


missyesterday 2010-02-25
  • 打赏
  • 举报
 回复
当数据从-7:00迁移到+8:00时区是，我这样做应该是正确的吧？
select convert_tz(t3,'-7:00','+8:00'),t2,t1 from tt
missyesterday 2010-02-25
  • 打赏
  • 举报
 回复
引用 14 楼 acmain_chm 的回复:
引用(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, 那实际在数据库中存的是什么呢？ 对照一下这段文字，这儿就不重复了。


存入字符串 '2010-02-25 10:34:54' 现在时区+8 实际存入数据库的是 2010-02-25 02:34:54



ACMAIN_CHM 2010-02-25
  • 打赏
  • 举报
 回复
引用
(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, 那实际在数据库中存的是什么呢？ 对照一下这段文字，这儿就不重复了。
missyesterday 2010-02-25
  • 打赏
  • 举报
 回复
引用 12 楼 acmain_chm 的回复:
2#楼的官方解释已经很清楚了啊？

time /datetime 字段没有时区的概念，你存进去什么就是什么！


如果从一个数据库迁移到另一个数据库的时候：
(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
这个过程呢？
ACMAIN_CHM 2010-02-24
  • 打赏
  • 举报
 回复
这种情况下使用SELECT EXTRACT(HOUR FROM TIMEDIFF(NOW() ,UTC_TIMESTAMP())) AS OFFSET是不是得到的时区偏移量更准确呢？
也是一种方法。
missyesterday 2010-02-24
  • 打赏
  • 举报
 回复
我遇到一种情况是，通过SHOW VARIABLES LIKE '%time_zone'查询数据库时区，得到的是CST(这里的CST是东八区，而GMT-06:00也是CST，出现了歧义)，而有的数据库的timezone为空，

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


ACMAIN_CHM 2010-02-24
  • 打赏
  • 举报
 回复
引用
是否数据库设置了时区，再创建time/datetime/timestamp这三种类型的列的时候，读取记录的时候都要加上数据库时区的偏移量呢？


TIMESTAMP 会根据时区重新加上偏移。

time/datetime 不变
missyesterday 2010-02-24
  • 打赏
  • 举报
 回复
这是数据库设置的时区，
是否数据库设置了时区，再创建time/datetime/timestamp这三种类型的列的时候，读取记录的时候都要加上数据库时区的偏移量呢？
加载更多回复（2）
Mysql查看数据库时区并设置时区
在mysql目录下使用命令行打开MySQL win + r 打开 cmd， cd进入mysql安装目录 输入mysql -hlocalhost -uroot -p 注意-前有空格。其中"-h"表示服务器名，localhost表示本地；"-u"为数据库用户名，root是MySQL默认用户名；"-p"为密码 提示输入密码，输入自己之前设置的密码，回车即可 Mysql查看数据库时区并设置时区 查看数据库时区show variables like'%time_zone'; 如果显示SYSTEM，则需要设置时区
IDEA配置mysql数据库时区
IDEA配置mysql数据库时区
Java查询Mysql数据库时区问题（相差13/14）个小时
默认把查询出的时间当作Central Standard Time (USA) 美国中央时区 (UTC-6)处理，转换为当前服务所在的时区东八区，差13-14个小时（夏令时和冬令时）。UTC是国际无线电咨询委员会制定和推荐的，若与GMT时差大于0.9秒，则由位于巴黎的国际地球自转事务中央局发布闰秒，使UTC与地球自转周期一致。中国大陆、中国香港、中国澳门、中国台湾、蒙古国、新加坡、马来西亚、菲律宾、西澳大利亚州的时间与UTC的时差均为+8，也就是UTC+8。表示的是数据库所在服务器的时区。
JDBC连接MySQL数据库时区问题
JDBC连接MySQL数据库时区问题：The server time zone value ‘�й���׼ʱ��‘ is unrecognized or represents more than on JDBC连接MySQLa数据库时区问题：Thie server atime zonge valuue '�йi���׼xʱ��' is unrecognized or represents more than one time zone. You must configure either th..
MySql数据库时区问题
问题： 往数据库插入数据时，发现时间比当前的时刻少了几个小时。 什么情况？？ 先来模拟一下事故现场 简单的设置一个时间，并插入数据库 @Service public class Test { @Autowired TsMapper tsMapper; @PostConstruct public void init() throws ParseException { TimeEntity timeEntity = new TimeEntity();
MySQL

56,681

社区成员

56,709

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章