HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to persistently set MySQL (Linux mysql command) session time zone to UTC?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
zoneutctimemysqlpersistentlylinuxhowsessioncommandset

Problem

I'm using MySQL on Linux (CentOS, recent release). I want the MySQL system variable time_zone to reflect UTC.

When I start a MySQL session (using the command-line tool mysql) and check the time zone setting, I see this:

mysql> select @session.time_zone ;
+--------------------+
| @session.time_zone |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)


I know (see MySQL Set UTC time as default timestamp) that I can configure the server to have UTC as its default timezone. What I want is to set up a client session to have UTC as the default.

The page above says I can set a MySQL system variable in my SQL syntax, like this:

mysql> set @session.time_zone="+0:00";


This leads to having the session time zone to be the local (SYSTEM) time zone:

mysql> show variables like '%zone%' ;
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | PDT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)


What I'd like to do is to set the time zone as a kind of preference for a user, like a setting in .my.cnf.

Problem is, the MySQL 'mysql' command options don't list a 'time zone' option for .my.cnf, and (of course I had to try it anyway) putting

time_zone="+0:00"


in my $HOME/.my.cnf file leads to the message

mysql: unknown variable 'time_zone=+0.00'


I tried setting the TZ environment variable, and that has no impact.

As the StackExchange reference above says, the only effective way I've found is through issuing the SQL command:

```
mysql> select @session.time_zone ;
+--------------------+
| @session.time_zone |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)

mysql> set @session.time_zone="+0:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select @session.time_zone ;
+--------------------+
| @session.time_zone |
+--------------------+
| +0

Solution

Use default_time_zone=UTC in my.cnf.

Ref: https://stackoverflow.com/questions/4562456/mysql-setting-time-zone-in-my-cnf-options-file

Context

StackExchange Database Administrators Q#151182, answer score: 2

Revisions (0)

No revisions yet.