Synchronizing Date and Time in different Timezones with PHP, MySQL and JavaScript

How do you show the correct date and time (timestamp) to users in differnet time zones? In theory it should be simple:

• Save your your date and time with the correct timezone
• Retrieve the timezone of the user to whom you will display the date and time to
• Calculate the difference in hours between the saved date and time and the users date and time
• Add the difference in hours to the saved date and time and display

Save your your date and time with the correct timezone

The date and time with timezone is a timestamp. Though implementations differ, timestamps basically contain the same information (date/time and timezone).The timezone can be explicitly recorded in the timestamp (eg: 2005-10-30 T 10:45 UTC) or implicitly taken from the context in which the timestamp was generated or recorded (eg: unix timestamp is dependent on timezone of server generating the timestamp).

Something as simple as saving a timestamp in mysql with PHP can be not so simple due to the difference in the timestamp representation in the two languages.

The PHP timestamp is defined as the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT) while the mysql timestamp is a representation of the present date and time in the format YYYY-MM-DD HH:MM:SS.

If you save the timestamp as a mysql timestamp field, then the timestamp is saved as UTC, however, when you access the timestamp it is converted to the timezone set in the mysql server, so basically you don't get to see the stored UTC version of the timestamp. If you save it as a PHP timestamp in a varchar or unsigned int field then it would be subject to the the PHP servers timezone. So in essence both the MySQL and PHP timestamp a dependent on the timezone of their respective servers.

Whichever format you save it in, just remember that both the PHP and MySQL timestamps reference the timezone on the server they are saved on, PHP during generation of the timestamp, and mysql during retrieval.

Retrieve the timezone of the user to whom you will display the date and time to

The easy way to do this is ask the user what their timezone is. You see this used in many registration forms on websites as well as many open source forums, CMSs, blog software etc. However, not every user will even bother giving you the correct timezone.

You can use JavaScript if it is available on the browser:

var tz = (new Date().getTimezoneOffset()/60)*(-1);

This depends on the users computer's clock, so if it is set wrong, then you will get a wrong result. Time is relative however, so if a user wants to be a few hours behind, let them be.

You can use the users IP address to assume their geographic location and thus their timezone. This can be done server side and thus is not dependent on the users browser having JavaScript. Using the IP to determine timezone is dependent on the accuracy of your IP geocoding service you use. Here is an example using the hostip.info API for geocoding and earthtools.org for lat/long conversion to timezone.

<?php
/**
* Retrieves the Timezone by the IP address
* @param String $IP (optional) IP address or remote client IP address is used
*/
function getTimeZoneByIP($IP = false) {

 // timezone
 $timezone = false;

 // users IP
 $IP = $IP ? $IP : $_SERVER['REMOTE_ADDR'];

 // retrieve geocoded data from http://hostip.info/ API in plain text
 if ($geodata = file('http://api.hostip.info/get_html.php?ip='.$IP.'&position=true')) {
  // create an associative array from the data
  $geoarr = array();
  foreach($geodata as $line) {
   list($name, $value) = explode(': ', $line);
   $geoarr[$name] = $value;
  }
  
  // retrieve lat and lon values
  $lat = trim($geoarr['Latitude']);
  $lon = trim($geoarr['Longitude']);
  
  if (strlen($lat) > 0 && strlen($lon) > 0) {
   // pass this lat and long to http://www.earthtools.org/ API to get Timezone Offset in xml
   $tz_xml = file_get_contents('http://www.earthtools.org/timezone-1.1/'.$lat.'/'.$lon);
   // lets parse out the timezone offset from the xml using regex
   if (preg_match("/<offset>([^<]+)<\/offset>/i", $tz_xml, $match)) {
    $timezone = $match[1];
   }
  }

 }
 return $timezone;
}
?>

You can also use a combination of the three in order to correlate the data and get a better guess of the timezone.

Calculate the difference in hours between the saved date and time and the users date and time

Now that we have the timestamp and the users timezone, we just need to adjust the timestamp to their timezone. First we need to calculate the difference between the timezone the timestamp is saved in, as the users timezone.

$user_tz_offset = $tz_user - $tz_timestamp; 

where $user_tz_offset is how far ahead or behind in hours the user timezone is from the timestamps timezone.

Add the difference in hours to the saved date and time and display

Now we have all we need to show the correct time to the user based on their timezone. Example in pseudo code:

$user_tz_offset = $tz_user - $tz_timestamp; 
$users_timestamp = $timestamp + $user_tz_offset;