MYSQL set timezone in PHP code

Question

How can I change MYSQL TIMEZONE to GMT format like this function in PHP: /SET GMT TIMEZONE/ date_default_timezone_set('Etc/GMT');

My DB class is here:

class DB {
    private static $instance;
    private $MySQLi;

    private function __construct(array $dbOptions){

        $this->MySQLi = @ new mysqli(   $dbOptions['db_host'],
                                        $dbOptions['db_user'],
                                        $dbOptions['db_pass'],
                                        $dbOptions['db_name'] );

        if (mysqli_connect_errno()) {
            throw new Exception('Database error.');
        }

        $this->MySQLi->set_charset("utf8");
    }

    public static function init(array $dbOptions){
        if(self::$instance instanceof self){
            return false;
        }

        self::$instance = new self($dbOptions);
    }

    public static function getMySQLiObject(){
        return self::$instance->MySQLi;
    }

    public static function query($q){
        return self::$instance->MySQLi->query($q);
    }

    public static function prepare($q){
        return self::$instance->MySQLi->prepare($q);
    }

    public static function esc($str){
        return self::$instance->MySQLi->real_escape_string(htmlspecialchars($str));
    }

}

And my queries like this in other files:

DB::query('UPDATE `calendar_data` SET `data` = "'.DB::esc(json_encode($array)).'", `upcoming_time` = "'.date('Y-m-d H:i:s', $upcoming).'", `time_now` = NOW() WHERE `id` = "1"');

Where should I insert my query to the class for run it once?

Thanks!

$this->MySQLi->set_charset("utf8");
$this->MySQLi->query("SET timezone = 'GMT'");

• Doesn't work. If I use NOW(), server time was inserted (GMT-4). Where is a problem???

Answer 1

Try the following:

$this->MySQLi->query("SET time_zone = '+0:00'");

Using named timezones will only work if the time zone information tables in the MySQL database have been created and populated.

Answer 2

After you write this:

$this->MySQLi->set_charset("utf8");

Also write this:

$this->MySQLi->query("SET timezone = 'GMT'");

This timezone setting will apply to all future queries sent on that connection.

Also, ensure you've set up the zone tables in the mysql database.

http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html

Answer 3

From manual;

Per-connection time zones. Each client that connects has its 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:

mysql> SET time_zone = timezone;

So must be like this;

$this->MySQLi->query("SET time_zone = 'GMT'");

http://dev.mysql.com/doc/refman/5.5/en//time-zone-support.html
http://dev.mysql.com/doc/refman/5.5/en//server-system-variables.html#sysvar_time_zone

Answer 4

MySQL has extensive timezone support. You can set it on server level, on a per connection basis and even per query. I think executing 'SET time_zone = xxx' in __construct is enough for you.