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

Question

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:00 |
+--------------------+
1 row in set (0.00 sec)

So...

My question then is, is there some other mechanism for setting MySQL session variables? Or some other way to set the session time zone?

• dolmen: The proper way to show the time_zone active in the current session is SELECT @@time_zone;. That value is the one defined locally in the session or the one inherited from the server setting.

Answer 1

Use default_time_zone=UTC in my.cnf.

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

• kev: You should add which section it should live under and which MySQL version you are talking about.
• dolmen: my.cnf is not ~/.my.cnf. This question is explicitely about setting the session timezone in a client session. But the answers in the link so far only give solutions by changing a server setting that affects all sessions.

Answer 2

In ~/.my.cnf:

[mysql]
init_command="SET time_zone='+0:00'"