Synchronizing Timezones in PHP and MySQL

You are developing an application that requires storing dates in MySQL using the PHP date() function. Comparing these dates within MySQL using NOW() to calculate time differences is necessary. However, the PHP date() function utilizes the timezone defined in PHP, while NOW() employs the timezone configured in the MySQL server.

To resolve this issue, you have attempted various approaches:

• date_default_timezone_set('Europe/Paris') affects PHP only.
• date.timezone = "Europe/Paris" remains isolated to PHP.
• SELECT CONVERT_TZ(now(), 'GMT', 'MET') returns empty results.
• mysql> SET time_zone = 'Europe/Paris' prompts an error in the MySQL console.

Ultimately, the MySQL timezone remains unchanged.

Solution

Synchronize timezones in PHP and MySQL without manual console commands or modifications in php.ini:

PHP:

define('TIMEZONE', 'Europe/Paris');
date_default_timezone_set(TIMEZONE);

MySQL:

$now = new DateTime();
$mins = $now->getOffset() / 60;
$sgn = ($mins d', $hrs*$sgn, $mins);

// Assuming an existing DB connection named $db
$db->exec("SET time_zone='$offset';");

This approach ensures that both PHP and MySQL utilize the same timezone, eliminating discrepancies in date storage and comparison. Additionally, it eliminates the need for manual timezone settings or modification of global configuration files.