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)

Caste

There's not just a timezone difference here - but a not-so-insignificant time-drift as well: "Talk to your DBA about ntp - and see what's right for you!" – Barfly

@Barfly Calm down it's probably the difference between running the queries – Espino

@Espino by 7 minutes? Coffee break in between comparisons? – Barfly

system_time_zone - "When the server begins executing, it inherits a time zone setting from the machine defaults". To change the "system_time_zone": Use sudo dpkg-reconfigure tzdata and restart mysql with sudo service mysql restart. – Mummer

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

Kurrajong

+00:00 isn't a timezone, it's a time offset. What happens with regards to DST? Does it stay at +0 year-round? – Forty

@Mark I am not sure, the docs say that when setting time_zone="+00:00" you are setting the timezone using an offset from UTC, considering that the set value never changes and that UTC does not follow DST I can assume that it stays the same all year round. – Kurrajong

@TimoHuovinen Sorry, I meant that +00:00 looks like an offset so it's kind of strange that MySQL chose that to represent UTC instead of just using the string "UTC" itself. Thanks for the info. – Forty

I set +00:00, but I get this error: error: Found option without preceding group in config file: /etc/my.cnf – Adamant

@János check the encoding of the file, the BOM and the quotes around +00:00 #8020797 – Kurrajong

it is not encoding or something like that, I typed it manually – Adamant

I have mac and I am using vi ;-) – Adamant

in Win7, the path to the mysql settings file is C:\ProgramData\MySQL\MySQL Server x.x\my.ini – Kablesh

For me, the underscores in the variable name in the answer, were actually hyphens, as per dev.mysql.com/doc/refman/5.5/en/time-zone-support.html – Ander

@Ander could you point out the section where it's hyphens? because normally in SQL statements you use underscores – Kurrajong

Add in mysql config (my.ini) in [mysqld] section [mysqld] default-time-zone = UTC – Statocyst

FYI, I had to restart the mysql service (sudo service mysql restart) in order for it to detect system tz changes. On Ubuntu 14.04. – Bromeosin

what about different solar time? every country in europe change the clock different itm of the year – Uralite

@Uralite I know, it's called DST, the offset in hours does not take that into account, DATETIME fields are stored just like plain strings would, TIMESTAMP gets converted from the current timezone and stored in UTC and converted back to the current timezone when retrieving it, which should work for named timezones like America/New_York. I suggest set your db timezone to UTC and store everything as UTC and convert it back when fetching. – Kurrajong

To get current timezone you may also use: select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')) – Lopes

note that setting default-time-zone='+00:00' in the config file will make the value used every time the server restarts. but using SET GLOBAL time_zone = '+8:00'; query -with privilege account- will not make the server use the value when it is restarted. So I prefer the config file as the edit will be permanent . I'm using mariaDB on windows 7 comes in xampp – Burdette

