[mysql] MySQL timezone change?

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

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)

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;

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

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.

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;

While Bryon's answer is helpful, I'd just add that his link is for PHP timezone names, which are not the same as MySQL timezone names.

If you want to set your timezone for an individual session to GMT+1 (UTC+1 to be precise) just use the string '+01:00' in that command. I.e.:

SET time_zone = '+01:00';

To see what timezone your MySQL session is using, just execute this:

SELECT @@global.time_zone, @@session.time_zone;

This is a great reference with more details: MySQL 5.5 Reference on Time Zones