Timezone help in PHP/MySQL

Question

I have been fighting the timezone battle for weeks now, everyone thinks they know the solution and then they get a million upvotes but the reality is I am totaly 100% stuck, 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 and all.

If you are a PHP/MySQL expert, or even a begginner with crazy good luck and have gotten php/mysql/ user submitted timezone working, then please do help.

Below is what I have so far, please read the comments to see what kind of issues I am having. Basicly I can show a date and time in the users timezone if it is in the form of a timestamp like this "1262819848" but the NEWER 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 paased in. The only way I can do this is to eather 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.

If you can help me use a datetime functions then I would greatly appreciate the help, I have been stuck on this problem for a couple YEARS now. I am about to give up and use the Integer field like everyone on SO says is bad for some reason.

<?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"

?>

Based on past responses to a similar question, plus reading every post on this site related to php/mysql and timezones, I will probably not resolve this issue so I will start a bounty on this as soon as it let's me. Thanks

Answer 1

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'))

Answer 2

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 http://stackoverflow.com/questions/455554/datetime-to-unix-timestamp/455573#455573 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

Answer 3

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.