PHP, MySQL and Time Zones

Question

我正在尝试在我的应用程序中集成时区系统，到目前为止，我真的很努力地避免制作具有时区意识的应用程序，但这是现在的强制性要求，所以别无选择。时区，它就在我的脑海里。我在PHP.net和其他网站上读过一些主题，包括但不限于SO。但我从来没有掌握过它的窍门。

所以我想知道是否有人能帮我：(我想在我的应用程序中做一个首选项，允许用户从选择菜单中选择自己的时区，但应用程序也应该能够为每个用户设置/选择相应的夏令时

请注意，我相信这将帮助其他仍在努力掌握时区的人，所以请提供尽可能详细的解释，即使你不得不认为我是个十足的傻瓜。

---

编辑赏金：

我为这个问题添加了一个奖励，因为我真的认为在编写PHP/MySQL应用程序时，我们需要一个关于时区的好的规范问题(因此我也添加了MySQL标签)。我从很多地方找到了东西，但如果能把它们放在一起就好了。查尔斯的回答很好，但我还是觉得有些欠缺。以下是我想到的一些事情：

• 如何从PHPDateTime对象将时间存储在数据库中
• 它们应该存储在DATETIME还是TIMESTAMP中？每种方法的好处或注意事项是什么
• 我们是否需要担心MySQLDATE的时区问题
• 如何使用NOW()插入值。这些是否需要在插入之前或之后以某种方式进行转换
• 是否需要设置MySQL使用的时区？如果是，如何？应该持久地执行还是在每次HTTP请求时执行？它必须设置为UTC还是可以设置为其他值？或者服务器的时间足够吗
• 如何从MySQL中检索值并将其转换为DateTime对象。直接将其放入DateTime::__construct()中就足够了吗？还是我们需要使用DateTime::createFromFormat()
• 何时转换为当地时间以及为什么。在将其回显给用户之前(例如，与另一个DateTime对象或静态值进行比较)，我们是否希望将其转换为
• 有没有一段时间我们需要担心夏令时(夏令时)？为什么
• 如果有人之前插入了数据(例如使用NOW())，而不担心时区，该怎么办，以确保一切保持一致
• 你认为有人应该注意的其他任何事情

如果可能，请尝试将其分为逻辑部分，以便将来的用户更容易找到信息。请务必在必要时提供代码示例。

Answer 1

此答案已更新以适应赏金。未经编辑的原始答案在下面。

赏金所有者添加的几乎所有问题点都与MySQL和PHP日期时间在时区上下文中应该如何交互有关。

MySQL仍然有可怜的时区支持，这意味着智能必须在PHP方面。

• 如上面链接中所述，将MySQL连接时区设置为UTC。这将导致MySQL处理的所有日期时间，包括NOW()，都得到合理处理
• 始终使用DATETIME，永远不要使用TIMESTAMP，除非您非常明确地要求TIMESTAMP中的特殊行为。这比以前不那么痛苦了。
  
  • 如果有的话，可以将Unix epoch时间存储为整数，例如用于遗留目的。纪元是UTC
  • MySQL的首选日期时间格式是使用PHP日期格式字符串Y-m-d H:i:s创建的
• 将所有PHP日期时间存储在MySQL中时转换为UTC，这是一件小事，如下所述
• MySQL返回的DateTime可以安全地传递给PHP DateTime构造函数。也要确保通过UTC时区
• 在echo上立即将PHP DateTime转换为用户的本地时区。值得庆幸的是，DateTime与其他DateTimes的比较和数学计算将考虑每个DateTime所处的时区
• 您仍然可以随心所欲地使用PHP提供的DST数据库。让你的PHP和操作系统补丁保持最新！让MySQL处于UTC的幸福状态，以消除一个潜在的DST烦恼

这解决了大多数点。

最后一件事很糟糕：

• 如果有人之前插入了数据(例如使用NOW())，而不必担心时区，该怎么办才能确保一切保持一致

这真是个麻烦。另一个答案指出MySQL的CONVERT_TZ，尽管我个人会在选择和更新时在服务器本地时区和UTC时区之间切换，因为我就是这样的铁杆。

---

应用程序还应该能够为每个用户设置/选择相应的DST。

在现代，你不需要也不应该这样做。

现代版本的PHP有DateTimeZone类，其中包括列出命名时区的功能。命名时区允许用户选择他们的实际位置，并让系统自动根据该位置确定他们的夏令时规则。

您可以将DateTimeZone与DateTime相结合，以获得一些简单但强大的功能。默认情况下，您可以简单地以UTC存储和使用所有时间戳，并将其转换为显示的用户时区。

// UTC default
date_default_timezone_set('UTC');
// Note the lack of time zone specified with this timestamp.
$nowish = new DateTime('2011-04-23 21:44:00');
echo $nowish->format('Y-m-d H:i:s'); // 2011-04-23 21:44:00
// Let's pretend we're on the US west coast.  
// This will be PDT right now, UTC-7
$la = new DateTimeZone('America/Los_Angeles');
// Update the DateTime's timezone...
$nowish->setTimeZone($la);
// and show the result
echo $nowish->format('Y-m-d H:i:s'); // 2011-04-23 14:44:00

通过使用此技术，系统将自动为用户选择正确的夏令时设置，而无需询问用户当前是否处于夏令时。

可以使用类似的方法来渲染选择菜单。您可以不断为单个DateTime对象重新分配时区。例如，此代码将列出区域及其当前时间，此时：

$dt = new DateTime('now', new DateTimeZone('UTC')); 
foreach(DateTimeZone::listIdentifiers() as $tz) {
$dt->setTimeZone(new DateTimeZone($tz));
echo $tz, ': ', $dt->format('Y-m-d H:i:s'), "n";
}

通过使用一些客户端魔术，您可以极大地简化选择过程。Javascript有一个不稳定但功能强大的Date类，它有一个以分钟为单位获取UTC偏移量的标准方法。在盲目假设用户的时钟是正确的情况下，您可以使用它来帮助缩小可能的时区列表。

让我们把这种方法和自己做比较。您需要在每次操作日期时间时执行日期计算，此外还需要向用户推送他们不会真正关心的选项。这不仅仅是次优，这是蝙蝠粪的疯狂。强迫用户表示何时需要DST支持会带来麻烦和混乱。

此外，如果您想使用现代的PHP DateTime和DateTimeZone框架，您需要使用不推荐使用的Etc/GMT...时区字符串，而不是命名的时区。这些区域名称可能会从未来的PHP版本中删除，所以这样做是不明智的。我说这一切都是凭经验。

tl；dr：使用现代工具集，让自己免受日期数学的恐怖。向用户显示命名的时区列表将日期存储在UTC中，它不会受到夏令时的任何影响。将日期时间转换为用户选择的命名时区，而不是更早。

---

根据要求，这里有一个可用时区的循环，以分钟为单位显示其GMT偏移量。我在这里选择了几分钟来证明一个不幸的事实：并不是所有的偏移都在整小时内！有些人实际上在夏令时提前半小时而不是一整小时。以分钟为单位的偏移量应该与Javascript的Date.getTimezoneOffset相匹配。

$utc = new DateTimeZone('UTC');
$dt = new DateTime('now', $utc); 
foreach(DateTimeZone::listIdentifiers() as $tz) {
$local = new DateTimeZone($tz);
$dt->setTimeZone($local);
$offset = $local->getOffset($dt); // Yeah, really.
echo $tz, ': ', 
$dt->format('Y-m-d H:i:s'),
', offset = ',
($offset / 60),
" minutesn";
}

Answer 2

• 如何从PHPDateTime对象将时间存储在数据库中

  SQL-92标准规定，应使用合适的数据类型关键字(例如，日期/时间值的TIMESTAMP)在SQL中传递时态文字，后跟值的字符串表示形式(如果不是默认值，则包含可选的时区偏移量)。

  遗憾的是，MySQL不符合SQL标准的这一部分。如日期和时间文字：

  标准SQL允许使用类型关键字和字符串指定临时文字。

  [deletia]

  MySQL识别这些构造以及相应的ODBC语法：

  [deletia]

  然而，MySQL忽略type关键字，并且前面的每个构造都生成类型为VARCHAR的字符串值'str'。

  文档继续描述MySQL支持的文字格式，特别是没有显式时区偏移。有一个功能请求来修复这个问题，它已经有七年多的历史了，而且看起来不太可能很快推出。

  相反，在服务器和客户端之间交换日期/时间值之前，必须设置会话的time_zone变量。因此，使用PDO:

  1. 连接到MySQL：

     $dbh = new PDO("mysql:dbname=$dbname", $username, $password);
     $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
     

  2. 将会话time_zone设置为DateTime对象的会话：

     $qry = $dbh->prepare('SET SESSION time_zone = ?');
     $qry->execute([$datetime->format('P')]);
     

  3. 从DateTime对象中生成一个合适的文本，并正常传递给MySQL(即作为准备好的语句的参数)。

     如文档中所述，有许多可能的文字格式可供使用。然而，我建议使用'YYYY-MM-DD hh:mm:ss.ffffff'格式的字符串(注意，在5.6之前的MySQL版本中，小数秒将被忽略)，因为它最接近SQL标准；实际上，可以在文本前面加上TIMESTAMP关键字，以确保SQL是可移植的：

     $qry = $dbh->prepare('
     UPDATE my_table
     SET    the_time = TIMESTAMP ?
     WHERE  ...
     ');
     $qry->execute([$datetime->format('Y-m-d H:i:s.u')]);
     

• 它们应该存储在DATETIME还是TIMESTAMP中？每种方法的好处或注意事项是什么？

  PHPDateTime对象应始终存储在TIMESTAMP类型的列中。

  最基本的区别是TIMESTAMP存储时区信息(通过以UTC存储值并根据上面的time_zone变量的要求转换为时区信息)，而DATETIME不存储时区信息。因此，TIMESTAMP可用于表示特定时刻(类似于PHPDateTime对象)，而DATETIME可用于表示日历/时钟上的时间(如照片中)。

  如DATE、DATETIME和TIMESTAMP类型下所述：

  DATETIME类型用于同时包含日期和时间部分的值。MySQL检索并显示'YYYY-MM-DD HH:MM:SS'格式的DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

  TIMESTAMP数据类型用于同时包含日期和时间部分的值。TIMESTAMP的范围为'1970-01-01 00:00:01'UTC至'2038-01-19 03:14:07'UTC。

  MySQL将TIMESTAMP值从当前时区转换为UTC进行存储，并将其从UTC转换回当前时区进行检索。(其他类型(如DATETIME)不会发生这种情况。)默认情况下，每个连接的当前时区是服务器的时间。可以根据每个连接设置时区。只要时区设置保持不变，您就会得到与存储的值相同的值。如果存储TIMESTAMP值，然后更改时区并检索该值，则检索到的值与存储的值不同。发生这种情况的原因是没有将同一时区用于两个方向的转换。当前时区可用作time_zone系统变量的值。有关更多信息，请参阅第10.6节"MySQL Server时区支持"。

  TIMESTAMP数据类型提供了对当前日期和时间的自动初始化和更新。有关更多信息，请参阅第11.3.5节"TIMESTAMP的自动初始化和更新"。

  注意最后一段，这段话经常会吸引MySQL的新手。

  还值得补充的是，如数据类型存储要求中所述，DATETIME值需要8个字节进行存储，而TIMESTAMP值只需要4个字节(基本数据存储格式可在日期和时间数据类型表示中找到)。

• 我们是否需要担心MySQLDATE的时区问题？

  只有时间对时区敏感才有意义。根据定义，无论时区如何，单独的日期都是相同的，因此在使用MySQL的DATE数据类型时无需"担心时区"。

  其推论是，如果一个值对时区敏感，则还必须将其时间存储在TIMESTAMP列中：使用DATE列会导致重要信息的不可逆损失。

• 如何使用NOW()插入值。这些是否需要在插入之前或之后以某种方式进行转换？

  如NOW():中所述

  以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS.uuuuuu格式的值返回当前日期和时间，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。该值以当前时区表示。

  由于">值以当前时区表示"，并且在评估日期/时间值时将使用相同的">current timezone"，因此在使用MySQL的NOW()函数(或其任何别名)时不必担心时区。因此，插入一条记录：

  INSERT INTO my_table (the_time) VALUES (NOW());
  

  注意，如上所述，MySQL对TIMESTAMP列的自动初始化使得在记录插入/更新期间使用NOW()的大多数尝试变得多余。

• 是否需要设置MySQL使用的时区？如果是，如何？应该持久地执行还是在每次HTTP请求时执行？它必须设置为UTC还是可以设置为其他值？或者服务器的时间足够吗？

  上面已经讨论过了。如果需要，可以全局设置MySQL的time_zone变量，从而避免在每次连接时都设置它。有关更多信息，请参阅MySQL Server时区支持。

• 如何从MySQL中检索值并将其转换为DateTime对象。将其直接放入DateTime::__construct()中就足够了吗？还是我们需要使用DateTime::createFromFormat()？

  如复合格式下所述，PHP在DateTime::__construct()中使用的解析器识别的日期/时间格式之一是MySQL的输出格式。

  然而，由于MySQL输出格式不包括时区，因此必须确保通过其可选的第二个参数向DateTime构造函数提供该信息

  $qry = $dbh->prepare('SET SESSION time_zone = ?');
  $qry->execute([$timezone->getName()]);
  $qry = $dbh->query('SELECT the_time FROM my_table');
  $datetime = new DateTime($qry->fetchColumn(), $timezone);
  

  或者，可以让MySQL将时间转换为UNIX时间戳，并从中构造DateTime对象：

  $qry = $dbh->query('SELECT UNIX_TIMESTAMP(the_time) FROM my_table');
  $datetime = new DateTime($qry->fetchColumn());
  

• 何时转换为本地时间以及原因。在将其回显给用户之前(例如，与另一个DateTime对象或静态值进行比较)，我们是否希望将其转换为？

  我不知道你所说的"本地时间"是什么意思(本地给谁？RDBMS？Web服务器？Web客户端？)，但DateTime对象之间的比较将根据需要处理时区转换(PHP在内部以UTC存储值，仅转换为输出)。

• 有没有时间我们需要担心夏令时(DST)？为什么？

  一般来说，如果您遵循上面给出的方法，DST唯一关心的是确保在用户期望的时区中向用户呈现值。

• 如果有人之前插入了数据(例如使用NOW())，而不担心时区，该怎么办才能确保一切保持一致？

  如上所述，NOW()的使用永远不会引起问题。

  如果在会话的time_zone变量设置为不正确的值时，将文字值插入到TIMESTAMP列中，则需要相应地更新这些值。MySQL的CONVERT_TZ()函数可能会有所帮助：

  UPDATE my_table SET the_time = CONVERT_TZ(the_time, '+00:00', '+10:00');
  

Answer 3

如何从PHP DateTime对象将时间存储在数据库中它们应该存储在DATETIME还是TIMESTAMP中？有什么好处还是每个都要注意？

*更新，澄清我的第一段*您还可以将时间戳存储为INT。优点是您知道您在哪个时区存储了您的值，因为时间戳是自Unix Epoch(1970年1月1日00:00:00 GMT)以来以秒为单位测量的当前时间请参阅php文档：http://php.net/manual/en/function.time.php使用64位操作系统，您不必担心2038年的问题：http://en.wikipedia.org/wiki/Year_2038_problem

时间戳更容易用来比较日期时间，在对象和数组中使用更有趣。例如，您可以很容易地将它们用作数组的键。

我们需要担心MySQL DATE的时区吗？

在MySQL中，CURRENT_TIMESTAMP()、CURRENT_IME()、CurrentRENT_DATE()和FROM_UNIXTIME()函数返回连接当前时区中的值，该值可用作TIME_zone系统变量的值。此外，UNIX_TIMESTAMP()假设其参数是当前时区中的日期时间值。http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

如何使用NOW()插入值。这些需要转换吗在插入之前或之后？

如果你使用时间戳，你可以依赖PHP函数，它只是一个整数。

如果您使用日期-时间，函数curdate允许您使用当前日期。http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_curdate

是否需要设置MySQL使用的时区？如果是，如何？应该它是持久地完成还是在每次HTTP请求时完成？一定要这样吗设置为UTC，或者可以是其他值吗？或者是服务器的时间足够的

请参阅http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

如何从MySQL中检索值并将其转换为DateTime对象将其直接放入DateTime:：__construct()是否足够还是我们需要使用DateTime:：createFromFormat()？

同样，如果使用时间戳，会更容易。你知道时间戳时区，你知道何时转换为当地时间以及为什么。DST易于使用时间戳进行管理，请参阅有关时间戳的功能：http://php.net/manual/en/function.mktime.php

我们有没有想过在它之前转换它回显给用户(例如，与另一个DateTime对象进行比较，或静态值)？

我再次认为，时间戳可以让你与日期进行比较，提取你需要的内容并打印你想要的内容。

有没有时间我们需要担心夏令时(DST)？为什么？如果某人以前有插入的数据(例如使用NOW())而不必担心时区确保一切保持一致？

是的，您应该担心是否必须在应用程序中创建约会或会议。我开发了两个应用程序，一个用于临床预约，另一个用于研讨会预约，支持70000多个账户和大量记录。我坚持使用时间戳，它非常容易索引、操作、比较。打印部分仅显示在视图中。

在数据库中使用日期时间是有好处的。如果你必须在sql中直接分析表中的数据，它会更容易阅读，更具"人类可读性"。

我不确定这篇文章会有一个固定的答案，因为这取决于你的需求。时间戳对于操作来说非常容易操作(这是一种实用的方法)。存储方式取决于您的偏好，因为您可以存储日期，稍后仍可以将其转换为时间戳。但根据我的理解，时区是时间戳定义的一部分。