How To Synchronize Your PHP and MySQL Timezones

time zones msql php 300x172 How To Synchronize Your PHP and MySQL Timezones

PHP and MySQL are web processes that operate with their own separate default configurations for timezones. It is unlikely that you will have timezone issues during development or when you use a single server. PHP and MySQL can use the server’s time configurations in these situations.

Many developers set PHP as the default configuration if:

One’s application can be set to the user’s timezone.
If different timezone requirements exist in two or more running applications
One cannot set the default timezones because the application was installed on shared server.

Developers also change from dates to Unix timestamp integers for simpler usability but there is a risk of running into variety of issues such as additional processing of translating dates for your application, the difficulty to see the dates in a table, the difficulty of acquiring date-based SQL queries as well as the inability to use MySQL date/time functions like NOW().

However it is possible to synchronize the timetables of the PHP and MySQL. A single configuration variable is required to define your PHP timezone.

2. define(‘TIMEZONE’, ‘America/New_York’);

The code below will establish the default timezone for PHP:


For MySQL , use this code:

SET time_zone=’offset’;

Offset is the difference of time in relation to the GMT zone. For instance, if you want to set the timezone a hour ahead of GMT you would replace offset with ‘+1:00′. For the code to work, you need to have a +/- sign with the hours even at 0:00.

To synchronize PHP and MySQL together, you must first start with the following PHP code:

1. $now = new DateTime(); 
2. $mins = $now->getOffset() / 60;

From there you can convert the number of seconds in the offset to minutes with the following calculation:

1. $sgn = ($mins < 0 ? -1 : 1); 
2. $mins = abs($mins); 
3. $hrs = floor($mins / 60);
4. $mins -= $hrs * 60;

From here, format the string with this function:

$offset = sprintf(‘%+d:%02d’, $hrs*$sgn, $mins);

After this step, you can now use the SET time_zone command. Now your PHP and MySQL codes should be synchronized.The following code is an example:

$db = new PDO(‘mysql:host=localhost;dbname=test’, ‘dbuser’, ‘dbpassword’); 
$db->exec(“SET time_zone=’$offset’;”);

Quoted from: