MySQL: setting time_zone in my.cnf options file

Question

In MySQL you can set a session variable called time_zone to change the timezone. This is useful e.g. when looking at timestamps from another country. Here is an example:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-12-30 18:59:18 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone='Brazil/East';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-12-30 09:59:29 |
+---------------------+
1 row in set (0.00 sec)

Is it possible to put that in an option file e.g. .my.cnf ?

When I try, it doesn't work. All I get is:

mysql: unknown variable 'time_zone=Brazil/East'

So far the answers work by changing settings the server side. I'm interested in an anwser that allows to change the `time_zone` for a `mysql` CLI session by setting something in `~/.my.cnf`. — dolmen, Jun 27 '18 at 14:29

score 19 · Accepted Answer · answered Dec 30 '10 at 12:23

19

it should be

default_time_zone=Brazil/East

details : http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_time_zone

Option-File Format = default_time_zone

answered Dec 30 '10 at 12:23

ajreal

44,929
10
81
118

3

I am getting `mysql: [ERROR] unknown variable 'default_time_zone=Brazil/East'` when putting this in my `/etc/mysql/conf.d/mysql.cnf` under section `[mysql]`. Version 5.7.20. – kev Dec 07 '17 at 05:28
3

Putting it under `[mysqld]` results in the server not restarting and exiting with: `Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.`. – kev Dec 07 '17 at 06:18
This answer changes the default *on the server*. But this doesn't seem to work in `~/.my.cnf` for a `mysql` CLI client. Note that the question explicitely mentions `.my.cnf`. So I'm voting down. – dolmen Jun 27 '18 at 14:23
@dolmen - Nonsense. The example can be applied to any *.cnf and I don't even explicitly state is for server conf. – ajreal Jun 28 '18 at 01:41
@ajreal. No this doesn't work for mysql CLI: `mysql: [ERROR] unknown variable 'default_time_zone=Brazil/East'`. And your link points to documentation about server configuration. – dolmen Jun 28 '18 at 08:42
@dolmen - OP does not ask for CLI solution. Can you please read the question again? Your problem is not the same to OP problem. – ajreal Jun 28 '18 at 09:13
@ajreal Please read the question again: it says `Is it possible to put that in an option file e.g. .my.cnf`. Note the dot at the beginning of the filename. – dolmen Jun 28 '18 at 09:21
@dolmen - This configuration is for mysqld instead of mysql. – ajreal Jun 28 '18 at 09:33
it should be `default-time-zone='Europe/Berlin'` with dashes instead of underscores: https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html – Tilmann Bach Jan 24 '20 at 15:19

score 2 · Answer 2 · answered Jun 24 '17 at 11:28

I'm not certain what has changed in Xampp, but this solution only works if you place this line in the proper place. Trust me I tried many times and had to do a pretty thorough search to find this solution.

default-time-zone = "+00:00"

Example:

read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-time-zone = "+00:00" <--- Place here.
log_error = "mysql_error.log"

https://community.apachefriends.org/f/viewtopic.php?f=16&t=47656

Also, you'll want to be sure that you have your database populated with the proper time zone names if you are going to use "America/Los_Angeles". I'd recommend using the offset. I'd actually recommend using UTC as your base then converting your time from that point for users based on their timezone which will save you many headaches later and keep your database nice and uniform. Check out the guide I linked below it explained it very clearly for me and I utilized this system. There are many ways to code it but taking this approach will save you a lot of issues.

http://www.vertabelo.com/blog/technical-articles/the-proper-way-to-handle-multiple-time-zones-in-mysql

score 2 · Answer 3 · answered Apr 24 '18 at 16:10

2

For MAMP, I added default_time_zone=-03:00 under [mysqld] in /Applications/MAMP/conf/my.cnf

I would get the following error for Brazil/East, probably because its deprecated(https://en.wikipedia.org/wiki/List_of_tz_database_time_zones):

[ERROR] Fatal error: Illegal or unknown default time zone 'Brazil/East'

answered Apr 24 '18 at 16:10

AEQ

1,199
1
15
18

For named time zones to work you have to load special tables. See https://dev.mysql.com/doc/refman/8.0/en/mysql-tzinfo-to-sql.html – dolmen Jun 27 '18 at 14:26

score 0 · Answer 4 · answered Mar 06 '18 at 10:09

A bit late, but this might be helpful nonetheless:

When explicitly setting the timezone, confirm that you are using the correct timezone name, keeping in mind the many have been deprecated. You can use https://en.wikipedia.org/wiki/List_of_tz_database_time_zones to confirm.

In my case, using MySQL 5.7, the deprecated timezone name was not working when adding it below the [mysqld] in my mysqld.cnf file. Using the new timezone name, and restarting the mysql service, worked.

So for user @kev here, using America/Sao_Paulo should work, instead of using Brazil/East.

score 0 · Answer 5 · answered Jun 28 '18 at 08:36

0

In ~/.my.cnf:

[mysql]
init_command="SET time_zone='Brazil/East'"

answered Jun 28 '18 at 08:36

dolmen

6,442
5
30
34

score 0 · Answer 6 · answered Jun 06 '19 at 11:06

Edit the following:

nano /etc/mysql/conf.d/mysql.cnf && systemctl restart mysql ; systemctl status mysql

MySQL.cnf:

[mysql]
default_time_zone=America/Vancouver

See here for current Time Zone formats --> https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

score 0 · Answer 7 · answered May 26 '21 at 09:54

For Windows 10:

Go to C:\ProgramData\MySQL\MySQL Server 8.0 folder and open my.ini file as Administrator.

Scroll to the [mysqld]: line.

Add this lines after the [mysqld]:

#default time-zone
default-time-zone = '<your_time_zone>'

Save the file and restart the MySQL80 service from Services window.

You can control changes from

MySQL Workbench -> Local instance -> Server -> 
Status and System Variables -> System Variables -> Filtered -> time_zone

MySQL: setting time_zone in my.cnf options file

7 Answers7

Linked