Mysql FROM_UNIXTIME 作为 UTC
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18276768/
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 FROM_UNIXTIME as UTC
提问by wutzebaer
How can i get
我怎样才能得到
FROM_UNIXTIME
as UTC/GMT in mysql? The date is returned in the timezone of the connection.
作为 UTC/GMT 在 mysql 中？日期以连接的时区返回。
I don't want to change the timezone of the connection.
我不想更改连接的时区。
采纳答案by Matt Johnson-Pint
You would be better off setting the time zone ahead of time:
您最好提前设置时区：
SET time_zone='UTC';
select FROM_UNIXTIME(1277942400);
The reason is that conversions involving a local time zone can be lossy. There is an example of this in the docs here(see the 4th paragraph under the UNIX_TIMESTAMP()section starting with "Note:")
原因是涉及本地时区的转换可能是有损的。此处的文档中有一个示例（请参阅UNIX_TIMESTAMP()以“注意：”开头的部分下的第4 段）
回答by wutzebaer
my solution was
我的解决方案是
SELECT CONVERT_TZ(FROM_UNIXTIME(1277942400), @@session.time_zone,'UTC')
if CONVERT_TZ returns null, make sure the timezone table of mysql is filled:
如果 CONVERT_TZ 返回空值，请确保 mysql 的时区表被填满：
zypper install mysql-community-server-tools
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
回答by Mārti?? Radi??
I know this has been answered but still I'd like to contribute:
我知道这已经得到回答，但我仍然想贡献：
If you wan't your server to produce UTC timezone time with functions like NOW()and FROM_UNIXTIME()etc., you have to set the time_zonelike @Matt Johnson said.
如果你与像功能wan't您的服务器生产UTC时区的时间NOW()和FROM_UNIXTIME()等等，你必须设置time_zone像@马特约翰逊说。
There's a thing he didn't mention: SET time_zone = timezone;sets the timezone for the current connection only.
Use SET GLOBAL time_zone = '+00:00'if you want these functions to return/convert your dates in UTC by default so you didn't have to set the timezone every time you want to save some UTC based dates.
有一件事他没有提到：SET time_zone = timezone;只为当前连接设置时区。使用SET GLOBAL time_zone = '+00:00'，如果你想这些函数返回/转换你的日期在UTC在默认情况下，所以你没得你想节省一些基于UTC日期每一次设置时区。
Check your current time_zone settings: SELECT @@global.time_zone, @@session.time_zone;
检查您当前的 time_zone 设置： SELECT @@global.time_zone, @@session.time_zone;
MySQL :: MySQL 5.5 Reference Manual :: 10.6 MySQL Server Time Zone Support
回答by Issay
if you are lazy like me and don't want to change global session timezone variables (moved to another server, forgot to change that there and hop, you are in troubles), session variables (used some module autoreconnecting to db and hop, your variable issued at connection is gone) and don't wish to load time zone stuff then i suppose this might help :).
如果您像我一样懒惰并且不想更改全局会话时区变量（移到另一台服务器，忘记在那里更改并跳转，您遇到麻烦），会话变量（使用一些模块自动重新连接到数据库和跳转，您的连接时发出的变量消失了）并且不想加载时区内容，那么我想这可能会有所帮助:)。
select
now() as timezoned,
date_add(
FROM_UNIXTIME(0), interval
unix_timestamp()+TIMESTAMPDIFF(SECOND,NOW(),UTC_TIMESTAMP()) SECOND
) as utc
and it's going to work even with negative timestamps. and if you need to convert a specific column, just replace unix_timestamp() with your column containing the timestamp.
即使使用负时间戳，它也能正常工作。如果您需要转换特定列，只需将 unix_timestamp() 替换为包含时间戳的列。
as a bonus, if you need to find a difference between datetime and now, while the column is in unix_timestamp (say, "utc"), just wrap it with TIMESTAMPdiff(SECOND, .., NOW()) and the day is saved :).
作为奖励，如果您需要找到日期时间和现在之间的差异，而该列位于 unix_timestamp（例如，“utc”）中，只需将其用 TIMESTAMPdiff(SECOND, .., NOW()) 包装起来即可保存这一天:)。
