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?

  • 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. – dolmen Jun 27 '18 at 14:39
2

Use default_time_zone=UTC in my.cnf.

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

  • You should add which section it should live under and which MySQL version you are talking about. – kev Dec 7 '17 at 6:23
  • 1
    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. – dolmen Jun 27 '18 at 14:35
0

In ~/.my.cnf:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.