MySQL timezone change?

Question

How do I change my timezone which is currently in UTC to GMT +1, what is the correct line and do i just enter it in phpMyAdmin SQL execution?

My host just gave me this link http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html and went off so I'm kinda lost thanks

Answer 1

The easiest way to do this, as noted by Umar is, for example

mysql> SET GLOBAL time_zone = 'America/New_York';

Using the named timezone is important for timezone that has a daylights saving adjustment. However, for some linux builds you may get the following response:

#1298 - Unknown or incorrect time zone

If you're seeing this, you may need to run a tzinfo_to_sql translation... it's easy to do, but not obvious. From the linux command line type in:

mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql -u root mysql -p

Provide your root password (MySQL root, not Linux root) and it will load any definitions in your zoneinfo into mysql. You can then go back and run your

mysql> SET GLOBAL time_zone = timezone;

Answer 2

If SET time_zone or SET GLOBAL time_zone does not work, you can change as below:

• Change timezone system, example: ubuntu... $ sudo dpkg-reconfigure tzdata

• Restart the server or you can restart apache2 and mysql (/etc/init.d/mysql restart)

Answer 3

Here is how to synchronize PHP (>=5.3) and MySQL timezones per session and user settings. Put this where it runs when you need set and synchronized timezones.

date_default_timezone_set($my_timezone);
$n = new \DateTime();
$h = $n->getOffset()/3600;
$i = 60*($h-floor($h));
$offset = sprintf('%+d:%02d', $h, $i);
$this->db->query("SET time_zone='$offset'");

Where $my_timezone is one in the list of PHP timezones: http://www.php.net/manual/en/timezones.php

The PHP timezone has to be converted into the hour and minute offset for MySQL. That's what lines 1-4 do.

Answer 4

This works fine

<?php
      $con=mysqli_connect("localhost","my_user","my_password","my_db");
      $con->query("SET GLOBAL time_zone = 'Asia/Calcutta'");
      $con->query("SET time_zone = '+05:30'");
      $con->query("SET @@session.time_zone = '+05:30'");
?>

Answer 5

If you have the SUPER privilege, you can set the global server time zone value at runtime with this statement:

mysql> SET GLOBAL time_zone = timezone;

Answer 6

issue the command:

SET time_zone = 'America/New_York';

(Or whatever time zone GMT+1 is.: http://www.php.net/manual/en/timezones.php)

This is the command to set the MySQL timezone for an individual client, assuming that your clients are spread accross multiple time zones.

This command should be executed before every SQL command involving dates. If your queries go thru a class, then this is easy to implement.