PHP Date and Time MySQL Management Recommendation

Things are simple if everyone lives in the same time zone. Things become complicated when we want to develop a system that is "international". Time zone is a very important for internet related applications. The following shows the method I like when inserting time and date into MySQL and retrieve from it:


Insert date and time to MySQL

$timezone = "2"; // Get the time zone from user, usually from POST data // Normalize Date/Time to Greenwich Mean Time (GMT) Zone // Use gmdate to format to MySQL friendly format $date = gmdate("Y-m-d H:i:s", time()); $result = mysql_query("INSERT INTO my_table VALUES ('$value1', ..., '$date', '$timezone')"); if (!$result) { die('Invalid query: ' . mysql_error()); }

IMPORTANT: Make sure the MySQL field is declared as timestamp data type. Time zone is of integer of size 1.






Retrieve date and time from MySQL

$result = mysql_query("SELECT my_date_time, my_time_zone FROM my_table WHERE my_match_criteria = '".$search_criteria."'"); if (!$result) { die('Invalid query: ' . mysql_error()); } // assume only one row of result is returned. Otherwise, use mysql_fetch_array() $time_data = mysql_fetch_assoc($result); $offset = intval($time_data['my_time_zone'])*60*60; $adjusted_time_data = strtotime($time_data['my_date_time']) + $offset; $adjusted_time = date("g:i a F j, Y ", $adjusted_time_data);

NOTE: The date/time stored in the MySQL will be displayed according to the time zone saved in the database as well. Please change the column name my_data_time and my_time_zone according to your need. intval() is used to convert the time zone string retrieved from MySQL to integer value to calculate the offset to the time/date stored in GMT format.

With this method, one does not need to worry about the origin of the user. The downside is that the date/time will change whenever the time zone is changed. It all depends on your web app design whether you allow the user to change the time zone after the initial setting.

strtotime() is used to convert the date/time in string format to UNIX time format (integer) so that the offset can be added to it to produce desirable result. Adding offset to a string won't produce the right time-zone-adjusted date/time.

date() is finally used to format the date/time for proper display.

ALTERNATIVE: You can also store the 10-digit raw UNIX time value from time() function and stored as 10-byte integer to reduce the size of your database. Anyway, the MySQL functions related to the timestamp format can't be used for this 10-byte integer data type. It's totally up to your preference whether time manipulation or the size is more important for your app.