How do I set the time zone of MySQL

Question

On one server  when I run   mysql gt  select now     ---------------------    now                    ---------------------    2009-05-30 16 54 29    ---------------------  1 row in set  0 00 sec    On another server   mysql gt  select now     ---------------------    now                    ---------------------    2009-05-30 20 01 43    ---------------------  1 row in set  0 00 sec

User · Answer

This is a 10 years old question  but anyway here s what worked for me  I m using MySQL 8 0 with Hibernate 5 and SpringBoot 4   I ve tried the above accepted answer but didn t work for me  what worked for me is this   db url jdbc mysql   localhost 3306 testdb useSSL false amp serverTimezone Europe Warsaw   If this helps you don t forget to upvote it  D

User · Answer

If you are using the MySql Workbench you can set this by opening up the administrator view and select the Advanced tab   The top section is  Localization  and the first check box should be  default-time-zone    Check that box and then enter your desired time zone  restart the server and you should be good to go

User · Answer

To set the standard time zone at MariaDB you have to go to the 50-server cnf file   sudo nano  etc mysql mariadb conf d 50-server cnf   Then you can enter the following entry in the mysqld section   default-time-zone   01 00    Example       These groups are read by MariaDB server    Use it for options that only the server  but not clients  should see     See the examples of server my cnf files in  usr share mysql       this is read by the standalone daemon and embedded servers  server     this is only for the mysqld standalone daemon  mysqld         Basic Settings   user              mysql pid-file           var run mysqld mysqld pid socket             var run mysqld mysqld sock port              3306 basedir            usr datadir            var lib mysql tmpdir             tmp lc-messages-dir    usr share mysql skip-external-locking      Default timezone     default-time-zone   01 00     Instead of skip-networking the default is now to listen only on   localhost which is more compatible and is not less secure    The change must be made via the configuration file  otherwise the MariaDB server will reset the mysql tables after a restart

User · Answer

You can do this easily by changing the OS time zone if match your scenario  In Ubuntu  to list time zones  run this command sudo timedatectl list-timezones  To change the OS time zone  run this command with your timezone timedatectl set-timezone America New York  Check The OS time zone  run date  Then restart the MySQL sudo service mysql restart  To Chek time zone in MySQL  login and run SELECT TIMEDIFF NOW    UTC TIMESTAMP

User · Answer

If anyone is using GoDaddy Shared Hosting  you can try for following solution  worked for me   When starting DB connection  set the time zone command in my PDO object e g     pdo   new PDO  dsn   user   pass   opt    pdo- gt exec  SET time zone   05 30        Where   05 30  is the TimeZone of India  You can change it as per your need    After that  all the MySQL processes related to Date and Time are set with required timezone   Source   https   in godaddy com community cPanel-Hosting How-to-change-TimeZone-for-MySqL td-p 31861

User · Answer

On Windows  IIS  in order to be able to SET GLOBAL time zone    Europe Helsinki   or whatever  the MySQL time zone description tables need to be populated first   I downloaded these from this link https   dev mysql com downloads timezones html  After running the downloaded SQL query I was able to set the GLOBAL time zone and resolve the issue I had where SELECT NOW    was returning GMT rather than BST

User · Answer

I thought this might be useful   There are three places where the timezone might be set in MySQL   In the file  my cnf  in the  mysqld  section  default-time-zone   00 00      global time zone variable  To see what value they are set to   SELECT   global time zone    To set a value for it use either one   SET GLOBAL time zone     8 00   SET GLOBAL time zone    Europe Helsinki   SET   global time zone     00 00      Using named timezones like  Europe Helsinki  means that you have to have a timezone table properly populated    Keep in mind that  02 00 is an offset  Europe Berlin is a timezone  that has two offsets  and CEST is a clock time that corresponds to a specific offset     session time zone variable  SELECT   session time zone    To set it use either one   SET time zone    Europe Helsinki   SET time zone     00 00   SET   session time zone     00 00     Both might return SYSTEM which means that they use the timezone set in my cnf   For timezone names to work  you must setup your timezone information tables need to be populated  http   dev mysql com doc refman 5 1 en time-zone-support html  I also mention how to populate those tables in this answer   To get the current timezone offset as TIME  SELECT TIMEDIFF NOW    UTC TIMESTAMP     It will return 02 00 00 if your timezone is  2 00   To get the current UNIX timestamp   SELECT UNIX TIMESTAMP    SELECT UNIX TIMESTAMP NOW       To get the timestamp column as a UNIX timestamp  SELECT UNIX TIMESTAMP  timestamp   FROM  table name    To get a UTC datetime column as a UNIX timestamp  SELECT UNIX TIMESTAMP CONVERT TZ  utc datetime     00 00     session time zone   FROM  table name    Note  Changing the timezone will not change the stored datetime or timestamp  but it will show a different datetime for existing timestamp columns as they are internally stored as UTC timestamps and externally displayed in the current MySQL timezone   I made a cheatsheet here  Should MySQL have its timezone set to UTC

User · Answer

You can specify the server s default timezone when you start it  see http   dev mysql com doc refman 5 1 en server-options html and specifically the --default-time-zone timezone option  You can check the global and session time zones with  SELECT   global time zone    session time zone    set either or both with the SET statement   c  see http   dev mysql com doc refman 5 1 en time-zone-support html for many more details

User · Answer

For anyone still having this issue   value  jdbc mysql   localhost 3306 dbname serverTimezone UTC    Worked for me  Just append  serverTimezone UTC at the end

User · Answer

