调整RDS的默认时区设置 [英] Adjusting for the default time-zone setting on RDS
问题描述
我们最近切换到RDS实例，并注意到我们的许多数据库任务都比需要提前4小时被触发.在进一步调查中，问题是由RDS实例上的默认时区设置(UTC)引起的.由于无法更改此设置，因此我们希望使用该数据库实例在我们所有应用程序中的代码级全局解决此问题.我尝试通过使用
We recently switched to an RDS instance and noticed that bunch of our database tasks were getting triggered 4 hours earlier than needed. On investigating further, the problem is caused by the default time-zone setting (UTC) on the RDS instance. Since this setting can not be altered, we would like to fix the issue on the code level globally across all our applications using this database instance. I tried to set the time-zone on the db instance I create to 'US/Eastern' by using
set GLOBAL time_zone = 'US/Eastern'" OR
set time_zone = 'US/Eastern'"
但这会生成错误数据库错误:未知或不正确的时区:'美国/东部'"
But that generates an error "Database error: Unknown or incorrect time zone: 'US/Eastern'"
您认为我在这里做错了什么?有没有人使用过其他解决方案?
What do you think I am doing wrong here? Does anyone has used any other solutions ?
推荐答案
很遗憾，无法在RDS DB ParameterGroups中设置default_timezone，因此您的尝试已经是正确的方向.
Unfortunately it's not possible to set the default_timezone in the RDS DB ParameterGroups so your attempt was the right direction already.
$ rds-describe-db-parameters default | grep "time_zone"
DBPARAMETER default_time_zone engine-default string static false
要通过SET GLOBAL设置全局值，您需要具有SUPER特权，而该特权不以RDS用户身份授予您.
To set the global value via SET GLOBAL you need to have the SUPER privilege which is not granted to you as a RDS user.
设置time_zone的唯一方法是基于每个连接
The only way to set the time_zone is on a per-connection basis
mysql> SET time_zone = timezone;
在我的机器上，我已经成功地尝试了US/Eastern，但是我运行了相当老的一代.
On my machines I've tried US/Eastern successfully but I got a quite old generation running.
要确定可用的时区，请登录框
To determine the timezones you have available log into your box
mysql -h yourboxhost.rds.amazonaws.com -u <youruser> -p
然后输入
mysql> SELECT * FROM mysql.time_zone_name;
您应该获得可以在实例上设置的已安装有效时区名称的列表
You should get a list of installed and valid timezone names you can set on your instance
+----------------------------------------+--------------+
| Name | Time_zone_id |
+----------------------------------------+--------------+
| Africa/Abidjan | 1 |
| Africa/Accra | 2 |
| Africa/Addis_Ababa | 3 |
| Africa/Algiers | 4 |
| Africa/Asmara | 5 |
| Africa/Asmera | 6 |
| Africa/Bamako | 7 |
| Africa/Bangui | 8 |
| Africa/Banjul | 9 |
| Africa/Bissau | 10 |
| Africa/Blantyre | 11 |
| Africa/Brazzaville | 12 |
| Africa/Bujumbura | 13 |
| Africa/Cairo | 14 |
etc...
每次连接数据库服务器时都必须设置time_zone
You have to set the time_zone each time you connect to your database server
例如，如果您使用php Mysqli扩展名，则可以这样做
For example if you use the php Mysqli extension you can do this
$mysqli = mysqli_init();
mysqli_options($mysqli,MYSQLI_INIT_COMMAND,"SET time_zone = 'Africa/Brazzaville'" );
mysqli_real_connect($mysqli,$host, $user, $pass,$dbName) or die ('Unable to connect');
否则，只需手动(就让数据库连接器执行操作而言)，在连接到数据库后立即执行SET time_zone = '<YOUR_DESIRED_TIMEZONE>'查询
Otherwise just manually ( in terms of let your database connector do it ) execute the SET time_zone = '<YOUR_DESIRED_TIMEZONE>' Query right after you've connected to your database
这篇关于调整RDS的默认时区设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
