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

User · Answer

This works fine   lt  php        con mysqli connect  localhost   my user   my password   my db           con- gt query  SET GLOBAL time zone    Asia Calcutta            con- gt query  SET time zone     05 30            con- gt query  SET   session time zone     05 30       gt

User · Answer

The easiest way to do this  as noted by Umar is  for example  mysql gt  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 gt  SET GLOBAL time zone   timezone

User · Answer

If you have the SUPER  privilege  you can set the global server time zone value at runtime with this statement   mysql gt  SET GLOBAL time zone   timezone

User · Answer

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- gt getOffset   3600   i   60   h-floor  h     offset   sprintf    d  02d    h   i    this- gt db- gt 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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL timezone change?

Examples related to mysql

Examples related to timezone