将 MySQL 数据库时区设置为 GMT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13916747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set MySQL database timezone to GMT
提问by buzzmonkey
I need to change the timezone of a single database is this possible?
我需要更改单个数据库的时区，这可能吗？
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!).
我知道我们可以在我们的 WHM 中更改时区（我们使用的是来自 hostgator 的专用服务器），但是在服务器上运行的大量遗留软件中有很多 +6 小时的编码（即服务器时区是 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.
我现在正在开发一个新软件，并希望在 GMT 中使用它，我知道我可以使用 date_default_timezone_set('GMT') 但是这不会解决 MySQL 插入，其中日期时间列设置为 CURRENT_TIMESTAMP，因为它会插入 @CST时区。
回答by spencer7593
No, it's not possible to change the timezone for a single database within a MySQL instance.
不，无法更改 MySQL 实例中单个数据库的时区。
We can retrieve the server and client time_zonesettings with a query, like this:
我们可以time_zone通过查询检索服务器和客户端设置，如下所示：
SELECT @@global.time_zone, @@session.time_zone;
We can also change the client timezone for a session, or change the timezone for the entire MySQL instance.
我们还可以更改会话的客户端时区，或更改整个 MySQL 实例的时区。
But we need to be keenly aware of the implication that this change will have on existing client connections, and the how DATETIMEand TIMESTAMPvalues already stored in the instance will be interpreted.
但是我们需要敏锐地意识到这种变化将对现有客户端连接产生的影响，以及已经存储在实例中的方式DATETIME和TIMESTAMP值将被解释。
To have the server time_zone set at MySQL instance startup, we can modify the /etc/my.cnffile (or wherever the mysql instance initialization parameters are read from), under the [mysqld]section:
要在 MySQL 实例启动时设置服务器 time_zone，我们可以在以下部分修改/etc/my.cnf文件（或读取 mysql 实例初始化参数的任何位置）[mysqld]：
[mysqld]
default-time-zone='+00:00'
-- or --
- 或者 -
It is also possible (less desirable) to add the --default_time_zone='+00:00'option to mysqld_safe
也可以（不太理想）将--default_time_zone='+00:00'选项添加到 mysqld_safe
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.
注意：更改 MySQL 服务器上的时区设置不会更改存储在现有 DATETIME 或 TIMESTAMP 列中的值，但由于它确实有效地更改了解释这些存储值的上下文，因此看起来所有值都已移动。（其中 08:00 表示 CST 上午 8 点，服务器的 time_zone 从 CST 更改为 GMT，相同的“08:00”现在将被视为格林威治标准时间上午 8 点，这实际上是中部标准时间凌晨 2 点。
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
另请记住，TIMESTAMP 列始终以 UTC 格式存储，而 DATETIME 列没有时区。 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.
应用程序层处理时区转换没有什么本质上的“坏”；有时，这是处理的最佳位置。它只需要正确和一致地完成。
(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.)
（您描述的设置的奇怪之处在于应用程序存储 DATETIME 值，就好像 MySQL 服务器 time_zone 设置为 GMT，但 MySQL 服务器 time_zone 设置为其他内容。）
回答by Can Bezmen
If you can't change your current time zone you can change the result.
如果您无法更改当前时区，则可以更改结果。
date 2015-01-05 12:06:16
日期 2015-01-05 12:06:16
Select date + Interval 2 Hour 'date' From ExampleTable
date 2015-01-05 14:06:16
日期 2015-01-05 14:06:16
回答by cHAuHaN
You can modify the the default value instead of entering current_timestamp make it insert current_timestamp added to hours offset of your timezone. I just did it this way when didn't found any solution, had to invent my own.
您可以修改默认值而不是输入 current_timestamp 使其插入 current_timestamp 添加到您的时区的小时偏移量。当没有找到任何解决方案时，我就是这样做的，不得不自己发明。
