4

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?

Community
  • 1
rich p
  • 233
  • 1
  • 3
  • 6

2 Answers2

2

Use default_time_zone=UTC in my.cnf.

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

Gea-Suan Lin
  • 385
  • 1
  • 2
  • 6
1

In ~/.my.cnf:

[mysql]
init_command="SET time_zone='+0:00'"
dolmen
  • 135
  • 1
  • 8