MySQL MySQL时区更改？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3451847/
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
MySQL timezone change?
提问by Karem
How do I change my timezone which is currently in UTC to GMT +1, what is the correct line and do i just enter it in phpMyAdmin SQL execution?
如何将当前处于 UTC 的时区更改为 GMT +1，正确的行是什么，我是否只需在 phpMyAdmin SQL 执行中输入它？
My host just gave me this link http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.htmland went off so I'm kinda lost thanks
我的主人刚刚给了我这个链接http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html然后就走了，所以我有点迷失了谢谢
采纳答案by Bryon Hibbetts
issue the command:
发出命令：
SET time_zone = 'America/New_York';
(Or whatever time zone GMT+1 is.: http://www.php.net/manual/en/timezones.php)
（或任何时区 GMT+1 是。：http: //www.php.net/manual/en/timezones.php）
This is the command to set the MySQL timezone for an individual client, assuming that your clients are spread accross multiple time zones.
这是为单个客户端设置 MySQL 时区的命令，假设您的客户端分布在多个时区。
This command should be executed before every SQL command involving dates. If your queries go thru a class, then this is easy to implement.
此命令应在每个涉及日期的 SQL 命令之前执行。如果您的查询通过一个类，那么这很容易实现。
回答by Ben D
The easiest way to do this, as noted by Umar is, for example
执行此操作的最简单方法，如 Umar 所指出的，例如
mysql> SET GLOBAL time_zone = 'America/New_York';
Using the named timezone is important for timezone that has a daylights saving adjustment. However, for some linux builds you may get the following response:
使用命名时区对于具有夏令时调整的时区很重要。但是，对于某些 linux 版本，您可能会收到以下响应：
#1298 - Unknown or incorrect time zone
#1298 - 未知或不正确的时区
If you're seeing this, you may need to run a tzinfo_to_sql translation... it's easy to do, but not obvious. From the linux command line type in:
如果你看到这个，你可能需要运行一个 tzinfo_to_sql 翻译......这很容易做到，但并不明显。从 linux 命令行输入：
mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql -u root mysql -p
Provide your root password (MySQL root, not Linux root) and it will load any definitions in your zoneinfo into mysql. You can then go back and run your
提供您的根密码（MySQL 根，而不是 Linux 根），它会将您的 zoneinfo 中的任何定义加载到 mysql 中。然后你可以回去运行你的
mysql> SET GLOBAL time_zone = timezone;
回答by JJC
While Bryon's answer is helpful, I'd just add that his link is for PHP timezone names, which are not the same as MySQL timezone names.
虽然 Bryon 的回答很有帮助，但我只想补充一点，他的链接是针对 PHP 时区名称的，它与 MySQL 时区名称不同。
If you want to set your timezone for an individual session to GMT+1 (UTC+1 to be precise) just use the string '+01:00' in that command. I.e.:
如果要将单个会话的时区设置为 GMT+1（准确地说是 UTC+1），只需在该命令中使用字符串“+01:00”。IE：
SET time_zone = '+01:00';
SET time_zone = '+01:00';
To see what timezone your MySQL session is using, just execute this:
要查看您的 MySQL 会话使用的时区，只需执行以下命令：
SELECT @@global.time_zone, @@session.time_zone;
SELECT @@global.time_zone, @@session.time_zone;
This is a great reference with more details: MySQL 5.5 Reference on Time Zones
这是一个很好的参考，有更多的细节：MySQL 5.5 Reference on Time Zones
回答by drolex
Here is how to synchronize PHP (>=5.3) and MySQL timezones per session and user settings. Put this where it runs when you need set and synchronized timezones.
以下是如何同步每个会话和用户设置的 PHP (>=5.3) 和 MySQL 时区。当你需要设置和同步时区时，把它放在它运行的地方。
date_default_timezone_set($my_timezone);
$n = new \DateTime();
$h = $n->getOffset()/3600;
$i = 60*($h-floor($h));
$offset = sprintf('%+d:%02d', $h, $i);
$this->db->query("SET time_zone='$offset'");
Where $my_timezone is one in the list of PHP timezones: http://www.php.net/manual/en/timezones.php
其中 $my_timezone 是 PHP 时区列表中的一个：http://www.php.net/manual/en/timezones.php
The PHP timezone has to be converted into the hour and minute offset for MySQL. That's what lines 1-4 do.
PHP 时区必须转换为 MySQL 的小时和分钟偏移量。这就是第 1-4 行所做的。
回答by Umar Adil
If you have the SUPER privilege, you can set the global server time zone value at runtime with this statement:
如果您有 SUPER 权限，则可以在运行时使用以下语句设置全局服务器时区值：
mysql> SET GLOBAL time_zone = timezone;
回答by Arjun s
If SET time_zone or SET GLOBAL time_zone does not work, you can change as below:
如果 SET time_zone 或 SET GLOBAL time_zone 不起作用，您可以更改如下：
Change timezone system, example: ubuntu... $ sudo dpkg-reconfigure tzdata
Restart the server or you can restart apache2 and mysql (/etc/init.d/mysql restart)
更改时区系统，例如：ubuntu... $ sudo dpkg-reconfigure tzdata
重启服务器或者重启apache2和mysql（/etc/init.d/mysql restart）
回答by yaseen ahmed
This works fine
这工作正常
<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");
$con->query("SET GLOBAL time_zone = 'Asia/Calcutta'");
$con->query("SET time_zone = '+05:30'");
$con->query("SET @@session.time_zone = '+05:30'");
?>
