调整 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 设置全局值，您需要具有作为 RDS 用户未授予您的 SUPER 权限.
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
相关文章