PHP和MySQL存在时区问题 [英] Having Timezone problems with PHP and MySQL

问题描述

我的服务器时间以格林尼治标准时间(GMT)为准，每当有人在线时，我都会执行以下操作.

My server time is in GMT, and I do the following whenever someone comes online.

// Set a default timezone
$defaultTimeZone = 'America/Toronto';

// load the user, if they are online...
$onlineUser = new user();
if (isset($_SESSION['user_id']))
{
    if ($onlineUser->loadUser($_SESSION['user_id']))
    {
        $defaultTimeZone = $onlineUser->timeZone;
    }
}

// set time zones
date_default_timezone_set($defaultTimeZone);
$db->query("SET SESSION time_zone = '".$defaultTimeZone."'");

所以，我的问题是……每当有人执行某项操作时，它会将日期/时间存储在用户本地时间中……这给我带来了很多麻烦.

So, my problem is this... whenever someone does something it stores the date/time in the users local time... causing me a whole wack of issues.

我想要的是将所有内容存储在GMT中，但让用户看到&与数据在其本地时区进行交互.

All I want is for everything to be stored in GMT, but have the users see & interact with the data in their local time zone.

这是我更新用户状态的方法:

Here is how I am updating the users status:

public function updateStatus()
{
    global $db, $common, $config;

    // update the user record
    $data = array(
        'date_last_active'  => new Zend_Db_Expr('NOW()')
    );

    $db->update('users', $data, 'user_id='.$this->userId);
}

这是我的功能，可以将日期戳转换为秒...

Here is my function to turn a date stamp into seconds...

public function dateTimeToUnixTime($dateString)
{
    if (strlen($dateString) < 10)
    {
        return "";
    }

    $parseDateTime = split(" ", $dateString);
    $parseDate = split("-", $parseDateTime[0]);

    if (isset($parseDateTime[1]))
    {
        $parseTime = split(":", $parseDateTime[1]);
    }
    else
    {
        $parseTime = split(":", "00:00:00");
    }

    return mktime($parseTime[0], $parseTime[1], $parseTime[2], $parseDate[1], $parseDate[2], $parseDate[0]);
}

最后，我如何比较日期:

And finally, how I am comparing the dates:

    $date = $oUser->dateLastActive;

    $lastSeen = abs($common->dateTimeToUnixTime(date('Y-m-d H:i:s')) - $common->dateTimeToUnixTime($date));

    if ($lastSeen < 300 )
    {
        echo "<font size='1' color='green'><strong>Online</strong></font>";
    }

    if ($lastSeen >= 300)
    {
        echo "<font size='1' color='black'><strong>Offline</strong></font>";
    }

推荐答案

这里的诀窍是知道date_last_active是什么列类型.我对TIMESTAMP和DATETIME有足够的经验，知道其中一个会翻译(并尊重)MySQL time_zone会话变量，而另一个不会.

The trick here is to know what column type date_last_active is. I've had enough experience with TIMESTAMP and DATETIME to know that one translates (and honors) the MySQL time_zone session variable, the other doesn't.

mysql> SET SESSION time_zone = 'America/New_York';
Query OK, 0 rows affected (0.00 sec)

mysql> create table timetest ( dt datetime NULL, ts timestamp NOT NULL DEFAULT 0 );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO timetest ( dt, ts ) VALUES ( UTC_TIMESTAMP(), UTC_TIMESTAMP() );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timetest ( dt, ts ) VALUES ( NOW(), NOW() );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM timetest;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2009-06-27 17:53:51 | 2009-06-27 17:53:51 | 
| 2009-06-27 13:53:54 | 2009-06-27 13:53:54 | 
+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> set session time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM timetest;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2009-06-27 17:53:51 | 2009-06-27 21:53:51 | 
| 2009-06-27 13:53:54 | 2009-06-27 17:53:54 | 
+---------------------+---------------------+
2 rows in set (0.00 sec)

所以，这就是我的工作:

So, here is what I do:

• 插入数据库时​​，请在PHP和MySQL中使用UTC.在my.cnf中，我具有:time_zone=UTC以避免MySQL和PHP我date_default_timezone_set('UTC')中的任何问题.
• 使用UTC_TIMESTAMP()而不是NOW()-一个是UTC，一个使用本地(会话)时区.如果您遵循上面的第一点，请使用UTC_TIMESTAMP().
• 选择显示给用户时，在显示任何内容之前，先set time_zone=' local_time_zone '.
• 如果您必须显示内容，然后进行更新，请确保在PHP和MySQL中以适当的时区更改将您的呼叫括在括号中.

• When inserting into the database, use UTC in PHP and in MySQL. In my.cnf I have: time_zone=UTC to avoid any issues in MySQL, and in PHP I date_default_timezone_set('UTC').
• Use UTC_TIMESTAMP() instead of NOW() - one is UTC, one uses the local (session) time zone. If you're following the first point, above, use UTC_TIMESTAMP().
• When selecting to display to the user, set time_zone='local_time_zone' before displaying anything.
• If you have to display stuff, then update, make sure to bracket your calls with the appropriate timezone changes in PHP and MySQL.

希望这足以弄清楚如何解决这个问题.如果您还有其他问题，请告诉我.

Hopefully that's enough to figure out how to approach this. Let me know if you have further questions.

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