PHP、MySQL 和时区 [英] PHP, MySQL and Time Zones

问题描述

我正在尝试将时区系统集成到我的应用程序中，到目前为止，我一直在努力避免制作时区感知应用程序 - 但现在这是一项强制性要求，因此别无选择.时区它只是超出了我的头脑.我在 PHP.net 和其他网站上阅读了几个主题，包括但不限于 SO.但我始终无法掌握.

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.net 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.

编辑赏金:

我正在为这个问题添加一个悬赏，因为我真的认为在编写 PHP/MySQL 应用程序时我们需要一个关于时区的良好规范问题(因此我还添加了 MySQL 标记).我从很多地方找到了东西，但最好把它们放在一起.查尔斯的回答很好，但我还是觉得有些欠缺.以下是我想到的一些事情:

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:

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

• 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.

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

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 仍然有可怜的时区支持a>，这意味着智能必须在 PHP 端.

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

• 将您的 MySQL 连接时区设置为 UTC，如上面链接中所述.这将导致 MySQL 处理的所有日期时间，包括 NOW()，都被理智地处理.
• 始终使用DATETIME，从不使用TIMESTAMP 除非您非常明确地要求 TIMESTAMP 中的特殊行为.这不像以前那样痛苦了.
  
  • 可以将 Unix 纪元时间存储为整数(如果您有)，例如用于遗留目的.纪元是UTC.
  • MySQL 的首选日期时间格式是使用 PHP 日期格式字符串创建的 Y-m-d H:i:s
  • 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

    这解决了大部分的问题.

    最后一件事很糟糕:

    • 如果有人之前插入过数据(例如使用 NOW())而不必担心时区以确保一切保持一致，他们应该怎么做?

    • 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?

    这是一个真正的烦恼.其他答案之一指出 MySQL 的 CONVERT_TZ，虽然我个人是通过在选择和更新期间在服务器本地和 UTC 时区之间跳转来完成的，因为我是那样的铁杆.

    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.

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

    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.

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

    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.

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

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

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

    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.

    让我们将这种方法与自己做比较.您需要在每次操作日期时间时实际执行日期数学运算，此外还需要为用户提供一个他们不会真正关心的选择.这不仅是次优的，而且是蝙蝠鸟粪的疯狂.强迫用户在需要 DST 支持时表示是自找麻烦和困惑.

    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.

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

    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:使用现代工具集，避免日期数学的恐怖.向用户显示一个命名时区列表.以 UTC 格式存储您的日期，这不会以任何方式受到夏令时的影响.将日期时间转换为用户显示选择的命名时区，而不是更早的时间.

    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.

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

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

    这篇关于PHP、MySQL 和时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！