Applying timezone to DateTimes from MySQL

[Teach]

Hi guys. I've got a query that's baffling me, and I've been trying to resolve it for weeks.

I am storing my user's timezones in my user database as timezone with values such as: Europe/London, America/New_York which PHP understands.

Our website stores data in GMT time (using gmdate()), and stores timestamps as 'datetime' in my MySQL database as Y-m-d H:i:s

An example of SQL dump shows:

`onlinetime` datetime NOT NULL default '0000-00-00 00:00:00',

We are setting the timezone using the below code. This means that if the user is logged in (the logged variable) it will find their timezone and set it, otherwise, use GMT.

if (isset($_SESSION['logged'])) {
  $search=mysql_fetch_array(mysql_query2("SELECT * FROM user WHERE account='".mysql_real_escape_string($_SESSION['account'])."'"));
  $timezone=$search['timezone'];
  date_default_timezone_set($timezone);
} else {
  date_default_timezone_set('GMT');
}

Now the problem... date() works fine and will display the correct time according to their timezone, but echoing out a time from a MySQL value doesn't, it remains GMT. An example of how we echo it is as follows:

".date('l, jS F, Y, H:i:s T', strtotime($cnews['dateposted']))."

Any help will be really appreciated.

Thanks.

[foyer]

You probably want to use mk_time

[Teach]

Hi there

Thanks for that.

I did a bit more research and found another way, and using this code it seems to work. Can you confirm this is okay too?

  $time = strtotime($myrow['dateadded']);
  echo date('M d H:i:s Y', $time+date("Z",$time));

Thanks.

[Installer]

Here's another way, if you have PHP >= 5.2.0:

$dt = new DateTime($myrow['dateadded'], new DateTimeZone('GMT'));
$dt->setTimezone(new DateTimeZone($myrow['timezone']));

echo $dt->format('M d H:i:s Y');