MySQL：获取特定时区的本地时间

Question

这是一个简单版本的表格 users：

+--------------+-------------------+
|      id      |     timezone      |
+--------------+-------------------+
|       1      | 'Europe/Helsinki' |
|       2      |  'Europe/Paris'   |
+--------------+-------------------+

我想知道这些用户的本地时间（根据他们的时区），以便我可以选择那些对他们来说是下午4点的用户。

我使用的是LAMP堆栈，但我希望仅使用MySQL完成这个任务（而不是选择所有用户并在PHP循环中运行它们）。

- aaaaaa

---

为什么不使用CONVERT_TZ函数？http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz - Nin

因为我显然是个傻瓜。明知我的MySQL服务器在法国，SELECT CONVERT_TZ( NOW( ) , 'Europe/Paris', 'America/New_York' ) 完全可以正常工作！请在此问题中添加一个答案，说明这一点，我会接受它。 - aaaaaa

我经常在星期五下午有这种感觉 :) - Nin

Answer 1

使用 CONVERT_TZ 函数来实现： http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

SELECT * FROM users WHERE hour(CONVERT_TZ(now(), server_tz, `timezone`))=16

- Nin

---

我不知道"server_tz"是什么。只需使用@@session.time_zone，即可为您提供连接上当前时区设置。 - Garr Godfrey

Answer 2

您可以使用set time_zone更改时区：

mysql> set time_zone='Europe/Helsinki';
mysql> select now();
2012-09-21 16:15:06
mysql> set time_zone='Europe/Paris';
mysql> select now();
2012-09-21 15:15:40

使用这个功能，你可以定义一个函数，返回用户所在时区的当前时间：

create function current_time_in_tz(tz varchar(40)) returns datetime 
begin
set @old_tz = @@session.time_zone;
set time_zone=tz;
set @now = now();
set time_zone=@old_tz;
return @now;
end

select id, current_time_in_tz(timezone) from users;

请注意，DATE、TIME 和 DATETIME 值不依赖于时区，因此这些类型的列中的值在查询时不会自动调整。而 TIMESTAMP 的值会被调整。

mysql> create temporary table tbl (dt datetime, ts timestamp);
mysql> insert into tbl values (now(),now());
mysql> select * from tbl;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2012-09-21 15:21:56 | 2012-09-21 15:21:56 |
+---------------------+---------------------+
mysql> set time_zone='Europe/Helsinki';
mysql> select * from tbl;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2012-09-21 15:21:56 | 2012-09-21 16:21:56 |
+---------------------+---------------------+

如果set time_zone出现以下错误：

ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/Helsinki'

您需要使用如下命令将时区信息加载到MySQL中：

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

更多信息请参见http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html。该网站提供有关MySQL数据库的时区支持的详细信息。

- Joni

---

我已经阅读了手册的那一部分，但它并没有帮助我。我需要在单个查询中处理多个时区，我无法通过声明连接/服务器/系统变量来实现。 - aaaaaa

你可以创建一个函数来设置变量，并在查询中调用该函数...请查看更新。 - Joni

那个方法可以行，但是@nin的回答才是我想要的。不过还是谢谢你！ - aaaaaa

哇，我不知道有convert_tz这个函数，之前还以为没有这样的函数存在！ - Joni

Answer 3

比起Nin的答案，更通用的解决方案（不依赖于服务器时区）是：

SELECT * FROM users WHERE hour( CONVERT_TZ(UTC_TIMESTAMP(), 'UTC', timezone) )=16

如果MySQL有一个类似NOW_TZ(timezone)的函数就好了。

- Mark1