In my case  the solution was to set serverTimezone parameter in Advanced settings to an appropriate value  CET for my time zone    As I use IntelliJ  I use its Database module  While adding a new connection to the database and after adding all relevant parameters in tab General  there was an error on  Test Connection  button  Again  the solution is to set serverTimezone parameter in tab Advanced

User · Answer

From MySQL Workbench 8 0 under the server tab  if you go to Status and System variables you can set it from here

User · Answer

Keep in mind  that  Country Zone  is not working sometimes    This issue is not OS  MySQL version and hardware dependent - I ve met it since FreeBSD 4 and Slackware Linux in year 2003 till today  MySQL from version 3 till latest source trunk  It is ODD  but it DOES happens  For example   root Ubuntu  ls -la  usr share zoneinfo US total 8  drwxr-xr-x  2 root root 4096 Apr 10  2013   drwxr-xr-x 22 root root 4096 Apr 10  2013    lrwxrwxrwx  1 root root   18 Jul  8 22 33 Alaska - gt     SystemV YST9YDT lrwxrwxrwx  1 root root   21 Jul  8 22 33 Aleutian - gt     posix America Adak lrwxrwxrwx  1 root root   15 Jul  8 22 33 Arizona - gt     SystemV MST7 lrwxrwxrwx  1 root root   18 Jul  8 22 33 Central - gt     SystemV CST6CDT lrwxrwxrwx  1 root root   18 Jul  8 22 33 Eastern - gt     SystemV EST5EDT lrwxrwxrwx  1 root root   37 Jul  8 22 33 East-Indiana - gt     posix America Indiana Indianapolis lrwxrwxrwx  1 root root   19 Jul  8 22 33 Hawaii - gt     Pacific Honolulu lrwxrwxrwx  1 root root   24 Jul  8 22 33 Indiana-Starke - gt     posix America Knox IN lrwxrwxrwx  1 root root   24 Jul  8 22 33 Michigan - gt     posix America Detroit lrwxrwxrwx  1 root root   18 Jul  8 22 33 Mountain - gt     SystemV MST7MDT lrwxrwxrwx  1 root root   18 Jul  8 22 33 Pacific - gt     SystemV PST8PDT lrwxrwxrwx  1 root root   18 Jul  8 22 33 Pacific-New - gt     SystemV PST8PDT lrwxrwxrwx  1 root root   20 Jul  8 22 33 Samoa - gt     Pacific Pago Pago root Ubuntu    And a statement like that is supposed to work   SET time zone  US Eastern     But you have this problem      Error Code  1298  Unknown or incorrect time zone   EUS Eastern    Take a look at the subfolder in your zone information directory  and see the ACTUAL filename for symlink  in this case it s EST5EDT  Then try this statement instead   SET time zone  EST5EDT     And it s actually working as it is supposed to     Keep this trick in mind  I haven t seen it to be documented in MySQL manuals and official documentation  But reading the corresponding documentation is must-do thing  MySQL 5 5 timezone official documentation - and don t forget to load timezone data into your server just like that  run as root user     mysql tzinfo to sql  usr share zoneinfo   mysql -u root mysql   Trick number one - it must be done exactly under MySQL root user  It can fail or produce non-working result even from the user that has full access to a MySQL database - I saw the glitch myself

User · Answer

This work for me for a location in India   SET GLOBAL time zone    Asia Calcutta   SET time zone     05 30   SET   session time zone     05 30

User · Answer

Ancient question with one more suggestion   If you ve recently changed the timezone of the OS  e g  via   unlink  etc localtime ln -s  etc usr share zoneinfo US Eastern  etc localtime       MySQL  or MariaDB  will not notice until you restart the db service   service mysqld restart    or   service mariadb restart

User · Answer

Edit the MySQL config file  sudo nano  etc mysql my cnf   Scroll and add these to the bottom  Change to relevant time zone   mysqld  default-time-zone     00 00    Restart the server  sudo service mysql restart

User · Answer

You have to set up  the your location timezone  So that follow below process Open your MSQLWorkbench  write a simple sql command like this    select now      And also your url could be like this    url    jdbc mysql   localhost 3306 your database name serverTimezone UTC

User · Answer

When you can configure the time zone server for MySQL or PHP   Remember    Change timezone system  Example for Ubuntu     sudo dpkg-reconfigure tzdata  Restart the server or you can restart Apache 2 and MySQL    etc init d mysql restart

User · Answer

If you re using PDO    offset   10 00    db- gt exec  SET time zone     offset          If you re using MySQLi    db- gt MySQLi- gt query  SET time zone     offset          More about formatting the offset here  https   www sitepoint com synchronize-php-mysql-timezone-configuration

User · Answer

Set MYSQL timezone on server by logging to mysql server there set timezone value as required  For IST SET SESSION time zone     5 30    Then run SELECT NOW

User · Answer

Simply run this on your MySQL server   SET GLOBAL time zone     8 00     Where  8 00 will be your time zone

User · Answer

First to figure out what the time zone is you can query   SHOW VARIABLES LIKE   time zone       Your output should be something similar as follows     Variable name         Value   system time zone      CDT time zone             SYSTEM   Then if you want to confirm that you are in say some time zone like CDT instead of something like EST you can check what time it thinks your machine is in by saying   SELECT NOW      If this is not the time you want you need to change it    all you need to do is SET time zone   timezone name  Make sure it is one that is in Continent City format    If you are on a shared server because you have a hosting service please refer to these answers regarding changing the php ini file or the  htaccess file

User · Answer

To set it for the current session  do   SET time zone   timezonename

[mysql] How do I set the time zone of MySQL?

Examples related to mysql

Examples related to timezone