将MySQL数据库时区设置为GMT [英] Set MySQL database timezone to GMT

查看:5608
本文介绍了将MySQL数据库时区设置为GMT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要更改单个数据库的时区是否可能?

I need to change the timezone of a single database is this possible?

我知道我们可以更改WHM中的时区(我们使用专用服务器hostgator),然而在服务器上运行的大量遗留软件有很多+6小时编码(即服务器时区是CST,我们希望在GMT时间,以前的开发人员手动在代码中更改日期/时间 - 坏!)。

I know we can change the timezone within our WHM (we are using a dedicated server from hostgator), however a large number of legacy software running on the server has a lot of +6 hours coding in it (i.e. server timezone is CST, we wanted the time in GMT so previous developers altered date/times manually within the code - bad!).

我现在正在使用一个新的软件,并希望有所有在GMT,我知道我可以使用date_default_timezone_set('GMT')不解决MySQL插入,其中datetime列设置为CURRENT_TIMESTAMP,因为它将插入@ CST时区。

I am now working on a new software and would like to have it all in GMT, I know I can use date_default_timezone_set('GMT') however that will not solve MySQL inserts where the datetime column is set to CURRENT_TIMESTAMP as it will insert @ CST timezone.

推荐答案

不,不可能更改MySQL实例中单个数据库的时区。

No, it's not possible to change the timezone for a single database within a MySQL instance.

您可以检索服务器和客户端 time_zone 设置:

You can retrieve the server and client time_zone settings:

SELECT @@global.time_zone, @@session.time_zone;

您还可以更改客户端时区或整个MySQL实例的时区。

You can also change the client timezone, or the timezone for the entire MySQL instance.

但是,请深入了解对现有客户端连接的影响,以及如何解释实例中已存储的DATETIME和TIMESTAMP值。

But be keenly AWARE of the implication that has on existing client connections, and the how DATETIME and TIMESTAMP values already stored in the instance will be interpreted.

要在MySQL实例启动时设置服务器time_zone,修改 /etc/my.cnf 文件(或从mysql实例初始化参数读取的位置),在[mysqld]部分下:

To have the server time_zone set at MySQL instance startup, modify the /etc/my.cnf file (or wherever your mysql instance initialization parameters are read from), under the [mysqld] section:

[mysqld]
default-time-zone='+00:00' 

- 或 -

为mysqld_safe添加 - default_time_zone ='+ 00:00'选项

add the --default_time_zone='+00:00' option to mysqld_safe

注意: / b>更改MySQL服务器上的时区设置不会更改存储在现有DATETIME或TIMESTAMP列中的值,但是因为它有效地更改了解释这些存储值的上下文,所以它看起来像是所有的值都被移位。 (其中08:00被认为是指CST的8AM,服务器的time_zone从CST改为GMT,同样的'08:00'现在将被视为8AM GMT,实际上是2AM CST。

NOTE: Changing the timezone setting on the MySQL server does NOT change the values stored in existing DATETIME or TIMESTAMP columns, BUT since it does effectively change the context in which those stored values are interpreted, it will look like all of the values ARE shifted. (Where 08:00 was taken to mean 8AM CST, with the time_zone of the server changed from CST to GMT, that same '08:00' will now be taken to be 8AM GMT, which would effectively be 2AM CST.

还要记住,TIMESTAMP列始终以UTC存储,而DATETIME列没有时区。
http://dev.mysql.com/doc/refman/5.5/en/datetime.html

Also keep in mind that TIMESTAMP columns are always stored in UTC, while DATETIME columns do not have a timezone. http://dev.mysql.com/doc/refman/5.5/en/datetime.html

每个客户端会话可以更改自己会话的时区设置:

Each client session can change the timezone setting for their own session:

SET time_zone='-06:00';






时区转换问题,它只是移动转换问题。


But none of this really "solves" the timezone conversion problem, it just moves the conversion problem around.

应用程序层处理时区转换没有固有的坏处;有时,这是最好的地方

There's nothing inherently "bad" with the application layer handling timezone conversions; sometimes, that's the best place to handle. It just has to be done correctly and consistently.

(你描述的设置有什么奇怪的是,应用程序存储的DATETIME值,如果MySQL服务器time_zone是设置为GMT,但MySQL服务器的time_zone设置为其他值。)

(What's odd about the setup you describe is that the app is storing DATETIME values as if the MySQL server time_zone is set to GMT, but the MySQL server time_zone is set to something else.)

这篇关于将MySQL数据库时区设置为GMT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