PHP 和 MySQL 的时区问题

2022-01-16 00:00:00 timezone php mysql

我的服务器时间是格林威治标准时间，每当有人上线时，我都会执行以下操作.

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.

相关文章