Having Timezone problems with PHP and MySQL...

Question

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.

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.

EDIT:

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>";
 }

Answer 1

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:

• 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.

Answer 2

I think Mysql's NOW() function stores the current time zone, which is definitely a hassle, I would check out CONVERT_TZ(dt,from_tz,to_tz), this could solve your issue, also I believe php's time() returns server time, you could store this instead. the only drawback is you'd either have to convert the returned value to a datetime/timestamp compatible value, or store it as a varchar. the drawback to using convert_tz is you could end up with some ugly queries, and you'd have to figure out the current time zone every time you used it.