当前 GMT 时间的 MySQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2258274/
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 query for current GMT time
提问by Russell C.
This sounds simple enough but I haven't been able to figure out how to use a simple SELECT statement to return the current time in GMT.
这听起来很简单，但我一直无法弄清楚如何使用简单的 SELECT 语句以格林威治标准时间返回当前时间。
I have been trying to use CONVERT_TZ()to convert NOW() to GMT based on the server time zone and the GMT time zone but for some reason it returns NULL when I put in the text time zones. The only way I get a result is to actually put in the offsets which is getting way too complicated for what should be a really simple operation. Here is what I mean:
我一直在尝试使用CONVERT_TZ()根据服务器时区和 GMT 时区将 NOW() 转换为 GMT，但由于某种原因，当我输入文本时区时它返回 NULL。我得到结果的唯一方法是实际放入偏移量，这对于一个真正简单的操作来说变得太复杂了。这就是我的意思：
mysql> SELECT CONVERT_TZ(NOW(),@@global.system_time_zone,'GMT');
NULL
mysql> SELECT CONVERT_TZ(NOW(),'PST','GMT');
NULL
mysql> SELECT CONVERT_TZ(NOW(),'-08:00','+00:00');
2010-02-13 18:28:22
All I need is a simple query to return the current time in GMT. Thanks in advance for your help!
我只需要一个简单的查询来返回 GMT 中的当前时间。在此先感谢您的帮助！
回答by Rufinus
Just use UTC (doesnt get affected with daylight savings time)
只需使用 UTC（不受夏令时影响）
SELECT UTC_TIMESTAMP();
Old Content for reference:
旧内容供参考：
this should work, but with
这应该有效，但与
SELECT CONVERT_TZ(NOW(),'PST','GMT');
i got also NULL as result. funny enough the example in the mysql docu also returns null
结果我也得到了NULL。有趣的是，mysql 文档中的示例也返回 null
SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tzseems you found a bug in mysql. (thanks to +Stephen Pritchard)
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz似乎您在 mysql 中发现了一个错误。（感谢 +Stephen Pritchard）
you could try:
你可以试试：
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SELECT NOW();
SET TIME_ZONE=@OLD_TIME_ZONE;
ok is not exactly what you wanted (its 4 queries, but only one select :-)
ok 不完全是你想要的（它有 4 个查询，但只有一个选择 :-)
回答by Stephen Pritchard
NO BUG in CONVERT_TZ()
CONVERT_TZ() 中没有错误
To use CONVERT_TZ() you need to install the time-zone tables otherwise MySql returns NULL.
要使用 CONVERT_TZ() 您需要安装时区表，否则 MySql 返回 NULL。
From the CLI run the following as root
从 CLI 以 root 身份运行以下命令
# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u 根 mysql
SEE http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html
见http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html
Thanks
谢谢
http://www.ArcanaVision.com(SJP 2011-08-18)
http://www.ArcanaVision.com(SJP 2011-08-18)
回答by Timo Huovinen
Note: GMT might have DST UTC does not have DST
注意：GMT 可能有 DST UTC 没有 DST
SELECT UTC_TIMESTAMP();
I made a cheatsheet here: Should MySQL have its timezone set to UTC?
我在这里做了一个备忘单：MySQL 是否应该将时区设置为 UTC？
回答by Goodbye StackExchange
The surefire way to fetch the current UTC datetime is:
获取当前 UTC 日期时间的可靠方法是：
SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+0:00')
回答by Mark
When the source server timestamp is unknown but the requirement is for a specific tz do this:
当源服务器时间戳未知但要求是针对特定 tz 时，请执行以下操作：
select convert_tz(utc_timestamp(),'+05:00','+00:00')
This uses CST as an example. utc_timestamp will guarantee a '+00:00' result no matter where you query it. The '+05:00' is your desired offset. This works great when hitting multiple unknown servers and it assures you that you returning result will all be in a common tz result set.
这里以 CST 为例。无论您在哪里查询，utc_timestamp 都将保证“+00:00”结果。'+05:00' 是您想要的偏移量。这在访问多个未知服务器时非常有效，它可以确保您返回的结果都在一个公共的 tz 结果集中。
回答by Zakaria Bin Abdur Rouf
I was searching for my time zone which is GMT+6 (Asia/Dhaka TimeZone)
我正在寻找我的时区，即 GMT+6（亚洲/达卡时区）
MySQL server is in US. The below worked for me.
MySQL服务器在美国。以下为我工作。
SELECT CONVERT_TZ( UTC_TIMESTAMP( ) , '+06:00', '+00:00' )
SELECT CONVERT_TZ( UTC_TIMESTAMP( ) , '+06:00', '+00:00' )
回答by konung
This should work - I think you are specified your timezone wrong. Using Chicago as example
这应该有效 - 我认为您指定的时区错误。以芝加哥为例
SELECT CONVERT_TZ(NOW(), 'America/Chicago', 'GMT')
回答by Mauvis Ledford
After seeing all the answers above and seeing it's unreliable to convert to PST, I just used this:
在看到上面的所有答案并看到转换为 PST 不可靠后，我只是使用了这个：
DATE_SUB(user_last_login, INTERVAL 7 hour)
DATE_SUB（用户最后一次登录，间隔 7 小时）
回答by Daniel
There is no bug in CONVERT_TZ function. You get NULL because you use time zones names/abbreviations. Mysql does not know what this 'GMT','PST', etc means unless you install mysql.time_zone_name table. However if you use numbers it will always work.
CONVERT_TZ 函数中没有错误。你得到 NULL 因为你使用时区名称/缩写。除非您安装 mysql.time_zone_name 表，否则 Mysql 不知道这个“GMT”、“PST”等是什么意思。但是，如果您使用数字，它将始终有效。
mysql> SELECT CONVERT_TZ(NOW(), 'America/Chicago', 'GMT');
returns NULL
返回 NULL
mysql> SELECT CONVERT_TZ(NOW(),'-08:00','+00:00');
returns 2016-06-24 17:58:33
返回 2016-06-24 17:58:33
