PHP &MySQL:将存储的 TIMESTAMP 转换为用户的本地时区

所以我有一个具有评论功能的网站，其中评论的时间戳存储在 MySQL 数据库中.据我了解，时间戳在存储时转换为 UTC，然后在检索时转换回默认时区.就我而言，我的服务器位于中央夏令时时区 (CDT).

我计划通过输入表单从每个用户那里获取时区.我只是想知道如何将 TIMESTAMP 值转换为用户的时区.

• 首先，我会从 UTC 转换为本地时区吗?还是 CDT 到本地时区?
• 其次，我将如何在 PHP 中执行此操作?我会这样做吗:

<上一页>$userTimezone = new DateTimeZone($userSubmittedTimezoneString);$myDateTime = new DateTime($storedTimestamp, $userTimezone);

...还是不正确?

解决方案

日期/时间/日期时间值在您提供时存储在 MySQL 中.IE.如果您将字符串 2012-04-17 12:03:23 INSERT 插入到 DATETIME 列中，则该值将被存储.它将在内部转换为可能准确也可能不准确的时间戳(见下文)，但是当您再次查询该值时，您将得到相同的值；往返是透明的.

如果您尝试在 SQL 中进行时间计算，可能会出现问题.IE.任何需要 SQL 来考虑时区和/或服务器时间的操作.例如，使用 NOW().对于任何这些操作，应正确设置时区和/或服务器时间..请参阅时区问题.

如果这与您无关并且您只需要在 PHP 中进行计算，您只需要确保您知道要从哪个时区转换到哪个时区.为此，可以方便地将所有时间标准化为 UTC，但这不是必需的，因为从任何时区到任何其他时区的时区转换同样有效，只要您清楚您要从哪个时区转换到哪个时区.

date_default_timezone_set('Asia/Tokyo');//你的参考时区在这里$date = date('Y-m-d H:i:s');/* INSERT $date INTO 数据库 */;$date =/* 从数据库中选择日期 */;$usersTimezone = new DateTimeZone('美国/温哥华');$l10nDate = new DateTime($date);$l10nDate->setTimeZone($usersTimezone);echo $l10nDate->format('Y-m-d H:i:s');

So I have a site with a comments feature where the timestamp of the comment is stored in a MySQL database. From what I understand, the timestamp is converted to UTC when stored, then converted back to the default timezone when retrieved. In my case, my server is in the Central Daylight Time timezone (CDT).

I have a plan to get the timezone from each user via entry form. I just wanted to know how to convert the TIMESTAMP value into the user's timezone.

• First, would I convert from UTC to local timezone? Or CDT to local timezone?
• Secondly, how would I go about doing that in PHP? Would I just do:

$userTimezone = new DateTimeZone($userSubmittedTimezoneString);
$myDateTime = new DateTime($storedTimestamp, $userTimezone);

...or is that not correct?

解决方案

Date/time/datetime values are stored in MySQL as you supply them. I.e. if you INSERT the string 2012-04-17 12:03:23 into a DATETIME column, that's the value that will be stored. It will be converted internally into a timestamp which may or may not be accurate (see below), but when you query for the value again, you'll get the same value back out; the roundtrip is transparent.

Problems may occur if you try to do time calculations inside SQL. I.e. any operation that requires SQL to take the timezone and/or the server time into account. For example, using NOW(). For any of those operations, the timezone and/or server time should be set correctly. See Time Zone Problems.

If that doesn't concern you and you only need to do calculations in PHP, you only need to make sure you know from which timezone to which timezone you want to convert. For that purpose it can be convenient to standardize all times to UTC, but it is not necessary, as timezone conversions from any timezone to any other timezone work just as well, as long as you're clear about which timezone you're converting from and to.

date_default_timezone_set('Asia/Tokyo'); // your reference timezone here

$date = date('Y-m-d H:i:s');

/* INSERT $date INTO database */;

$date = /* SELECT date FROM database */;

$usersTimezone = new DateTimeZone('America/Vancouver');
$l10nDate = new DateTime($date);
$l10nDate->setTimeZone($usersTimezone);
echo $l10nDate->format('Y-m-d H:i:s');