snippetsqlMinor
How to persistently set MySQL (Linux mysql command) session time zone to UTC?
Viewed 0 times
zoneutctimemysqlpersistentlylinuxhowsessioncommandset
Problem
I'm using MySQL on Linux (CentOS, recent release). I want the MySQL system variable
When I start a MySQL session (using the command-line tool
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:
This leads to having the session time zone to be the local (
What I'd like to do is to set the time zone as a kind of preference for a user, like a setting in
Problem is, the MySQL 'mysql' command options don't list a 'time zone' option for
in my
I tried setting the
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
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) puttingtime_zone="+0:00"in my
$HOME/.my.cnf file leads to the messagemysql: 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
Ref: https://stackoverflow.com/questions/4562456/mysql-setting-time-zone-in-my-cnf-options-file
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.