在不同的时区处理PHP服务器和MySQL服务器

For those of us who use standard shared hosting packages, such as GoDaddy or Network Solutions, how do you handle datetime conversions when your hosting server (PHP) and MySQL server are in different time zones?

对于我们这些使用标准共享主机软件包的用户，例如GoDaddy或Network Solutions，当您的托管服务器（PHP）和MySQL服务器位于不同的时区时，如何处理日期时间转换？

Also, does anybody have some best practice advice for determining what time zone a visitor to your site is in and manipulating a datetime variable appropriately?

此外，是否有人提供了一些最佳实践建议，以确定您网站的访问者所处的时区，并适当地操纵日期时间变量？

5 个解决方案

#1

As of PHP 5.1.0 you can use date_default_timezone_set() function to set the default timezone used by all date/time functions in a script.

从PHP 5.1.0开始，您可以使用date_default_timezone_set（）函数来设置脚本中所有日期/时间函数使用的默认时区。

For MySql (quoted from MySQL Server Time Zone Support page)

对于MySql（引自MySQL服务器时区支持页面）

Before MySQL 4.1.3, the server operates only in the system time zone set at startup. Beginning with MySQL 4.1.3, the server maintains several time zone settings, some of which can be modified at runtime.

在MySQL 4.1.3之前，服务器仅在启动时设置的系统时区中运行。从MySQL 4.1.3开始，服务器维护多个时区设置，其中一些可以在运行时修改。

Of interest to you is per-connection setting of the time zones, which you would use at the beginning of your scripts

您感兴趣的是时区的每个连接设置，您将在脚本的开头使用它们

SET timezone = 'Europe/London';

As for detecting the client timezone setting, you could use a bit of JavaScript to get and save that information to a cookie, and use it on subsequent page reads, to calculate the proper timezone.

至于检测客户端时区设置，您可以使用一些JavaScript来获取并将该信息保存到cookie，并在后续页面读取时使用它来计算正确的时区。

//Returns the offset (time difference) between Greenwich Mean Time (GMT) 
//and local time of Date object, in minutes.
var offset = new Date().getTimezoneOffset(); 
document.cookie = 'timezoneOffset=' + escape(offset);

Or you could offer users the chioce to set their time zones themselves.

或者您可以为用户提供自己设置时区的权限。

#2

Store everything as UTC. You can do conversions at the client level, or on the server side using client settings.

将所有内容存储为UTC。您可以使用客户端设置在客户端级别或服务器端进行转换。

php - date

php - 约会

mysql - utc-timestamp

#3

RE the answer from Željko Živković, timezone descriptors like 'Europe/London' only work if the mySQL admin has added the timezone tables to the system, and keeps them updated.

来自ŽeljkoŽivković的答案，如果mySQL管理员已将时区表添加到系统中，并且保持更新，那么像“欧洲/伦敦”这样的时区描述符才有效。

Otherwise you are limited to numeric offsets like '-4:00'. Fortunately the php date('P') format provides it (as of 5.1.3)

否则，您只能使用“-4：00”之类的数字偏移。幸运的是，php日期（'P'）格式提供了它（从5.1.3开始）

So in say an app config file you might have

所以说你可能有一个app配置文件

define('TZ', 'US/Pacific');
....
if (defined('TZ') && function_exists('date_default_timezone_set')) {
    date_default_timezone_set(TZ);
    $mdb2->exec("SET SESSION time_zone = " . $mdb2->quote(date('P')));
}

This means PHP and mySQL will agree on what timezone offset to use.

这意味着PHP和mySQL将就使用什么时区偏移达成一致。

Always use TIMESTAMP for storing time values. The column is actually stored as UNIX_TIME (epoch) but implicitly converted from current time_zone offset when written, and back when read.

始终使用TIMESTAMP存储时间值。该列实际上存储为UNIX_TIME（epoch），但在写入时从当前time_zone偏移量隐式转换，并在读取时返回。

If you want to display times for users in other time zones, then instead of a global define(), set their given timezone in the above. TIMESTAMP values will be automatically converted by mySQL by the time your app sees the result set (which sometimes can be a problem, if you need to actually know the original timezone of the event too then it needs to be in another column)

如果要显示其他时区中用户的时间，则不要使用全局define（），而是在上面设置他们的给定时区。当应用程序看到结果集时，mySQL会自动转换TIMESTAMP值（有时可能会出现问题，如果您需要实际知道事件的原始时区，那么它需要在另一列中）

and as far as, "why not just store all times as int's", that does lose you the ability to compare and validate dates, and means you always have to convert to date representation at the app level (and is hard on the eyes when you are looking at the data directly - quick, what happened at 1254369600?)

而且，“为什么不把所有时间都存储为int”，这确实使你失去了比较和验证日期的能力，这意味着你总是必须在应用程序级别转换为日期表示（并且当时很难看到你正在直接查看数据 - 快速，1254369600发生了什么？）

#4

I save all my dates as a bigint due to having had issues with the dateTime type before. I save the result of the time() PHP function into it, now they count as being in the same timezone :)

由于之前遇到过dateTime类型的问题，我将所有日期保存为bigint。我将time（）PHP函数的结果保存到它中，现在它们被视为在同一时区:)

#5

In php set timezone by in the php.ini file: ini_set("date.timezone", "America/Los_Angeles");

在php设置时区中的php.ini文件：ini_set（“date.timezone”，“America / Los_Angeles”）;

or in particular page you can do like: date_default_timezone_set("America/Los_Angeles");

或者你可以这样做的特定页面：date_default_timezone_set（“America / Los_Angeles”）;

In mysql you can do like: SET GLOBAL time_zone = 'America/Los_Angeles';

在mysql中你可以这样做：SET GLOBAL time_zone ='America / Los_Angeles';

#1