Set timezone in PHP and MySQL

Question

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:

1. date_default_timezone_set('Europe/Paris'); It works only for PHP.
2. date.timezone= "Europe/Paris"; It works only for PHP.
3. SELECT CONVERT_TZ(now(), 'GMT', 'MET'); This return empty.
4. 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.

Solution 1

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.

Solution 2

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';");

Solution 3

I can change my mysql default timezone from variable section by editing row which says "time zone" in phpmyadmin.

Here you can also change format and lot more you can find in mysql support http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html, I hope its help you.

You can put same timezone for both php and mysql.

Solution 4

The best method Set timezone in PDO MySQL:

If appropriate, whether by mistake you can use: date('P') Example:

new PDO('mysql:host=localhost;dbname=nametable', 
 'username', 
 'password', 
 [PDO::MYSQL_ATTR_INIT_COMMAND =>"SET NAMES utf8;SET time_zone = '".date('P')."'"]);

Solution 5

For PHP use this function:

date_default_timezone_set()

MySQL:

default-time-zone='timezone'

If you have the root privilege, you can set the global server time zone value at run-time with this statement:

SET GLOBAL time_zone = timezone;

Per-connection time zones. Each client that connects has their own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

SET time_zone = timezone;

View more solutions

Comments

[Learning and sharing]

Thank you very much for the answer, and had seen this article, but I think it's a lot of work to do this, is there any way to make it easier? You can change the time zone myslq from php.ini?

[Thamilhan]

I don't think adding a couple of lines makes your work harder! By the way you are not required to go to console for changes right? php.ini is for php configuration, I don't think there is a way out there for mysql configuration in that php's file

[Learning and sharing]

I'm using CodeIgniter framework for query as the database, and I'm using these examples but displays an error, you will know how to apply this example in codeigniter? codeigniter.com/user_guide/database/…

[Learning and sharing]

These values [ default-time-zone='timezone' ] apply in php.ini? As I can apply these other options it on a hosting?

[Thamilhan]

I haven't tried in CI, but I found SO answer here stackoverflow.com/a/20488347/5447994 Hope it might work for you. And an article: mytricks.org/set-deafult-timezone-codeigniter

[Learning and sharing]

Excellent thank you very much, you have a good day, I would see an easier option as PHP would be perfect.

[Learning and sharing]

In the article you shared, shows a very practical example, specify in the query, the time zone example: mysql_query("SET time_zone = '+1:00'"); I'm doing it this way and it works, but I need to do it this way mysql_query("SET time_zone='Europe/Paris'"); and not working, will you have to adjust a parameter?

[Thamilhan]

You should use SET time_zone='$offset'; not SET time_zone='Europe/Paris'

[Semra]

Can be rewritten as $sec = (new DateTime())->getOffset(); $offset = ($sec < 0 ? '-' : '+') . gmdate('G:i', abs($sec)); db_query("SET time_zone='$offset'");

[mike.k]

You can use $offset = date('P'); to skip a few steps