在 MySQL 中计算时区的偏移量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/805538/
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
In MySQL caculating offset for a time zone
提问by Varun
Is there a way in MySQL to calculate the offset for any timezone?
For example, to get the local time in the timezone Asia/calcutta. What I want to do is calculate the offset for this timezone and add that offset to GMT to get the local time.
MySQL 有没有办法计算任何时区的偏移量？例如，获取 timezone 中的本地时间Asia/calcutta。我想要做的是计算该时区的偏移量并将该偏移量添加到 GMT 以获得本地时间。
回答by Kenneth Spencer
If you want to calculate the offset of a time zone such as America/Vancouver from UTC you can do it as follows:
如果要计算时区（例如美国/温哥华）与 UTC 的偏移量，可以按如下方式进行：
SELECT (unix_timestamp() - unix_timestamp(convert_tz(now(), 'Etc/UTC', 'America/Vancouver'))) / 3600 as offset;
For this to work you will first need to load the time zone information into mysql as outlined here: http://dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html
为此，您首先需要将时区信息加载到 mysql 中，如下所述：http: //dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html
回答by ColinM
SELECT TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), NOW());
SELECT TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), NOW());
If the server's timezone is PST this will return -8.
如果服务器的时区是 PST，这将返回-8.
SELECT TIMESTAMPDIFF(SECOND, NOW(), UTC_TIMESTAMP());
SELECT TIMESTAMPDIFF(SECOND, NOW(), UTC_TIMESTAMP());
Add the result of the above to any unix timestamp if you want to compare it to MySQL DateTimes.
如果要将上述结果与 MySQL DateTimes 进行比较，请将上述结果添加到任何 unix 时间戳。
回答by Jon Skeet
The offset will depend on the time that you're interested in - for instance, I'd currently have an offset of one hour from UTC, but during the winter my offset would be zero.
偏移量将取决于您感兴趣的时间 - 例如，我目前与 UTC 有一个小时的偏移量，但在冬天，我的偏移量为零。
Judging by the docs page on MySQL time zone support, you want to use the convert_tzfunction. If you're trying to convert UTC to local time, pass in "Etc/GMT+0" as the "from" time zone, and "Asia/Calcutta" as the "to".
从MySQL time zone support 上的文档页面判断，您想要使用该convert_tz功能。如果您尝试将 UTC 转换为本地时间，请将“Etc/GMT+0”作为“发件人”时区，“亚洲/加尔各答”作为“收件人”。
回答by Shibu
SELECT TIME_FORMAT(TIMEDIFF(NOW(), CONVERT_TZ(NOW(), 'Asia/Calcutta', 'UTC')), '%H:%i') AS offset;
Giving
给予
+--------+
| offset |
+--------+
| 05:30 |
+--------+
回答by drewish
I mixed up @ColinM and @Kenneth Spencer's answers because I wanted the offset of an arbitrary timezone in hours:
我混淆了@ColinM 和@Kenneth Spencer 的答案，因为我想要以小时为单位的任意时区的偏移量：
TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), CONVERT_TZ(UTC_TIMESTAMP(), "UTC", "America/Denver"))
-
——
SELECT ROUND(TIMESTAMPDIFF(MINUTE, UTC_TIMESTAMP(), CONVERT_TZ(UTC_TIMESTAMP(), "Etc/UTC", "Asia/Kolkata"))/60,1)
As Kenneth pointed out you'll need to load the timezone info: http://dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html
正如肯尼斯指出的那样，您需要加载时区信息：http: //dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html
But then you can do fun things like find the offset for multiple timezones at once:
但是你可以做一些有趣的事情，比如一次找到多个时区的偏移量：
SELECT Name,
TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), CONVERT_TZ(UTC_TIMESTAMP(), "Etc/UTC", Name)) as Offset
FROM mysql.time_zone_name
WHERE (Name IN ('America/Vancouver', 'Etc/UTC', 'America/Denver'))
Giving:
给予：
+-------------------+--------+
| Name | Offset |
+-------------------+--------+
| America/Denver | -6 |
| America/Vancouver | -7 |
| Etc/UTC | 0 |
+-------------------+--------+
回答by tetranz
I wonder if @ColinM's answer above is safe. Does it read the clock once or twice? Is there any possibility that UTC_TIMESTAMP() and NOW() could be one second apart? I don't know but this would avoid that.
我想知道@ColinM 上面的回答是否安全。它读时钟一次还是两次？UTC_TIMESTAMP() 和 NOW() 是否有可能相隔一秒？我不知道，但这会避免这种情况。
SET @now = now();
SET @utc = CONVERT_TZ(@now, 'SYSTEM', '+00:00');
SELECT TIMESTAMPDIFF(MINUTE, @utc, @now);
回答by Charles Himmer
SET time_zone = '-07:00';
You can just pass in an offset
你可以只传递一个偏移量
http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html
http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html
回答by Eric
DATEDIFF(NOW(), UTC_TIMESTAMP())
