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.

Answer 3

I have spend almost half day to solve this problem and I think I need to help others. So I am providing you some guide line to avoid problem regarding timezone.

First of all you have two options either use MYSQL TIMEZONE or PHP TIMEZONE don't mix it with each other if you want stay away from timezone nightmares.

IF YOU CHOOSE MYSQL TIMEZONE:

FOR INSERTION:

fieldname: yourfieldname

datatype: timestamp 

default: CURRENT_TIMESTAMP

FOR UPDATION:

UPDATE YOUR_TABLE SET youfieldname = NOW() WHERE CONDITION;

FOR SELECTION:

The only thing you need to do is set your require timezone for your application needs mysql automatically convert stored timestamp to your specified timezone.

mysql>SET SESSION time_zone='offset'; 
mysql>SELECT NOW();

offset example: +05:30 for India same way find offset for your area region.

Now you will get your desired date and time from mysql.

IF YOU CHOOSE PHP TIMEZONE:

Simply just set your desire timezone using this function:

date_default_timezone_set('America/Los_Angeles');

FOR INSERTION:

fieldname: yourfieldname

datatype: datetime

Use PHP date function to store date and time:

$sql = "INSERT INTO timetest ( YOURDATETIMEFIELD ) VALUES ('".date('Y-m-d H:i:s')."')";

FOR UPDATION:

$sql = "UPDATE YOUR_TABLE SET yourdatetimefield = '".date('Y-m-d H:i:s')."'";

FOR SELECTION:

$sql = "SELECT yourdatetimefield FROM yourtable";

I hope this help you.