在PHP/MySQL中将日期时间存储为UTC

Question

在PHP/MySQL中将日期时间存储为UTC

phpmysqltimezone

50

我发现无论在哪里搜索有关将时间转换为用户时区的信息，最好的方法似乎是将日期和时间存储在UTC中，然后只需将用户的时区偏移量添加到此时间即可。

如何将日期存储在UTC时间中？我使用MySQL DATETIME字段。

在我的PHP代码中向MySQL添加新记录时，我会使用now()插入到MySQL DATETIME中。

我需要使用与now()不同的内容来存储UTC时间吗？

- JasonDavis

1

如果您选择这条路线，请不要忘记考虑夏令时——即时区偏移并非对于给定用户的位置始终保持不变。 - Jim Lewis

1

是的，我已经阅读了很多关于在PHP中处理时区的混乱和矛盾的内容，似乎这是PHP需要改进的事情之一。 - JasonDavis

1

对于那些正在寻找更多信息的人，我链接了这些精彩文章:http://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time和http://infiniteundo.com/post/25509354022/more-falsehoods-programmers-believe-about-time-wisdom - Luke H

7个回答

41

我建议将日期插入到UTC时区。这样做将在未来避免许多因夏令时问题而引起的麻烦。

INSERT INTO abc_table (registrationtime) VALUES (UTC_TIMESTAMP())

当我查询我的数据时，我使用以下的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');
}
?>

你可以用可用的PHP时区之一替换DateTimeZone的值。

- Haluk

2

+1 但是你需要转义时区字面量，即 ." \U\T\C"，以避免与当前或未来的格式字符发生冲突。 - webbiedave

1

这个伟大的解决方案处理时间戳字段，但如果表存储来自客户端的日期和时间，是否有类似优雅的解决方案？即当存储日期时间时，日期时间文字来自客户端（使用客户端的时区）。 - adbie

1

DateTime 构造函数接受第二个参数作为时区。例如：$date = new DateTime('2000-01-01', new DateTimeZone('UTC')); 您可以在某个地方保留 UTC 时区对象的静态副本，这样您就不必一直重新构建它。 - mpen

添加一个答案，其中包含有关其工作原理以及如何插入来自PHP的日期的一些详细信息。 - yannick1976

10

NOW()函数返回的是正在运行你的数据库系统的时间（包括时区偏移量）。如果想要获取UTC日期/时间，你应该使用MySQL参考手册中所描述的UTC_TIMESTAMP()函数。

- user156676

7

https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp

引用上述链接中的所有答案以防删除：

为了配合 @ypercube 的评论，CURRENT_TIMESTAMP 作为 UTC 存储但作为当前时区检索，您可以使用 --default_time_zone 选项来影响服务器的时区设置以进行检索。这使得您的检索始终处于 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)

您可以动态设置此内容：

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)

或者永久地添加到你的 my.cnf 文件中：

[mysqld]
**other variables**
default_time_zone='+00:00'

重启服务器，您将看到更改：

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)

- TheFrost

2

正如 @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();

实际上，PHP的datetime没有与之关联的时区。传递给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时间的字符串。

然后，当你读取日期时，必须告诉PHP刚刚检索到的日期的时区是UTC。使用Haluk答案中的代码即可。

- yannick1976

0

随机：使用UTC_TIMESTAMP（6）命令可获取带有6位微秒的时间。
适用于MySQL 5.7+。

- Rahim Khoja

0

根据（https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html）在连接时设置时区。

SET TIME_ZONE = 'UTC';

此后所有日期时间函数应该使用UTC。

请避免使用TIMESTAMP()，因为它不能保存Y2038。

- theking2

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Remus Rusanu · Accepted Answer

MySQL: UTC_TIMESTAMP() 返回当前的UTC日期和时间，以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS.uuuuuu格式的值，取决于函数在字符串或数值上下文中的使用方式。

PHP: gmdate() 同时，PHP中使用date_default_timezone_set()来设置脚本的当前时区。您可以将其设置为客户端时区，以便所有格式化函数返回其本地时间。

说实话，我在让这个工作起来方面遇到了很多困难，总是会遇到一些意想不到的问题。例如，从MySQL返回的时间信息没有格式化为'UTC'，所以如果你不小心，strtotime会将其转换为本地时间。我很想知道是否有人对这个问题有一个可靠的解决方案，一个在日期穿越媒体边界（HTTP->PHP->MySQL和MySQL->PHP->HTTP）时不会出错的解决方案，同时考虑到XML和RSS/Atom。