mysql 时区 php,在PHP和MySQL中设置时区

I am making an application where I need to store in MySQL using the PHP date() function.

These dates need to compare them in MySQL using the NOW() function to return the difference in hours, for example:

SELECT TIMESTAMPDIFF( hour, NOW(), finalize_at ) FROM plans;

But the problem is – the PHP date function date('Y-m-d H:i:s') uses the PHP timezone setting, and the NOW() function takes the MySQL timezome from the MySQL server.

I'm trying to solve doing this:

date_default_timezone_set('Europe/Paris'); It works only for PHP.

date.timezone= "Europe/Paris"; It works only for PHP.

SELECT CONVERT_TZ(now(), 'GMT', 'MET'); This return empty.

mysql> SET time_zone = 'Europe/Paris'; This throws an error from the console of MySQL.

And the timezone does not change for MySQL.

Is there any way to change the timezone for both PHP and MySQL without having to do it from the MySQL console, or set a timezone change from somewhere from php.ini and that these values are available for both PHP and MySQL.

Much appreciate your support.

解决方案

In PHP:

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

date_default_timezone_set(TIMEZONE);

For MySQL:

$now = new DateTime();

$mins = $now->getOffset() / 60;

$sgn = ($mins < 0 ? -1 : 1);

$mins = abs($mins);

$hrs = floor($mins / 60);

$mins -= $hrs * 60;

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

//Your DB Connection - sample

$db = new PDO('mysql:host=localhost;dbname=test', 'dbuser', 'dbpassword');

$db->exec("SET time_zone='$offset';");

The PHP and MySQL timezones are now synchronized within your application. No need to go for php.ini or MySQL console!