通过 PhPMyAdmin 在 MySql Server 上设置默认时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5381581/
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
Setting default-time-zone on MySql Server via PhPMyAdmin
提问by sigi
I have a shared hosting mySql instance which has it's system_time_zone set to Pacific Standard Time and it's time_zone variable set to System, hence effectively it's running on Pacific Standard Time.
我有一个共享托管 mySql 实例，它的 system_time_zone 设置为太平洋标准时间，time_zone 变量设置为 System，因此它实际上在太平洋标准时间上运行。
i.e. I've run the following command to find this out:
即我已经运行以下命令来找出这一点：
SELECT version( ) , @@time_zone , @@system_time_zone , NOW( ) , UTC_TIMESTAMP( )
I would like to change the default mySql database / local mySql DB time-zone to GMT/UTC time. I tried to run, SET time_zone = '+0:00', and this does execute successfully!
我想将默认的 mySql 数据库/本地 mySql DB 时区更改为 GMT/UTC 时间。我试图运行，，SET time_zone = '+0:00'这确实执行成功！
However, this does not seem to affect the time_zone variable, when I check the state of @@time_zone. I've looked at another post dealing with similar issue How to set MySQL to use GMT in Windows and Linuxand I also checked the MySql documentation, with little progress. Since I am on a shared-hosting solution, I have limited access and I don't have access to more than what my PhPMyAdmin mySql functionality has on offer.
但是，当我检查@@time_zone 的状态时，这似乎不会影响 time_zone 变量。我看过另一篇处理类似问题的帖子How to set MySQL to use GMT in Windows and Linux我还检查了MySql 文档，但进展不大。由于我使用的是共享托管解决方案，因此我的访问权限有限，并且除了我的 PhPMyAdmin mySql 功能所提供的功能外，我无法访问更多内容。
I wonder if there is any way to change the default_time-zone from within an SQL query, or do I need to fall back to the command line (to which I don't have access to, unfortunately).
我想知道是否有任何方法可以从 SQL 查询中更改 default_time-zone，或者我是否需要退回到命令行（不幸的是，我无权访问）。
Thanks for your help and advice,
感谢您的帮助和建议，
Martin
马丁
采纳答案by Wh1T3h4Ck5
For shared hosting, you have to ask support-guys to help you and change default time zone for you? I had similar problem with Arcor hosting-provider, called them and they fixed it. Before that, I found temporary solution in date_default_timezone_set()from PHP code. Probably the best solution is to ask someone who has privilege to change that parameter.
对于共享主机，您必须请支持人员帮助您并为您更改默认时区？我在 Arcor 托管服务提供商那里遇到了类似的问题，给他们打电话，他们修复了它。在此之前，我date_default_timezone_set()从 PHP 代码中找到了临时解决方案。可能最好的解决方案是询问有权更改该参数的人。
回答by Matty J
In short, MySQLactually stores 'datetime' data type fields internally as UTC.
简而言之，MySQL实际上在datetime内部将 ' ' 数据类型字段存储为UTC.
However, PhpMyAdminshows you the dates using the server default time, hence your confusion.
However,PhpMyAdmin使用服务器默认时间向您显示日期，因此您会感到困惑。
For example, try adding this line before your SQL statement in PhpMyAdmin:
例如，尝试在 SQL 语句之前添加以下行PhpMyAdmin：
SET @@session.time_zone='+00:00';
SELECT * FROM MY_TABLE
See the MySQLdocumentation for further details, or the answer in this post: How to correctly set mysql timezone
有关MySQL更多详细信息，请参阅文档或这篇文章中的答案：如何正确设置 mysql 时区
Cheers Matt
干杯马特
回答by AceStaCy
<?php
date_default_timezone_set('UTC'); //define local time
$date=date('l jS \of F Y h:i:s A'); //type of time shown
$conn=mysql_connect("localhost","root","") or die('Could not connect!'); //your database connection here
$db_selected = mysql_select_db('databasename', $conn); //select db
$result=mysql_query("INSERT INTO table (date) VALUES ('$date')", $conn);
?>
Simply sent the time as VARCHAR into db hope it helps and sorry for syntax errors (if there are any).
只需将时间作为 VARCHAR 发送到 db 希望它有所帮助，并对语法错误（如果有的话）表示抱歉。
