Tutorial :How do I set the time zone of MySQL?



Question:

On one server, when I run:

mysql> select now();  +---------------------+  | now()               |  +---------------------+  | 2009-05-30 16:54:29 |  +---------------------+  1 row in set (0.00 sec)  

On another server:

mysql> select now();  +---------------------+  | now()               |  +---------------------+  | 2009-05-30 20:01:43 |  +---------------------+  1 row in set (0.00 sec)  


Solution:1

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?


Solution:2

To set it for the current session, do:

SET time_zone = timezonename;  


Solution:3

When you can configure the time zone server for MySQL or PHP:

Remember:

  1. Change timezone system. Example for Ubuntu:

    $ sudo dpkg-reconfigure tzdata`  
  2. Restart the server or you can restart Apache 2 and MySQL:

    `/etc/init.d/mysql restart`  


Solution:4

Simply run this on your MySQL server:

SET GLOBAL time_zone = '+8:00';  

Where +8:00 will be your time zone.


Solution:5

For anyone still having this issue:

value="jdbc:mysql://localhost:3306/dbname?serverTimezone=UTC"  

Worked for me. Just append ?serverTimezone=UTC at the end.


Solution:6

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";  


Solution:7

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.


Solution:8

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 -> ../SystemV/YST9YDT  lrwxrwxrwx  1 root root   21 Jul  8 22:33 Aleutian -> ../posix/America/Adak  lrwxrwxrwx  1 root root   15 Jul  8 22:33 Arizona -> ../SystemV/MST7  lrwxrwxrwx  1 root root   18 Jul  8 22:33 Central -> ../SystemV/CST6CDT  lrwxrwxrwx  1 root root   18 Jul  8 22:33 Eastern -> ../SystemV/EST5EDT  lrwxrwxrwx  1 root root   37 Jul  8 22:33 East-Indiana -> ../posix/America/Indiana/Indianapolis  lrwxrwxrwx  1 root root   19 Jul  8 22:33 Hawaii -> ../Pacific/Honolulu  lrwxrwxrwx  1 root root   24 Jul  8 22:33 Indiana-Starke -> ../posix/America/Knox_IN  lrwxrwxrwx  1 root root   24 Jul  8 22:33 Michigan -> ../posix/America/Detroit  lrwxrwxrwx  1 root root   18 Jul  8 22:33 Mountain -> ../SystemV/MST7MDT  lrwxrwxrwx  1 root root   18 Jul  8 22:33 Pacific -> ../SystemV/PST8PDT  lrwxrwxrwx  1 root root   18 Jul  8 22:33 Pacific-New -> ../SystemV/PST8PDT  lrwxrwxrwx  1 root root   20 Jul  8 22:33 Samoa -> ../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.


Solution:9

If you're using PDO:

$offset="+10:00";  $db->exec("SET time_zone='".$offset."';");  

If you're using MySQLi:

$db->MySQLi->query("SET time_zone='".$offset."';");  

More about formatting the offset here: https://www.sitepoint.com/synchronize-php-mysql-timezone-configuration/


Solution:10

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.


Solution:11

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  


Solution:12

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 coul be like this;

url = "jdbc:mysql://localhost:3306/your_database_name?serverTimezone=UTC";


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »