如何让 MySQL 的 NOW() 和 CURDATE() 函数使用 UTC？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/947299/
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
How do I make MySQL's NOW() and CURDATE() functions use UTC?
提问by Chad Johnson
I want to make it so calls to NOW() and CURDATE() in MySQL queries return the date in UTC. How do I make this happen without going through and changing all queries that use these functions?
我想让它在 MySQL 查询中调用 NOW() 和 CURDATE() 返回 UTC 中的日期。如何在不经历和更改使用这些函数的所有查询的情况下实现这一点？
采纳答案by Chad Johnson
Finally found what I was looking for...
终于找到我要找的了。。。
In my.cnf,
在 my.cnf 中，
[mysqld_safe]
timezone = UTC
I was putting this option under [mysqld], and mysql was failing to start.
我把这个选项放在 [mysqld] 下，mysql 启动失败。
Calling "SET time_zone='+0:00';" on every page load would also work, but I don't like the idea of calling that query on every single page load.
调用“SET time_zone='+0:00';” 在每个页面加载上也可以工作，但我不喜欢在每个页面加载时调用该查询的想法。
回答by MarkR
Set your server's clock to UTC. No really.
将服务器的时钟设置为 UTC。不完全是。
If you can do it, do do it.
如果你能做到，那就去做吧。
One of the biggest headaches is "cron" jobs etc, running in local time zone, this means that some cron jobs will get missed once a year, and all the rest run at a different time GMT for half the year (I'm assuming you're in a time zone which has daylight saving time here).
最大的难题之一是在本地时区运行的“cron”作业等，这意味着某些 cron 作业每年会错过一次，而其余所有作业都在格林威治标准时间的不同时间运行半年（我假设您所在的时区有夏令时）。
MySQL has time zone support, but it's crazy and messed up. Don't use it if you don't have to. Just set your server's clock to UTC and time will start working how it should.
MySQL 有时区支持，但它很疯狂而且一团糟。如果没有必要，请不要使用它。只需将您的服务器时钟设置为 UTC，时间就会开始工作。
I know that changing the server clock is a major change for any managed system, and you may have a large number of servers and services which could be affected, but please, try to do it anyway. The QA work may be significant, but try.
我知道更改服务器时钟对于任何托管系统都是一项重大更改，您可能有大量服务器和服务可能会受到影响，但请无论如何都尝试这样做。QA 工作可能很重要，但请尝试。
回答by arulraj.net
Goto /etc/mysql/my.cnf file and add this below line under [mysqld] section
转到 /etc/mysql/my.cnf 文件并在 [mysqld] 部分下添加以下行
default-time-zone = '+00:00'
默认时区 = '+00:00'
Then restart your mysql. Now select curtime(); shows the GMT time.
然后重启你的mysql。现在选择 curtime(); 显示 GMT 时间。
回答by Dan
If changing the timezone on your running production servers or updating a key configuration setting and restarting mysql seems unrealistic and/or overkill, try this:
如果更改正在运行的生产服务器上的时区或更新关键配置设置并重新启动 mysql 似乎不切实际和/或矫枉过正，请尝试以下操作：
CONVERT_TZ(NOW(), 'US/Pacific', 'UTC')
Where US/Pacificis the timezone your NOW()call is returning the time in.
US/Pacific您的NOW()呼叫返回时间的时区在哪里。
回答by wasserholz
UTC_TIMESTAMP()
Returns the current UTC date and time as a value in 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss.uuuuuu format, depending on whether the function is used in a string or numeric context.
以“YYYY-MM-DD hh:mm:ss”或 YYYYMMDDhhmmss.uuuuuu 格式的值形式返回当前 UTC 日期和时间，具体取决于函数是在字符串还是数字上下文中使用。
UTC_DATE()
Returns the current UTC date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
返回当前 UTC 日期作为 'YYYY-MM-DD' 或 YYYYMMDD 格式的值，具体取决于函数是在字符串还是数字上下文中使用。
UTC_TIME()
Returns the current UTC time as a value in 'hh:mm:ss' or hhmmss.uuuuuu format, depending on whether the function is used in a string or numeric context.
返回当前 UTC 时间作为 'hh:mm:ss' 或 hhmmss.uuuuuu 格式的值，具体取决于函数是在字符串还是数字上下文中使用。
MySQL reference: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_utc-timestamp
MySQL 参考：https: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_utc-timestamp
回答by andri
The proper way to do this is to change your server's time zone to UTC, just as MarkR said.
正确的方法是将服务器的时区更改为 UTC，正如 MarkR 所说。
However, it's also possible to use SET time_zoneto change the time zone for your current session.
但是，也可以使用SET time_zone来更改当前会话的时区。
From the manual:
从手册：
The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME()
当前会话时区设置会影响对区域敏感的时间值的显示和存储。这包括由 NOW() 或 CURTIME() 等函数显示的值
回答by websch01ar
You will need to use the SET TIMESTAMP statement to format the datetime results in the desired format. This will mean changing all those queries. sysdate() will not obey this though.
您将需要使用 SET TIMESTAMP 语句将日期时间结果格式化为所需的格式。这将意味着更改所有这些查询。sysdate() 不会遵守这一点。
