将 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 设置，如下所示:
We can retrieve the server and client time_zone settings with a query, like this:
SELECT @@global.time_zone, @@session.time_zone;
我们还可以更改会话的客户端时区，或更改整个 MySQL 实例的时区.
We can also change the client timezone for a session, or change the timezone for the entire MySQL instance.
但我们需要敏锐地意识到这种变化将对现有客户端连接产生的影响，以及已经存储在实例中的 DATETIME 和 TIMESTAMP 值将如何被解释.
But we need to be keenly aware of the implication that this change will have 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, we can modify the /etc/my.cnf file (or wherever the mysql instance initialization parameters are read from), under the [mysqld] section:
[mysqld]
default-time-zone='+00:00'
-- 或--
也可以(不太理想)将 --default_time_zone='+00:00' 选项添加到 mysqld_safe
It is also possible (less desirable) to add the --default_time_zone='+00:00' option to mysqld_safe
注意: 更改 MySQL 服务器上的时区设置不会更改存储在现有 DATETIME 或 TIMESTAMP 列中的值，但是因为它确实有效地更改了解释这些存储值的上下文，所以它看起来所有的值都被转移了.(其中 08:00 表示美国中部标准时间上午 8 点，服务器的 time_zone 从 CST 更改为 GMT，同样的08:00"现在将被视为格林尼治标准时间上午 8 点，实际上是美国中部标准时间凌晨 2 点.
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/zh/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.)
相关文章