I was asked this interview question so thought I would post it here to see how other users would answer:
Please write some code which connects to a MySQL database (any host/user/pass), retrieves the current date & time from the database, compares it to the current date & time on the local server (i.e. where the application is running), and reports on the difference. The reporting aspect should be a simple HTML page, so that in theory this script can be put on a web server, set to point to a particular database server, and it would tell us whether the two servers’ times are in sync (or close to being in sync).
This is what I put:
// Connect to database server
$dbhost = 'localhost';
$dbuser = 'xxx';
$dbpass = 'xxx';
$dbname = 'xxx';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (mysql_error());
// Select database
mysql_select_db($dbname) or die(mysql_error());
//
Retrieve the current time from the database server
$sql = 'SELECT NOW() AS db_server_time';
// Execute the query
$result = mysql_query($sql) or die(mysql_error());
// Since query has now completed, get the time of the web server
$php_server_time = date("Y-m-d h:m:s");
// Store query results in an array
$row = mysql_fetch_array($result);
//
Retrieve time result from the array
$db_server_time = $row['db_server_time'];
echo $db_server_time . '<br />';
echo $php_server_time;
if ($php_server_time != $db_server_time) {
// Server times are not identical
echo '<p>Database server and web server are not in sync!</p>';
// Convert the time stamps into seconds since 01/01/1970
$php_seconds = strtotime($php_server_time);
$sql_seconds = strtotime($db_server_time);
// Subtract smaller number from biggest number to avoid getting a negative result
if ($php_seconds > $sql_seconds) {
$time_difference = $php_seconds - $sql_seconds;
}
else {
$time_difference = $sql_seconds - $php_seconds;
}
// convert the time difference in seconds to a formatted string displaying hours, minutes and seconds
$nice_time_difference = gmdate("H:i:s", $time_difference);
echo '<p>Time difference between the servers is ' . $nice_time_difference;
}
else {
// Timestamps are exactly the same
echo '<p>Database server and web server are in sync with each other!</p>';
}
Yes, I know that I have used the deprecated mysql_* functions but that aside, how would you have answered, i.e. what changes would you make and why? Are there any factors I have omitted which I should take into consideration?
The interesting thing is that my results always seem to be an exact number of minutes apart when executed on my hosting account:
2012-12-06 11:47:07
2012-12-06 11:12:07
