I am making an application where I need to store th date in MySQL using the PHP date()
function.
<?php $finalize_at = date('Y-m-d H:i:s'); ?>
These dates need to be compared 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 in php.ini
and make these values available for both PHP and MySQL.
Much appreciate your support.
The MySQL NOW() function returns the current date and time in the configured time zone as a string or a number in the 'YYYY-MM-DD HH:MM:DD' or 'YYYYMMDDHHMMSS.
The default timezone for PHP is UTC regardless of your server's timezone. This is the timezone used by all PHP date/time functions in your scripts. See PHP's list of supported timezones to find the names of all possible timezones you can use for the date.
To explicitly specify the system time zone for MySQL Server at startup, set the TZ environment variable before you start mysqld. If you start the server using mysqld_safe, its --timezone option provides another way to set the system time zone. The permissible values for TZ and --timezone are system dependent.
The date_default_timezone_get() function returns the default timezone used by all date/time functions in the script.
In PHP:
<?php define('TIMEZONE', 'Europe/Paris'); date_default_timezone_set(TIMEZONE);
For MySQL:
<?php $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!
This is from this article on SitePoint.
You can do it easily just by the following two lines of PHP.
$tz = (new DateTime('now', new DateTimeZone('Asia/Kabul')))->format('P'); $pdo->exec("SET time_zone='$tz';");
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With