如何让 UNIX_TIMESTAMP 在不同时区时不偏移日期时间字段？

Question

我建立了一个小型论坛，用户可以在其中发布消息。我的服务器位于美国，但论坛的用户群位于台湾（+15 小时）。

当有人发布到表单时，我将时间以 YYYY-MM-DD HH:MM:SS 格式存储在 mySQL 数据库中。当我查看数据库时，时间显示正确的时间（台湾人发布的时间）。

但是，当我使用 UNIX_TIMESTAMP 从数据库中获取日期时，时间发生了变化。

示例：

1. 我在论坛上发布了一些内容。我手表上的日期时间是 2009-10-2 11:24am（台湾时间）
2. 我查看数据库，它说日期时间是 2009-10-2 11:24am（与我手表上的时间相同。很好！）
3. 然后当我使用 UNIX_TIMESTAMP 在我的网站上显示日期时，它显示为 2009-10-03 4:22 pm （糟糕！它应用了偏移量）

有没有办法让 UNIX_TIMESTAMP 停止转换时间（应用当我从数据库查询日期时偏移量）？

额外信息：
我正在使用 PHP
我已将 PHP 中的时区设置为台湾 (date.timezone = Asia/Taipei)
如果用户位于台湾以外的另一个时区，我希望它将时间转换为台北时间。该网站几乎 100% 被台湾使用，所以我只想始终显示台湾时间，即使他们位于另一个时区。
我以不同的 date() 格式在网站周围的许多区域显示日期。
基本上一切都很好，除了当我使用 UNIX_TIMESTAMP 查询数据时，它会向时间应用偏移量。

谢谢！

Answer 1

MySQL“按原样”写入日期，也如此读取它们，但是 UNIX_TIMESTAMP 将任何输入日期视为本地时区，并将它们转换为 UTC/GMT 时间戳，这意味着它将应用您的本地时区偏移量，现在如果您处理从mysql 通过例如。 php date() 它将再次应用您的本地时区偏移量（注意还有 gmtime() 不会这样做），这会产生不需要的结果。

但是您可以使用以下技巧来完成，该技巧将在 UNIX_TIMESTAMP() 应用它之前减去您的会话时区，因此，如果您希望数据库中的日期与数据库中的日期完全相同，则无论服务器/本地时区如何，您都将获得准确的数字格林威治标准时间。

mysql> SELECT UNIX_TIMESTAMP(CONVERT_TZ("2013-05-27","GMT",@@session.time_zone));
+--------------------------------------------------------------------+
| UNIX_TIMESTAMP(CONVERT_TZ("2013-05-27","GMT",@@session.time_zone)) |
+--------------------------------------------------------------------+
|                                                         1369612800 |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

另一种解决方案是将服务器或会话时区设置为 0(GMT)，这样就不会发生真正的转换。

Answer 2

除非另有说明，MySQL 采用系统的默认时区设置，它解释了您遇到的问题；查看 MySQL 的时区参考手册了解更多详细信息。根据我过去的经验，我得出的结论是 UTC 是存储日期和时间的最佳选择；当向用户显示时，它们会转换为用户的时区。

如果可能，将数据库中的所有日期和时间条目更改为 UTC，使用date_default_timezone_set()在 PHP 中配置时区，并确保在将其呈现给用户和将其存储在数据库中时正确转换它以及。如果无法存储 UTC 值，您可以按照与 UTC 相同的方式按照时区参考指南进行转换。

您需要做的是从数据库中获取原始日期和时间，然后使用 PHP 的 DateTime 来转换它。还可以查看 DateTimeZone 。

Answer 3

我发现这个问题最好的办法是使用这个：

SELECT UNIX_TIMESTAMP(CONVERT_TZ(<<>>,'+15:00','+00:00')) +TIMESTAMPDIFF(second,utc_timestamp() , now())

示例：我想获取当地时间 2012 年 5 月 31 日 23:59:59 的时间戳。
SELECT UNIX_TIMESTAMP(CONVERT_TZ('2012-05-31 23:59:59','+15:00','+00:00')) +TIMESTAMPDIFF(second,utc_timestamp(), now())

这样我得到时间戳 GMT-0，对应于当地时间。

Answer 4

我找到了一个可能的解决方案，即仅从数据库中检索日期而不将其转换为 Unix 时间，然后仅使用 strtotime();将其转换为 Unix 时间。基本上我不是使用 sql 进行转换，而是使用 php 进行转换。我唯一不喜欢它的是： strtotime() <我不确定这个函数有多可靠，我必须去更改大约 100 个使用 UNIX_TIMESTAMP 的地方（doh！）

还有其他方法吗？