支持用户时区 [英] Support user time zones

问题描述

我允许我的用户向我的Web应用添加事件.用户可以来自世界各地.

I allow my users to add events to my webapp. Users can originate from all around the world.

我当前的想法是自动确定用户的时区(使用IP定位API)并将其插入到用户表中(我也会允许他们进行更改).

My current idea is to determine the user's time zone automatically (using an IP to location API) and insert it into the users table (I will also allow them to change it).

在事件表上，我将在UTC中插入事件开始日期/结束日期.

On the events table I will insert the event start date/end date in UTC.

然后，每当需要显示事件信息时，我都会从表中获取事件开始日期/结束日期，并根据用户的时区进行计算.

Then, whenever I need to display the event info, I would take the event start date/end date from the table and do the calculation against the user's timezone.

这被认为是一种很好的做法还是有更好的方法呢?

Is that considered as good practice or is there a better way to do that?

在执行此操作时我应该注意的什么?

Anything I should be aware of when doing this?

谢谢

推荐答案

是的，这确实是一个好方法.还请记住，如果在MySQL中使用TIMESTAMP类型，则MySQL将为您处理时区转换.当您向数据库中插入新的日期/时间时，MySQL会将其从连接的时区转换为UTC(TIMESTAMP始终存储在UTC中).当您从数据库中检索TIMESTAMP字段时，MySQL会将其转换回连接的时区.

Yes, that is indeed a good way. Also keep in mind that if you use the TIMESTAMP type in MySQL, MySQL handles the timezone converting for you. When you insert new dates/times to the database, MySQL converts it from the connection's timezone to UTC (TIMESTAMP is always stored in UTC). When you retrieve a TIMESTAMP field from database, MySQL converts it back to the connection's timezone.

因此，如果您在MySQL中使用TIMESTAMP字段，那么您要做的就是在每个页面的开头将用户的时区告知MySQL.您可以这样做:

So if you use TIMESTAMP fields in MySQL, all you need to do is tell the user's timezone to MySQL at start of each your page. You do so by:

SET time_zone = 'Europe/Helsinki'

您还可以使用数字时区:

You can also use numeric timezones:

SET time_zone = '+02:00'

请记住，您可能需要先将tzinfo安装到MySQL，但这很简单(尽管仅适用于非数字版本).以下是有关如何执行此操作的信息: http://dev.mysql. com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html

Keep in mind that you might need to install the tzinfo to MySQL first, which is trivial though (only for the non-numeric version though). Here's information about how to do it: http://dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html

简而言之，这是重要的部分:

In a nutshell, this is the important part:

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

以下是其工作方式的示例:

Here's an example of how it works:

mysql> CREATE TEMPORARY TABLE test(foo TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> SET time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES ('2011-02-03 16:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> SET time_zone = '+02:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT foo FROM test;
+---------------------+
| foo                 |
+---------------------+
| 2011-02-03 18:00:00 |
+---------------------+
1 row in set (0.00 sec)

如果您不使用TIMESTAMP字段，例如您使用DATETIME(它也支持更广泛的日期范围)，那么您只需要确保始终在UTC中插入日期即可；我总是通过将连接的时区设置为+00:00来做到这一点.然后，您可以使用PHP中的视图助手将日期时间转换为用户的时区，这与PHP的 setTimezone函数.最后一个链接中有一个示例.要使用此方法，必须确保将PHP也设置为使用UTC作为其默认时区，您可以使用以下方法进行此操作:

If you don't use TIMESTAMP field, eg. you use DATETIME (which also supports wider range of dates), then you just need to make sure you always insert dates in UTC; I do this by always setting connection's timezone to +00:00. Then you can have a view helper in PHP that converts the datetime to the user's timezone, which is quite trivial to do with PHP's DateTime class and setTimezone function. There's an example in the last link. To use this method, you must make sure PHP is also set to use UTC as its default timezone, which you can do with this:

date_default_timezone_set('UTC');

无论使用哪种方法，都应始终注意以下事实:

Whichever method you use, you should always be aware of these facts:

• PHP和MySQL连接的时区应始终设置为相同的值，以便彼此保持一致
• 通常将TIMESTAMP和DATETIME类型相互混合是一个坏主意
• 如果使用TIMESTAMP类型，请将时区设置为用户的时区
• 如果使用DATETIME类型，请将时区设置为UTC并在PHP中处理时区转换

• The PHP and MySQL connection's timezones should always be set to the same value so they're consistent with each other
• Generally it's a bad idea to mix TIMESTAMP and DATETIME types with each other
• If you use TIMESTAMP type, set the timezone to the user's timezone
• If you use DATETIME type, set the timezone to UTC and handle timezone convertions in PHP

这篇关于支持用户时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！