MySQL 可以将存储的 UTC 时间转换为本地时区吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2187593/
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
Can MySQL convert a stored UTC time to local timezone?
提问by Johan
Can MySQL convert a stored UTC time to local timezon:ed time directly in a normal select statement?
MySQL 可以在普通的 select 语句中直接将存储的 UTC 时间转换为本地 timezon:ed 时间吗？
Let's say you have some data with a timestamp (UTC).
假设您有一些带有时间戳 (UTC) 的数据。
CREATE TABLE `SomeDateTable` (
`id` int(11) NOT NULL auto_increment,
`value` float NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
)
Then when I
然后当我
"select value, date from SomeDateTable";
I of course get all the dates as in their stored UTC form.
我当然会以它们存储的 UTC 形式获取所有日期。
But let's say that I would like to have them in another timezone (with DST), can I then add some magic to the select query so that I get all the dates back in the selected timezone?
但是，假设我希望将它们放在另一个时区（使用 DST），然后我可以向选择查询添加一些魔法，以便将所有日期返回到所选时区吗？
"select value, TIMEZONE(date, "Europe/Berlin") from SomeDateTable";
Or must I do this in some other layer on top, like in some php code? (it seems to be how most people have solved this problem).
或者我必须在顶部的其他层中执行此操作，例如在某些 php 代码中？（这似乎是大多数人解决这个问题的方式）。
If your MySQL installation allows you to use CONVERT_TZ it is a very clean solution, this example shows how to use it.
如果您的 MySQL 安装允许您使用 CONVERT_TZ，这是一个非常干净的解决方案，此示例显示了如何使用它。
SELECT CONVERT_TZ( '2010-01-01 12:00', 'UTC', 'Europe/Stockholm' )
However I don't know if this is a good way since some MySQL installation is missing this function, use with care.
但是我不知道这是否是一个好方法，因为某些 MySQL 安装缺少此功能，请谨慎使用。
采纳答案by Stephen Fischer
Yup, there's the convert_tzfunction.
是的，有这个convert_tz功能。
回答by tmsimont
For those unable to configure the mysql environment (e.g. due to lack of SUPER access) to use human-friendly timezone names like "America/Denver" or "GMT" you can also use the function with numeric offsets like this:
对于那些无法配置 mysql 环境（例如，由于缺乏超级访问权限）以使用人性化的时区名称（如“America/Denver”或“GMT”）的人，您还可以使用具有数字偏移量的函数，如下所示：
CONVERT_TZ(date,'+00:00','-07:00')
回答by Jestin
select convert_tz(now(),@@session.time_zone,'+03:00')
For get the time only use:
获取时间只使用：
time(convert_tz(now(),@@session.time_zone,'+03:00'))
回答by MR_AMDEV
One can easily use
一个可以轻松使用
CONVERT_TZ(your_timestamp_column_name, 'UTC', 'your_desired_timezone_name')
For example:
例如：
CONVERT_TZ(timeperiod, 'UTC', 'Asia/Karachi')
Plusthis can also be used in WHERE statement and to compare timestamp i would use the following in Where clause:
另外，这也可以在 WHERE 语句中使用并比较时间戳，我将在 Where 子句中使用以下内容：
WHERE CONVERT_TZ(timeperiod, 'UTC', '{$this->timezone}') NOT BETWEEN {$timeperiods['today_start']} AND {$timeperiods['today_end']}
回答by zerkms
I propose to use
我建议使用
SET time_zone = 'proper timezone';
being done once right after connect to database. and after this all timestamps will be converted automatically when selecting them.
连接到数据库后立即完成一次。在此之后，所有时间戳将在选择它们时自动转换。
回答by TheBuzzSaw
I am not sure what math can be done on a DATETIME data type, but if you are using PHP, I strongly recommend using the integer-based timestamps. Basically, you can store a 4-byte integer in the database using PHP's time() function. This makes doing math on it much more straightforward.
我不确定可以对 DATETIME 数据类型进行哪些数学运算，但是如果您使用的是 PHP，我强烈建议您使用基于整数的时间戳。基本上，您可以使用 PHP 的 time() 函数在数据库中存储一个 4 字节的整数。这使得对其进行数学运算变得更加简单。
