在 PHP/MySQL 中将日期时间存储为 UTC
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1349280/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Storing datetime as UTC in PHP/MySQL
提问by JasonDavis
Everywhere I read about converting time to a user's timezone says that the best method is to store a date and time in UTC then just add the user's timezone offset to this time.
我读到的关于将时间转换为用户时区的任何地方都说最好的方法是以 UTC 存储日期和时间，然后将用户的时区偏移量添加到这个时间。
How can I store a date in UTC time? I use the MySQL DATETIME field.
如何以UTC时间存储日期？我使用 MySQL DATETIME 字段。
When adding a new record to MySQL in my PHP code I would use now()to insert into MySQL DATETIME.
在我的 PHP 代码中向 MySQL 添加新记录时，我将用于now()插入 MySQL DATETIME。
Would I need to use something different than now()to store UTC time?
我需要使用now()与存储 UTC 时间不同的东西吗？
采纳答案by Remus Rusanu
MySQL: UTC_TIMESTAMP()
MySQL： UTC_TIMESTAMP()
Returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context
返回当前 UTC 日期和时间作为 'YYYY-MM-DD HH:MM:SS' 或 YYYYMMDDHHMMSS.uuuuuu 格式的值，具体取决于函数是在字符串还是数字上下文中使用
PHP: gmdate()
PHP： gmdate()
Also PHP date_default_timezone_set()is used in PHP to set the current time zone for the script. You can set it to the client time zone so all the formatting functions return the time in his local time.
在 PHP 中date_default_timezone_set()还使用 PHP 来设置脚本的当前时区。您可以将其设置为客户端时区，以便所有格式化函数都返回本地时间的时间。
In truth though I had a hard time getting this to work and always stumble into some gotcha. Eg. time information returned from MySQL is not formatted as 'UTC' so strtotimetransforms it into a local time if you are not careful. I'm curious to hear if someone has a reliablesolution for this problem, one that doesn't break when dates traverse media boundaries (HTTP->PHP->MySQL and MySQL->PHP->HTTP), also considering XML and RSS/Atom.
事实上，虽然我很难让它发挥作用，并且总是偶然发现一些问题。例如。从 MySQL 返回的时间信息未格式化为“UTC”，因此如果您不小心，strtotime会将其转换为本地时间。我很想知道是否有人对此问题有可靠的解决方案，当日期跨越媒体边界（HTTP->PHP->MySQL 和 MySQL->PHP->HTTP）时不会中断，同时考虑 XML 和 RSS /原子。
回答by Haluk
I would suggest inserting the date in UTC time zone. This will save you a lot of headaches in the future with daylight saving problems.
我建议在 UTC 时区插入日期。这将为您在将来避免夏令时问题带来很多麻烦。
INSERT INTO abc_table (registrationtime) VALUES (UTC_TIMESTAMP())
When I query my data I use the following PHP script:
当我查询我的数据时，我使用以下 PHP 脚本：
<?php
while($row = mysql_fetch_array($registration)) {
$dt_obj = new DateTime($row['message_sent_timestamp'] ." UTC");
$dt_obj->setTimezone(new DateTimeZone('Europe/Istanbul'));
echo $formatted_date_long=date_format($dt_obj, 'Y-m-d H:i:s');
}
?>
You can replace the DateTimeZonevalue with one of the available PHP timezones.
您可以DateTimeZone使用可用的PHP 时区之一替换该值。
回答by Haluk
NOW()gives you the time (including the timezone offset) of the system running your database. To get UTC date/time you should use UTC_TIMESTAMP()as described in the MySQL Reference Manual.
NOW()为您提供运行数据库的系统的时间（包括时区偏移量）。要获取 UTC 日期/时间，您应该UTC_TIMESTAMP()按照MySQL 参考手册中的说明使用。
回答by TheFrost
https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp
https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp
Quoting all the answer from above link in case of delete:
在删除的情况下引用以上链接中的所有答案：
To go along with @ypercube's comment that CURRENT_TIMESTAMPis stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zoneoption for retrieval. This allows your retrieval to always be in UTC.
要与CURRENT_TIMESTAMP存储为 UTC 但作为当前时区检索的@ypercube 的评论一起使用，您可以使用--default_time_zone检索选项影响服务器的时区设置。这允许您的检索始终使用 UTC。
By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may not be UTC!):
默认情况下，该选项是“SYSTEM”，这是您设置系统时区的方式（可能是也可能不是 UTC！）：
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 16:28:45 |
+---------------------+
1 row in set (0.00 sec)
You can set this dynamically:
您可以动态设置：
mysql> SET @@session.time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | +00:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
Or permanently in your my.cnf:
或永久在您的 my.cnf 中：
[mysqld]
**other variables**
default_time_zone='+00:00'
Restart your server, and you will see the change:
重新启动您的服务器，您将看到更改：
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +00:00 | +00:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 20:27:50 |
+---------------------+
1 row in set (0.01 sec)
回答by yannick1976
As @Haluk suggests, you can store the date as a UTC datetime.
I'm complementing his answer for situations when the date you want to insert comes from PHP code, and adding some details about how it works :
正如@Haluk 所建议的，您可以将日期存储为 UTC datetime。对于您要插入的日期来自 PHP 代码的情况，我正在补充他的回答，并添加了一些有关其工作原理的详细信息：
$pdo = new PDO('mysql:host=mydbname.mysql.db;dbname=mydbname', 'myusername', 'mypassword');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$insertStmt = $pdo->prepare("insert into test (last_modified)"
." values(:last_modified)");
$insertStmt->bindParam(':last_modified', $lastModified, PDO::PARAM_STR);
$lastModified = gmdate("Y-m-d H:i:s");
$insertStmt->execute();
Actually datetimein PHP doesn't have a timezone associated to it. What is passed to PDO is just a string, in one of the formats recognized by MySQL, representing the date in UTC timezone. For example if the date is 2015-10-21 19:32:33 UTC+2:00, the code above just tells MySQL to insert 2015-10-21 17:32:33. gmtdate("Y-m-d H:i:s")gives you the current date in such a format. In any case, all you need to do is build the string representing the date you want to insert in UTC time.
实际上datetime在 PHP 中没有与之关联的时区。传递给 PDO 的只是一个字符串，采用 MySQL 识别的一种格式，表示 UTC 时区中的日期。例如，如果日期是2015-10-21 19:32:33 UTC+2:00，上面的代码只是告诉 MySQL 插入2015-10-21 17:32:33. gmtdate("Y-m-d H:i:s")以这种格式为您提供当前日期。在任何情况下，您所需要做的就是构建表示要在 UTC 时间插入的日期的字符串。
Then, when you read the date, you have to tell PHP that the timezone of the date you just retrieved is UTC. Use the code in Haluk's answer for this.
然后，当您读取日期时，您必须告诉 PHP 您刚刚检索到的日期的时区是 UTC。为此，请使用 Haluk 的答案中的代码。
回答by Rahim Khoja
Random: UTC_TIMESTAMP(6)for time with 6 digits of microseconds.
随机：UTC_TIMESTAMP(6)6 位微秒的时间。
MySQL 5.7+
MySQL 5.7+
