MYSQL 在 PHP 代码中设置时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12296780/
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 set timezone in PHP code
提问by XTRUST.ORG
How can I change MYSQL TIMEZONE to GMT format like this function in PHP: /SET GMT TIMEZONE/ date_default_timezone_set('Etc/GMT');
如何将 MYSQL TIMEZONE 更改为 GMT 格式，如 PHP 中的此函数：/ SET GMT TIMEZONE/ date_default_timezone_set('Etc/GMT');
My DB class is here:
我的 DB 课程在这里：
class DB {
private static $instance;
private $MySQLi;
private function __construct(array $dbOptions){
$this->MySQLi = @ new mysqli( $dbOptions['db_host'],
$dbOptions['db_user'],
$dbOptions['db_pass'],
$dbOptions['db_name'] );
if (mysqli_connect_errno()) {
throw new Exception('Database error.');
}
$this->MySQLi->set_charset("utf8");
}
public static function init(array $dbOptions){
if(self::$instance instanceof self){
return false;
}
self::$instance = new self($dbOptions);
}
public static function getMySQLiObject(){
return self::$instance->MySQLi;
}
public static function query($q){
return self::$instance->MySQLi->query($q);
}
public static function prepare($q){
return self::$instance->MySQLi->prepare($q);
}
public static function esc($str){
return self::$instance->MySQLi->real_escape_string(htmlspecialchars($str));
}
}
And my queries like this in other files:
我在其他文件中的查询是这样的：
DB::query('UPDATE `calendar_data` SET `data` = "'.DB::esc(json_encode($array)).'", `upcoming_time` = "'.date('Y-m-d H:i:s', $upcoming).'", `time_now` = NOW() WHERE `id` = "1"');
Where should I insert my query to the class for run it once?
我应该在哪里将我的查询插入到类中以运行一次？
Thanks!
谢谢！
$this->MySQLi->set_charset("utf8");
$this->MySQLi->query("SET timezone = 'GMT'");
- Doesn't work. If I use NOW(), server time was inserted (GMT-4). Where is a problem???
- 不起作用。如果我使用 NOW()，则会插入服务器时间 (GMT-4)。哪里有问题？？？
回答by Mike
Try the following:
请尝试以下操作：
$this->MySQLi->query("SET time_zone = '+0:00'");
Using named timezones will only work if the time zone information tables in the MySQL database have been created and populated.
仅当 MySQL 数据库中的时区信息表已创建并填充时，使用命名时区才有效。
回答by Dan Grossman
After you write this:
写完之后：
$this->MySQLi->set_charset("utf8");
Also write this:
还写这个：
$this->MySQLi->query("SET timezone = 'GMT'");
This timezone setting will apply to all future queries sent on that connection.
此时区设置将应用于在该连接上发送的所有未来查询。
Also, ensure you've set up the zone tables in the mysql database.
此外，请确保您已在 mysql 数据库中设置区域表。
http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html
http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html
回答by K-Gun
From manual;
来自手册；
Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:
每个连接的时区。每个连接的客户端都有自己的时区设置，由会话 time_zone 变量给出。最初，会话变量从全局 time_zone 变量中获取其值，但客户端可以使用以下语句更改自己的时区：
mysql> SET time_zone = timezone;
So must be like this;
所以一定是这样；
$this->MySQLi->query("SET time_zone = 'GMT'");
http://dev.mysql.com/doc/refman/5.5/en//time-zone-support.html
http://dev.mysql.com/doc/refman/5.5/en//server-system-variables.html#sysvar_time_zone
http://dev.mysql.com/doc/refman/5.5/en//time-zone-support.html
http://dev.mysql.com/doc/refman/5.5/en//server-system-variables .html#sysvar_time_zone
回答by Sherlock
MySQL has extensive timezone support. You can set it on server level, on a per connection basis and even per query. I think executing 'SET time_zone = xxx' in __construct is enough for you.
MySQL 有广泛的时区支持。您可以在服务器级别、基于每个连接甚至每个查询进行设置。我认为在 __construct 中执行 'SET time_zone = xxx' 对你来说就足够了。
