[SOLVED] MySql Query Change Timezone

[hoopplaya4]

Hi All,

 

This is probably an easy question.  I have a timestamp in my mysql database that I'd like to display in Pacific (Los Angeles) time.  The mysql time appears to be set at GMT.  How would I go about displaying the timestamp in Pacific time?

 

Here's my current query right now:

 

<? 
require("../connection.php");
$rs = mysql_db_query($DBname,$sql,$link);

$data = mysql_query("SELECT * FROM tblAnnouncements ORDER BY announceID DESC LIMIT 5") or die(mysql_error());


while($info = mysql_fetch_array( $data ))
{
print ("<li>");
print ("<p>" . $info['announcement'] . "  ");
print ("<br><small>Posted by Johnny on ");
print("" . date("g:i a F j, Y ", strtotime($info["announceCreate"])) . "</small></p>");
print ("</li>");
}
?>

 

Any help is appreciated!

[Mchl]

Try date_default_timezone_set

[hoopplaya4]

Thanks for the reply Mchl.

 

using "date_default_timezone_set('America/Los_Angeles');" works in an instance where I will display the current time.  For example, "echo date("g:i a")"

 

However, the mysql values remain unchanged.  Is there anything else I'd need to add to my statement?

[Mchl]

Maybe like this

date("g:i a F j, Y ", strtotime($info["announceCreate"]) + strtotime('+2 hours'))

 

Instead of +2 hours put your time difference to GMT

[hoopplaya4]

Hmm, that seemed to totally throw the hours, minutes, and even the dates.  This is what I'm getting back now.  (I even tried to use your example of +2 hours).

 

2:29 pm September 24, 1911
2:24 pm September 24, 1911
1:37 pm September 24, 1911
2:03 pm September 23, 1911
1:45 pm September 23, 1911

 

When it used to be:

 

10:55 am November 30, 2008
10:50 am November 30, 2008
10:03 am November 30, 2008
10:29 am November 29, 2008
10:11 am November 29, 2008 

 

 

[PFMaBiSmAd]

He actually meant -

 

date("g:i a F j, Y ", strtotime($info["announceCreate"] .'+2 hours')

 

You can do this directly in your SELECT query -

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz

[hoopplaya4]

Thanks for the reply.  The updated PHP code did it for me.

 

date("g:i a F j, Y ", strtotime($info["announceCreate"] .'+2 hours'))

 

Also, thanks for the tip on the SELECT statement.  Unfortunately, I'm using MySql 4.12, and apparently "convert_tz" only works with 4.13+.  So, I'll have to go with the PHP update for now.

 

Thanks again!