Scroll page back to the top

Timezone with PHP and MySQL

In PHP and MySQL time zone is handled separately, therefore if you need to set up a time zone for your application different than your server's default one, you will have to address both in order for the results to be accurate.

Setting up the time zone with PHP as well as with MySQL is relatively easy.

For PHP we will use the date_default_timezone_set somewhere in our configuration file - so open your project's configuration file and somewhere at the top type the following:

date_default_timezone_set("Europe/London");

The value passed as parameter is the time zone I want to use for my own application. You might want to use a completely different one, so here's the list of all time zones grouped by the continent.

Now that our time zone for PHP is sorted we need to do the same with MySQL.
For MySQL we will use the statement in the following format, before processing any other queries:

SET time_zone = 'timezone';

or - if you are running the query with SUPER privileges you can set the global server time zone value at runtime by adding GLOBAL keyword:

SET GLOBAL time_zone = 'timezone';

Now to get the timezone for the SQL, what we will use is the date_default_timezone_get function, which will read the time zone set by the date_default_timezone_set function used to set up the time zone for PHP.

Here's the way we would do it with PHP PDO class:

try {

	$timezone = date_default_timezone_get();
	
	$objDb = new PDO('mysql:host=localhost;dbname=my_database', 'username', 'password');
	$objDb->exec("SET CHARACTER SET utf8");
	
	$objDb->exec("SET time_zone = '{$timezone}'");
    
} catch (PDOException $e) {

	echo 'Connection failed: ' . $e->getMessage();
    
}

Then to check wether the time zone set in MySQL is right you could use the following SQL statement:

SELECT NOW() AS `current_time`;

The above should output the column current_time with the current time matching your set time zone.

To test your PHP time zone you simply echo the date() function like so:

echo date('Y-m-d H:i:s');

Now it is worth to mention that time zone values have different formats and MySQL supports the following:

Format Description
SYSTEM Indicates that the time zone should be the same as the system time zone
+00:00 Indicates an offset from UTC
Europe/London Named time zones. Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.

Now, as you can see the SYSTEM keyword represents the time zone of the system. To change the time zone to the same as the server one use the following statement:

SET time_zone = 'SYSTEM';

If your mysql database does not contain the time zone information table, then you will have to use a different approach.

Here's how you can do it:

$objDT = new DateTime();
$offset = $objDT->getOffset();

In the above example we have used the DateTime class (available from PHP version 5.2.0), which when instantiated without any parameters, takes the current date and time within the system time zone (defined by the date_default_timezone_set function).

After object instantiation, we have used the getOffset method to get the time zone offset and assigned the result to the $offset variable.

Next we need to format the returned offset value so that MySQL can understand it:

$offsetHours = round(abs($offset) / 3600);
$offsetMinutes = round((abs($offset) - $offsetHours * 3600) / 60);
$offsetString  = ($offset < 0 ? '-' : '+');
$offsetString .= (strlen($offsetHours) < 2 ? '0' : '').$offsetHours;
$offsetString .= ':';
$offsetString .= (strlen($offsetMinutes) < 2 ? '0' : '').$offsetMinutes;

The above first converts the returned offset to number of hours by using round (which rounds the value - in this case without any decimal places) and abs function which returns the absolute value of the number (i.e. -3.5 => 3.5, 5 => 5 etc.) - which is divided by 3600 seconds.

Then we get the minutes by using the same functions and two generated values. After this we check whether the value assigned to the $offset variable is less then 0, which would indicate that the value is negative and assign the minus or plus symbol to the $offsetString variable.

We concatenate the $offsetString variable with the number of hours, by first checking wether the string length is less than 2, which would indicate that there is just one digit and prepending 0 to it if so.

On the next line we add the colon (divider between hours and minutes) and the last line adds the minutes in pretty much the same way as hours.

Now that we have our offset value ready, we can use it with our SQL statement:

SET time_zone = '{$offsetString}';
 
 
 

Discussion (2 comments)

  • HeatoN

    HeatoN on Monday, 31st October 2011

    I just went through the entire site and I found most of the tutorials very interesting and valuable. I just felt saying that I really appreciate the effort you put into this site and wanted to encourage you to keep going. Peace.

    Reply

  • Sebastian Sulinski

    Sebastian Sulinski : @designtutorials on Monday, 31st October 2011

    Thanks NeatoN

    Reply

 
 
Add a comment
Add Comment