PHP和MySQL之间的时区差异 [英] Timezone differences between PHP and MySQL

问题描述

我遇到一个问题，因为PHP和MySQL似乎不同意在将某些时间戳转换为日期和时间时应使用哪个时区。

当我有一个时间戳，我想转换回一个 datetime 格式时出现问题。 PHP给出基于GMT作为时区的日期和时间，但MySQL似乎认为它在GMT + 1中运行，并相应地进行转换。

简单复制

  $ original_date = '2009-08-31 23:59:59' 
 $ timestamp = strtotime（$ original_date）; 
 echo $ timestamp; //提供1251763199 
 
 echo date（'Y-m-d H：i：s'，$ timestamp）; // PHP：2009-08-31 23:59:59 
 echo db_field（'SELECT FROM_UNIXTIME（1251763199）'）; // MySQL：2009-09-01 00:59:59 
  

PHP给出的时间戳似乎成为正确的假定时区为GMT。如果我们正在运行BST（当时在GMT + 1之内的时间戳），MySQL的结果将是正确的。

如果我从今天（ 1267640942 $ timestamp c $ c>），PHP和MySQL都似乎很高兴地告诉我，它是 2010-03-03 18:29:02 （都返回GMT）。 / p>

服务器上设置了什么时区？ p>

我已经检查过 MySQL文档，这表示如果我的时区设置为系统比操作系统将提供时区信息。现在似乎是这样的：

  mysql> SELECT @@ global.time_zone，@@ session.time_zone; 
 + -------------------- + --------------------- + 
 | @@ global.time_zone | @@ session.time_zone | 
 + -------------------- + --------------------- + 
 | SYSTEM | SYSTEM | 
 + -------------------- + --------------------- + 
  

我的网络服务器上的默认时区是GMT。这也是我的数据库服务器上的默认系统时区（根据每个服务器上的cl上运行的 date 命令）。

  foo @ bar：/ home / foo $ date 
 Wed Mar 3 18:45:02 GMT 2010 
  

所以根据文档，我的数据库服务器应该运行在GMT，这就是我们想要的。然而，我的测试脚本中给出的查询输出表明它在GMT + 1中运行。

我知道这个问题有一些解决方法完全在PHP或MySQL等），但我希望了解导致这种差异的原因，以便我们可以将其排除在外，并阻止团队中的任何其他人被其绊倒。

$ b有没有人知道我有一个非常基本的设置，我在这里看过，或者知道可能导致这种差异？

谢谢！

解决方案

我建议使用 UTC 在您的服务器上，MySQL安装并将时区转换为您想要的。 MySQL和PHP中的转换相当简单。

• http://www.w3schools.com/php/php_ref_date.asp （getTimezone和setTimezone）


• http://dev.mysql.com/doc/refman/5.1 /en/date-and-time-functions.html

I've run in to an issue whereby PHP and MySQL seem to disagree on which timezone they should be using when converting certain timestamps back to date and time.

The problem arises when I have a timestamp which I would like to convert back to a datetime format. PHP gives the date and time based on GMT being the timezone, but MySQL seems to think it is operating in GMT+1, and converts accordingly.

Simple reproduction

$original_date = '2009-08-31 23:59:59';
$timestamp = strtotime($original_date);
echo $timestamp;                                    // Gives 1251763199

echo date('Y-m-d H:i:s', $timestamp);               // PHP:   2009-08-31 23:59:59
echo db_field('SELECT FROM_UNIXTIME(1251763199)');  // MySQL: 2009-09-01 00:59:59

The timestamp given by PHP seems to be the correct one for the date given, assuming timezone is GMT. The result from MySQL would have been the correct one had we been running BST (timestamp given fell within GMT+1 at that time).

If I try the above with a $timestamp from today (1267640942), PHP and MySQL both seem to be happy to tell me that it is 2010-03-03 18:29:02 (both returning GMT).

What timezone is set on the servers?

I've checked the MySQL docs, which say that if my timezone is set to system than the OS will provide the timezone info. This appears to be the case at the moment:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              | 
+--------------------+---------------------+

The default timezone on my web server is GMT. This is also the default system timezone on my database server (according to the date command run on the cl on each server).

foo@bar:/home/foo$ date
Wed Mar 3 18:45:02 GMT 2010

So according to the docs, my DB server should be running on GMT, which is what we want. Yet the query output given in my test scripts suggests that it's running in GMT+1.

I know there are a number of workarounds for this problem (all date arithmetic being done fully in either PHP or MySQL, etc) but I'd love to get to the bottom of what's causing this discrepancy so we can sort it out and prevent anyone else on the team from being tripped up by it.

Does anyone know if there's a very basic setting that I've over-looked here, or know what could be causing this discrepancy?

Thanks!

解决方案

I would suggest using UTC on your server and MySQL install and convert the timezone(s) to what ever you want. The conversion in MySQL and PHP are fairly simple.

• http://www.w3schools.com/php/php_ref_date.asp (getTimezone and setTimezone)
• http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

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