Im confused as to why mysql does not use the system time by default instead of UTC. Sure for some applications UTC would be the desired time (I can't think of one but im sure there are some). Mostly though I would assume that the system time would be more desirable, especially since the time can be kept in sync with the chosen timezone so handles DST without any changes. ofcourse this will affect applications that are critically reliant on time, such as banking transactions but for the most part surely not such a big deal? – Cowbane

@Chris IMHO UTC is the desired time for all applications without exceptions. You will not believe how much of a mess timezones are, and they keep changing even now! It's much easier to avoid all ambiguity and use UTC and then convert from UTC to whatever timezone you feel like, so the app becomes easier to make international. – Kurrajong

@Chris here is a very good article on when not to use UTC codeblog.jonskeet.uk/2019/03/27/… basically when the time stored represents a future human event. – Kurrajong

Answer 2

For anyone still having this issue:

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

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

Nedrud

If my time zone is UTC+7, how could i specify that in the connection string ? I tried appending "?serverTimezone=UTC+7" and it doesn't work. – Oldcastle

The question was not about JDBC, but MySQL itself. – Obolus

@ChiếnNghê: You should use Asia/Ho_Chi_Minh instead of UTC BTW, the question is about changing MySQL timezone. So please look at @Timo answer. If you have trouble with MySQL docker, add TZ environment and it should work. – Mcmurray

This only informs JDBC what's the server timezone, it does not change it. To set session timezone, check answers which use SET time_zone=... – Atheist

The simplest solution – Outplay

Answer 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
   

Clynes

Maybe this approach may not be the right one. Think about the situation of having a server with UTC but your application is serving data to a country on UTC -0600. To adjust the timezone for MySQL, could be much better. – Sag

Especially if you work in an area that covers multiple timezones. – Plier

Answer 4

To set it for the current session, do:

SET time_zone = timezonename;

Grisby

SELECT @@session.time_zone; – Grisby

I'm trying fix some errors on my system, and I need to set my time_zone to UTC, not GMT. Do you know if I set to '-0:00' or '+00:00' this is a UTC annotation or GMT annotation? I'm kind of NOT finding this specific information. Cheers! – Skivvy

@Castanho, I'm pretty sure either one will work fine. Check out the explanation on this question: #3467130 – Grisby

The value for the time zone can be the same as the one set in PHP, such as SET time_zone = 'America/Boise'. Thanks for the answer, James Skidmore! – Cowage

For names to work in this context they'll need to have been loaded into the mysql time zone information tables. To the best of my knowledge this is NOT done by default in most installations; instructions on loading them from your system's zoneinfo tables can be found here: dev.mysql.com/doc/refman/5.5/en/time-zone-support.html – Rodmur

Does this setting get lost if they restart the server? – Concrescence

@Concrescence yes, the SET time_zone = 'UTC'; setting gets lost during server restart – Kurrajong

Answer 5

Simply run this on your MySQL server:

SET GLOBAL time_zone = '+8:00';

Where +8:00 will be your time zone.

Villon

Note, that this is only temporary until you restart Mysql – Tight

Rarely does one have superuser privileges when connecting to a DB. Setting @@session.time_zone as described in other answers is a much better route. – Barfly

This would become inaccurate once daylight savings time hits as you'd go from +8:00 to +9:00 (for timezones that use DST). – Pudendas

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

Exequies

you need to run this as query on mysql or write under my.conf with following syntax default-time-zone = '+05:30' – Exequies

should i re-run the code after restart the server ? – Fremd

Answer 7

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

Whitewall

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

Monodrama

Please tell me your uname -a and further details - have some working cases on Ubuntu desktop and server. – Monodrama

You're a hero. . . – Agathy

Answer 9

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.

Alundum

Answer 10

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/

Nameplate

Answer 11

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

Enyo

Answer 12

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

Matteo

Answer 13

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.

Tragacanth

I've tried mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p E first and then SET time_zone = timezone_name worked fine. – Mauriac

Answer 14

if you use of named time-zone results in an error:

mysql> SET GLOBAL time_zone = "Asia/Bangkok";    
ERROR 1298 (HY000): Unknown or incorrect time zone: 'Asia/Bangkok'

you can try:

mysql_tzinfo_to_sql tz_file tz_name | mysql -u root -p mysql

and then:

SET GLOBAL time_zone = "Asia/Bangkok";
SET time_zone = "+07:00";
SET @@session.time_zone = "+07:00";

check what time is it:

SELECT NOW();

ref: https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html#time-zone-installation

Astatine

Answer 15

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.

Maggot

The issue is related to MySQL, not to a particular program. – Twyla

Found nothing like that. – Ultimatum

Nothing like that – Desdee

For MySQL Workbench 6.3: Instance => Options file => General => International – Budde

[UPDATE for MySQL Workbench 8.0] Select "Server" => "Options File", and on the "General" tab see below the "International" section. There will be "default-time-zone" checkbox along with related field. So you can set the default time zone value there. – Thera

Answer 16

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!

Refutation

Answer 17

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

Ileneileo

Answer 18

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

Illusive

Answer 19

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

Breezeway

Answer 20

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

Maharaja

@LuFFy this is what I was wondering about, so for each db-connect the time-zone must be set IF we are using the session-scope time-zone and not the 'global'-scope time-zone. Its good to use session-scope if the app can have users in different time-zones, right? – Aluminothermy

Answer 21

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.

Hemlock

I could use the number format "+01:00" instead of "Europe/Stockholm" so I didn't need to dowload time_zone description tables, I think. – Aluminothermy

Answer 22

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.

Kordofanian

Answer 23

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

Miniver