Getting timezones with a TIMESTAMP field in PHP/MySQL

I am totally 100% stuck on this, no answer ever given for timezone support in PHP/MySQL has worked 100% for me, so I am asking the question again on here in as much detail as I possibly can with code sample.

Below is what I have so far, please read the comments to see what kind of issues I am having. Basically I can show a date and time in the user's timezone if it is in the form of a timestamp like this "1262819848" but the N开发者_开发知识库EWER versions of MySQL return a TIMESTAMP like this instead "2010-01-06 23:17:28".

If I could pass "2010-01-06 23:17:28" into this date("m:d:y h:i:s A",$time_stamp) then things would work GREAT but instead it needs a timestamp passed in. The only way I can do this is to either store a real timestamp in an INTEGER field in MySQL OR else get the value "2010-01-06 23:17:28" and then convert it to a timestamp which just seems like to much overhead when I can store it already converted in an INT field.

<?PHP
/////////////////////////////////////////////////////////
//   Get a MySQL time / date and show in user timezone //
////////////////////////////////////////////////////////

//set the user's time zone for this page
//this assumes the user has already chosen a timezone and we are getting it from a PHP session value now
// example value is "America/New_York" which is -5
date_default_timezone_set($_SESSION['time_zone']);

// get a MySQL result with a time/date value in it
$sql = 'SELECT user_id,date from online_users WHERE user_id = 1';
$result = executeQuery($sql);

// set our date/time value from MySQL into a variable
if ($line_online = mysql_fetch_assoc($result)){
    $time_stamp = $line_online['date'];
}

// format our date/time value TIMESTAMP any way we like now!
// MySQL result MUST be in this format '1262291376'
$date_format = date("m:d:y h:i:s A",$time_stamp); // formats timestamp in mm:dd:yy 
echo $date_format;


/////////////////////////////////////////////////////////
//   Add a time / date to MySQL                       //
////////////////////////////////////////////////////////

// this add a time and date to MySQL and looks like this in the MySQL table "2010-01-06 23:17:28"
// that is a TIMESTAMP field in MySQL, newer version of mysql store a timestamp like this "2010-01-06 23:17:28" instead of like this "1262819848"
$sql = "INSERT INTO users (`id`, `datetime`) VALUES ('', UTC_TIMESTAMP())";
executeQuery($sql);

// So if I query this record and try to show it like I do in the code above in the "Get mysql result section" It will not work correctly because  
// because it is like this "2010-01-06 23:17:28" instead of like this "1262819848"
// The ONLY solution I have found is to store the UTC timestamp into an INTEGER field instead of a DATETIME or TIMESTAMP field.
// Everyone on this site says this is wrong and I could use the default date and time stuff but I have yet to get it working.
// If I could store a timestamp in mysql like a timestamp with no "-" dashes in it, then it would work correctly when I show it on page.
// Basicly I have this half done, I can show the time and date in a users timezone as long as I pass the PHP a real TIMESTAMP like this "1262819848"

?>

strtotime() in PHP is a way to pass database datetime format into a UNIX epoch timestamp in PHP. It reads understandable and standard formats and converts into UNIX epoch timestamp, which can be later used by other datetime functions in php such as date().

Alternatives can be as such:

UNIX_TIMESTAMP() function in MySQL helps you to convert a specific column datetime or timestamp value into UNIX timestamp. As mentioned at Datetime To Unix timestamp it can be a little faster because this is done on the MySQL server side.

This seems more of a timestamp than timezone question.

See more:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

http://www.php.net/strtotime

First off, if you have a solution to a programming problem here's my advice: USE IT. Don't worry about convincing the stackoverflow peanut gallery about the correctness of your solution.

That having been said, here's my two bits:

Showing the user a date in their timezone is a display/front-end issue. Your internal dates in both code & database should be standardized to a common timezone then translated to user's timezone on display.

Instead of setting the date_default_timezone_set to a user-supplied timezone, set the script's default timezone to UTC (so it matches the MySQL timestamp/datetime value) and then apply the user's timezone offset to the standardized date. If a user supplies a datetime like $mydate= 01/01/2010 5:30pm apply the user's tz offset to that date, and convert it to the standardized tz using strtotime, something like strtotime("$mydate $mytimezone") where $mytimezone is the user-supplied timezone or offset.

if I could pass "2010-01-06 23:17:28" into this date("m:d:y h:i:s A",$time_stamp)

maybe with: date('m:d:y h:i:s A', strtotime('2010-01-06 23:17:28'))