PHP，MySQL和时区

本文介绍了PHP，MySQL和时区的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我试图在我的应用程序中集成一个时区系统，我真的很努力地避免使时区感知的应用程序到目前为止 - 但它的强制性要求现在没有选择。 TimeZones它刚刚超过我的头。我已经在PHP和其他网站上阅读了几个主题，包括但不限于SO。但是我永远不会得到它的挂起。

所以我想知道是否有人可以帮助我在这里:(我想要做的是一个偏好选项在我的应用程序中，允许用户从选择菜单中选择自己的时区，但应用程序也应该能够为每个用户设置/选择相应的DST。

请我相信这将帮助那些仍然在努力争取时区的人，所以请提供尽可能详细的解释，即使你必须考虑我一个完整的dumbo / noob。

为赏金编辑

我正在添加赏金对于这个问题，因为我真的需要一个关于编写PHP / MySQL应用程序的时区的一个很好的规范性问题（因此我也添加了MySQL标签），我已经从许多地方找到了，但是它会很好查尔斯的回答很棒，但是我还是觉得它的缺点有些，这里有一些事情gt：

• 如何从PHP DateTime 将数据库中的时间存储在数据库中对象
• 应该将它们存储在 DATETIME 或 TIMESTAMP ？
• 我们是否需要担心MySQL的时区？ DATE ？
• 如何使用 NOW（）插入值。这些需要在插入之前或之后转换？
• 是否有必要设置MySQL使用的时区？如果是这样，怎么办？应该持续执行还是每个HTTP请求？它必须设置为UTC或可以是其他的吗？或者服务器的时间是否足够？
• 如何从MySQL中检索值并将其转换为 DateTime 对象。将它直接放入 DateTime :: __ construct（）就足够了，或者我们需要使用 DateTime :: createFromFormat（） ？
• 何时转换为当地时间，为什么？有没有时间，我们希望在之前将其转换为回到用户（例如，与另一个DateTime对象或静态值进行比较）？
<我们有时间需要担心夏令时间（DST）吗？为什么或为什么不？
• 如果有人先前插入数据（例如使用 NOW（））而不用担心时间段，以确保一切都保持一致？
• 其他任何你认为有人应该注意

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

解决方案

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

奖金所有者添加的几乎所有问题都与MySQL和PHP数据时间应如何交互，时区背景

MySQL还有时区支持，这意味着智能必须是PHP端。

• 设置您的MySQL 连接时区到UTC，如上面的链接所述。这将导致MySQL处理的所有datetimes（包括 NOW（））被正常处理。
• 始终使用 DATETIME ，切勿使用 TIMESTAMP ，除非您非常明确地要求在 TIMESTAMP 中的特殊行为。这比以前不那么痛苦。
  • 如果你存储Unix历元时间为整数，具有，例如用于遗留目的。时代是UTC。
  • MySQL的首选日期时间格式是使用PHP日期格式字符串 Ymd H：i：s
• 将PHP数据时间转换为UTC，将其存储在MySQL中，这是一个简单的事情，如下所示
• 从MySQL返回的数据可以安全地交给PHP DateTime构造函数。一定要传入UTC时区！
• 不久之后，将PHP DateTime转换为echo 上的用户本地时区。幸运的是，DateTime比较和其他DateTimes的数学计算将考虑每个时区的时区。
• 您仍然可以使用PHP提供的DST数据库。让您的PHP和OS补丁保持最新！让MySQL处于幸福的UTC状态，以消除一个潜在的DST烦恼。

最后一件事是doozy：

• 如果他们以前插入了数据（例如使用 NOW（））而不用担心时区确保一切保持一致，那么他们应该怎么办？

这是一个真正的烦恼。其他答案之一指出了MySQL的 CONVERT_TZ ，虽然我亲自在选择和更新期间通过在服务器本机和UTC时区之间跳转来完成，因为我是这样的硬核。

应用程序也应该能够为每个用户设置/选择DST。 >

您不需要，不应该在现代时代这样做。

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

您可以将DateTimeZone与 DateTime 为一些简单而强大的功能。您可以在UTC 全部 >默认情况下，并将其转换为显示的用户时区。

// UTC默认 date_default_timezone_set（'UTC'）; //注意缺少使用此时间戳指定的时区。 $ nowish = new DateTime（'2011-04-23 21:44:00'）; echo $ nowish-> format（'Y-m-d H：i：s'）; // 2011-04-23 21:44:00 //假装我们在美国西海岸。 //这将是现在的PDT，UTC-7 $ la = new DateTimeZone（'America / Los_Angeles'）; //更新DateTime的时区... $ nowish-> setTimeZone（$ la）; //并显示结果 echo $ nowish-> format（'Y-m-d H：i：s'）; // 2011-04-23 14:44:00

通过使用这种技术，系统将自动为用户选择正确的DST设置，而不问用户是否他们当前处于DST。

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

$ dt = new DateTime（'now '，new DateTimeZone（'UTC'））; foreach（DateTimeZone :: listIdentifiers（）as $ tz）{ $ dt-> setTimeZone（new DateTimeZone（$ tz））; echo $ tz，'：'，$ dt->格式（'Y-m-d H：i：s'），\\\ }

您可以通过使用一些客户端魔法大大简化选择过程。 Javascript具有多样但功能齐全的日期类，使用标准方法获取UTC偏移量（分钟）。您可以使用此来缩小可能的时区列表，盲目假设用户的时钟是正确的。

让我们比较这个方法来做自己。您需要实际执行日期数学每次您操纵日期时间，除了推动用户的选择，他们不会真正关心。这不仅仅是次优，而是蝙蝠鸟疯狂。迫使用户表示何时需要DST支持是要求麻烦和混乱。

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

tl; dr ：使用现代工具集，免除日期数学的恐怖。向用户显示名为时区的列表。 将您的日期存储在UTC 中，不会受到DST的任何影响。将数据时间转换为显示的用户选定的指定时区，而不是更早。

按要求，这里是可用时区的循环，以分钟显示其GMT偏移量。我在这里选择了几分钟，表明一个不幸的事实：并不是所有的偏移都在整个小时！有些实际上在DST而不是整整一个小时内改为半小时。 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ p> $ 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）; //是的，真的 echo $ tz，'：'， $ dt->格式（'Ymd H：i：s'），'，offset ='， / 60），minutes\\\; }

I am trying to integrate a timezone system in my app, i've really tried hard on avoiding making timezone-aware apps upto now - but its a mandatory requirement now so got no choice. TimeZones it just goes over my head. I've read several topics on PHP and also other sites including but not limited to SO. But i never could get the hang of it.

So i was wondering if some one can help me out here :( What i'm looking to make is a preference option in my app to allow users to choose their own timezones from a select menu but the app should also be able to SET/Choose the DST accordingly itself for each user.

Please i'm sure this will help others who are still striving to get the hang of the timezones, so please provide as much detailed explanation as possible, even if you have to consider me a complete dumbo/noob.

Edit for bounty:

I am adding a bounty to this question because I really thing we need a good canonical question about time zones when writing PHP/MySQL apps (thus I'm also adding the MySQL tag). I have found things from many places, but it would be good to have it all together. Charles' answer is great, but I still feel it's lacking somewhat. Here are some things I thought of:

• How to store the times in the database from a PHP DateTime object
• Should they be stored in DATETIME or TIMESTAMP? What are the benefits or caveats for each?
• Do we ever need to worry about time zones with MySQL DATE?
• How to insert values using NOW(). Do these need to be converted somehow either before or after the insert?
• Is it necessary to set the time zone used by MySQL? If so, how? Should it be done persistently or upon every HTTP request? Does it have to be set to UTC or can it be anything else? Or is the server's time sufficient?
• How to retrieve values from MySQL and convert them to a DateTime object. Will putting it straight into DateTime::__construct() suffice or do we need to use DateTime::createFromFormat()?
• When to convert to local time and why. Is there ever a time that we would want to convert it before it is echoed back to the user (e.g. to compare to another DateTime object or a static value)?
• Is there ever a time we need to worry about Daylight Savings Time (DST)? Why or why not?
• What should someone do if they have previously inserted data (e.g. using NOW()) without worrying about the time zone to make sure everything stays consistent?
• Anything else you think of that someone should look out for

If possible, try to separate it into logical sections to make it easier for future users to find the information. Be sure to provide code examples where necessary.

解决方案

This answer has been updated to accomodate the bounty. The original, unedited answer is below the line.

Almost all of the question points added by the bounty owner are in relation to how MySQL and PHP datetimes should interact, in the context of timezones.

MySQL still has pathetic timezone support, which means that the intelligence has to be PHP-side.

• Set your MySQL connection timezone to UTC as documented in the link above. This will cause all datetimes handled by MySQL, including NOW(), to be handled sanely.
• Always use DATETIME, never use TIMESTAMP unless you very expressly require the special behavior in a TIMESTAMP. This is less painful than it used to be.
  • It's ok to store the Unix epoch time as an integer if you have to, such as for legacy purposes. The epoch is UTC.
  • MySQL's preferred datetime format is created using the PHP date format string Y-m-d H:i:s
• Convert all PHP datetimes to UTC when storing them in MySQL, which is a trivial thing as outlined below
• Datetimes returned from MySQL can be handed safely to the PHP DateTime constructor. Be sure to pass in a UTC timezone as well!
• Convert the PHP DateTime to the user's local timezone on echo, no sooner. Thankfully DateTime comparison and math against other DateTimes will take into account the timezone that each is in.
• You're still up to the whims of the DST database provided with PHP. Keep your PHP and OS patches up to date! Keep MySQL in the blissful state of UTC to remove one potential DST annoyance.

That addresses most of the points.

The last thing is a doozy:

• What should someone do if they have previously inserted data (e.g. using NOW()) without worrying about the time zone to make sure everything stays consistent?

This is a real annoyance. One of the other answers pointed out MySQL's CONVERT_TZ, though I'd personally have done it by hopping between server-native and UTC timezones during selects and updates, 'cause I'm hardcore like that.

the app should also be able to SET/Choose the DST accordingly itself for each user.

You don't need to and should not do this in the modern era.

Modern versions of PHP have the DateTimeZone class, which includes the ability to list named timezones. Named timezones allow the user to select their actual location, and have the system automatically determine their DST rules based on that location.

You can combine DateTimeZone with DateTime for some simple but powerful functionality. You can simply store and use all of your timestamps in UTC by default, and convert them to the user's timezone on display.

// 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

By using this technique, the system will automatically select the correct DST settings for the user, without asking the user whether or not they're currently in DST.

You can use a similar method to render the select menu. You can continually reassign the time zone for the single DateTime object. For example, this code will list the zones and their current times, at this moment:

$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"; }

You can greatly simplify the selection process by using some client-side magic. Javascript has a spotty but functional Date class, with a standard method to get the UTC offset in minutes. You can use this to help narrow down the list of likely timezones, under the blind assumption that the user's clock is right.

Let's compare this method to doing it yourself. You'd need to actually perform date math every single time you manipulate a datetime, in addition to pushing a choice off on the user that they aren't going to really care about. This isn't just sub-optimal, it's bat-guano insane. Forcing users to signify when they want DST support is asking for trouble and confusion.

Further, if you wanted to use the modern PHP DateTime and DateTimeZone framework for this, you'd need to use deprecated Etc/GMT... timezone strings instead of named timezones. These zone names may be removed from future PHP versions, so it'd be unwise to do that. I say all of this from experience.

tl;dr: Use the modern toolset, spare yourself the horrors of date math. Present the user with a list of named time zones. Store your dates in UTC, which won't be impacted by DST in any way. Convert datetimes to the user's selected named time zone on display, not earlier.

As requested, here's a loop over the available time zones displaying their GMT offset in minutes. I selected minutes here to demonstrate an unfortunate fact: not all offsets are in whole hours! Some actually switch half an hour ahead during DST instead of a whole hour. The resulting offset in minutes should match that of Javascript's 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), " minutes\n"; }