PHP/MySQL Timezone Clarification

Question

Hello all,

I'm currently having an issue wrapping my brain around the notion of converting my MySQL time to a specific timezone, depending on the user's settings.

All of my MySQL times are stored in UTC time, in the following format:

2009-11-08 17:06:40

Once I query the time, I'm not quite sure how to convert it to the appropriate timezone using PHP.

Thus, in the example above, I'd like to display:

2009-11-08 09:06:40

Here's what I currently have (which probably needs to be fixed):

$sql = 'SELECT date FROM mytable';
 require("connection.php");
 $result = mysql_db_query($DBname,$sql,$link); 
 while($row = mysql_fetch_assoc($result)) { 

 $dt_obj = new DateTime($row['date']); 
 $dt_obj->setTimezone(new DateTimeZone('PST')); 
 echo $dt_obj;   
 echo "<br>";
  }

First off, I get the following error:

Catchable fatal error: Object of class DateTime could not be converted to string

Secondly, I am confused as to whethere I'm setting it up properly to display the time in the correct timezone anyway (in this case, PST).

Any suggestions on how to do this would be greatly appreciated. Thanks!

UPDATE:

I took GZipp's advice, and modified the code to look like:

$dt_obj->setTimezone(new DateTimeZone('America/Los_Angeles')); 
echo $dt_obj->format('Y-m-d H:i:s');

However, it's displaying my time (using the example from above) as:

2009-11-08 15:06:40

Any ideas on what would be causing this?

Answer 1

I may be wrong, but it looks like it's complaining about your echo $dt_obj; statement. You might try echoing the result of DateTime::format();

EDIT: For your new question I would guess that your default format is set to PST already, so when the new date is created it's create with that timezone, thereby setting the timezone changes nothing. You might check and see if that's the case. You also might look at date_default_timezone_set('<tz>');

Answer 2

I think this is what you're after;

$dt_obj = new DateTime($row['date']); 
$dt_obj->setTimezone(new DateTimeZone('America/Los_Angeles')); 
echo $dt_obj->format('Y-m-d H:i:s');

Use the List of Supported Timezones.

Update to edited question: To ensure that PHP sees the time from the database as UTC time, do something like this:

$row['time'] = '2009-11-08 09:06:40';

$dt_obj = new DateTime($row['time'], new DateTimeZone('UTC')); 
echo 'UTC: ' . $dt_obj->format('Y-m-d H:i:s') . '<br />';  // UTC: 2009-11-08 09:06:40
$dt_obj->setTimezone(new DateTimeZone('America/Los_Angeles')); 
echo 'Los Angeles: ' . $dt_obj->format('Y-m-d H:i:s');  // Los Angeles: 2009-11-08 01:06:40