在mysql数据库中存储PHP支持的时区的最佳实践

Question

我想知道存储PHP的'支持的时区'的最佳实践是什么。我将每个用户的时区存储起来，以便可以在UTC和本地时间之间进行转换。您会将它存储为varchar类型字段中的字符串吗？在这种情况下，最长可能的支持时区字符串是多少？是否有更好的存储方法？简单地存储时区偏移量不是一个选项，因为它不像PHP的datetime对象那样自动考虑夏令时。

- slick1537

---

为什么要把事情搞得那么复杂呢？将名称存储为 VARCHAR(255)，然后就可以了。这样不会占用比必要更多的空间，如果某个时刻出现超过 255 个字符的时区错误，那么再解决这个问题即可。 - Jon

我想这是一个不错的解决方案，但我似乎没有完全理解 VARCHAR 的工作原理。 - slick1537

Answer 1

这几乎是与此问题完全相同的副本：

Proper way to store a timezone in a database?

但是我将解决您提到的少数其他问题：

• PHP时区是IANA时区。

• 只需在varchar中存储名称。这里有一些关于时区名称字段长度的讨论here。 varchar（32）可以工作，但为了安全起见，我会留出一些额外的空间以供未来更改。 255可能过度，但varchar(50)可能是合理的。

• 很多人建议在数据库中存储UTC。有时这是一个好习惯，但我不喜欢它被推荐为你应该总是这样做的事情。使用UTC和使用本地时间都有好处。

• 如果使用UTC，则需要时区名称并在输入和输出上进行本地时间转换。

• 如果使用本地时间，应始终存储时区偏移量。这是由于在DST转换期间可能存在歧义的本地时间。某些数据库具有专门用于此的类型，例如Oracle或Postgres中的TIMESTAMP WITH TIMEZONE或SQL Sever中的DATETIMEOFFSET。不幸的是，MySql没有此类型，因此您将需要两个列。

如果您的数据只记录一次，不再更改（例如记录事件时间），则这两个选项都可行。 UTC具有准备进行数学运算和转换的优点。本地时间具有保留观察者视角的优点。请参见DateTime vs DateTimeOffset，其中涉及.Net，但这些概念在此仍然适用。

如果您将编辑这些时间，则无论如何都需要时区名称，因此最好存储它。您可以决定是否根据记录的时间戳每次存储它，或者只针对每个用户存储一次。您应该考虑如果用户更改其时区会发生什么。它应该适用于所有位置还是仅适用于新记录的条目？

我可以想到一些实际原因不要将其存储为UTC：

• 如果您正在运行一个将输出许多行的报表，并且所需的输出以记录的本地时间为基础，则需要在紧密循环中重复地从UTC进行转换可能会减慢报告的执行时间。

• 有时法律要求精确存储时间。非技术审计员可能不允许任何类型的转换，并将UTC时间视为非法。如果他们查看了一个本地时间加偏移值，他们可能不关心偏移部分，并且会满意于本地时间值。我知道这似乎很傻，但在某些行业和司法管辖区中确实存在这些要求。

• 有时您不想实际上参考单个时间点，而是特定于该时间的本地化表示。假设您在美国周围有餐馆，它们都在早上6:00开门。那么，在太平洋时间和东部时间，这个6:00 AM是不同的时刻。将其存储为UTC可能会导致无效的假设，尤其是在夏令时更改周围。

在大多数情况下，我建议将存储时间转换为协调世界时（UTC）。但是，你需要根据你的特定需求决定哪种方法最适合你。

- Matt Johnson-Pint

Answer 2

将所有时间存储为UTC，然后创建另一个带有时区偏移量的字段。

这实际上不是PHP问题，而是使用时区的数据库最佳实践。始终将数据保存在UTC中。另外再添加一个字段用于存储时区偏移量，例如-10或时区名称，例如'Europe/Amsterdam'。对我来说，我使用Python中的pytz来处理这些内容。

您始终可以恢复DST。

- Tampa

---

在数据库中存储UTC偏移量是一个不好的解决方案，因为它没有考虑到不同时区的夏令时变化。然而，如果您向PHP的日期时间对象传递一个“支持的时区”，它将自动考虑夏令时在不同时区之间的转换。 - slick1537

这就是为什么我建议同时使用时区。这样你就可以恢复了。有些情况下，除了时区之外，还需要存储偏移量。 - Tampa

Answer 3

我通常将日期和时间存储为整数字段中的Unix时间戳。这样，我就知道我从数据库中得到的确切内容。我建议使用此列表中的字符串表示，在文本字段中存储时区，您已经了解这一点。

例如，像这样的名为stackoverflow的表，在名为stackoverflow的数据库中：

+--+----------+-------------+
|id|date_time |time_zone    |
+--+----------+-------------+
|1 |1373212914|Europe/London|
|2 |1373212914|Europe/Rome  |
+--+----------+-------------+

那么您可以像这样为您的DateTime对象进行水合处理：

$dsn = 'mysql:dbname=stackoverflow;host=127.0.0.1';
$user = 'stackoverflow';
$password = 'stackoverflow';

try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sql = 'select date_time, time_zone from stackoverflow';
$statement = $dbh->prepare($sql);
$statement->execute();
$results = $statement->fetchAll();
foreach($results as $result){
    $datetimes[] = (new \DateTime())->setTimestamp((int)$result['date_time'])->setTimezone(new \DateTimeZone($result['time_zone']));
}

var_dump($datetimes);

这将产生以下输出：-

array (size=2)
  0 => 
    object(DateTime)[3]
      public 'date' => string '2013-07-07 17:01:54' (length=19)
      public 'timezone_type' => int 3
      public 'timezone' => string 'Europe/London' (length=13)
  1 => 
    object(DateTime)[4]
      public 'date' => string '2013-07-07 18:01:54' (length=19)
      public 'timezone_type' => int 3
      public 'timezone' => string 'Europe/Rome' (length=11)

检查表格会显示两个条目具有相同的时间戳，即2013-07-07 16:01:54 UTC，但在发送给客户端时，在存储的时区中正确表示。

- vascowhite