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

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)

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

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&serverTimezone=Europe/Warsaw

If this helps you don't forget to upvote it :D

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.

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

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.

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.

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

To set it for the current session, do:

SET time_zone = timezonename;

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

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!

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

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.

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.

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.

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?

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/

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
   

For anyone still having this issue:

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

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

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();

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

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

Simply run this on your MySQL server:

SET GLOBAL time_zone = '+8:00';

Where +8:00 will be your time zone.