如何为AWS RDS实例设置数据库时区 [英] How to set database timezone for a AWS RDS instance

问题描述

我们在AWS RDS实例上使用最新的MySQL服务器，并已配置为在US-East数据中心上运行它.我们假设任何新的Date()或Time.now()调用都将在数据库服务器运行所在的时区中存储日期.

We use the latest MySQL server on the AWS RDS instance and we have configured to run it on US-East data center. We are assuming that any new Date() or Time.now() invocation will store the date in the timezone in which the database server is running.

是否可以将在美国东部运行的AWS RDS实例指向PST时区，以便任何持久性日期都将值存储在PST中而不是EST中. (即，如果将对象存储在美国东部标准时间上午10点左右，则db列应改为反映美国东部标准时间上午7点).

Is there a way to point my AWS RDS instance running on US-East to point to the PST timezone, so that any persistence of date will store the values in PST instead of EST. (i.e. If store an object around 10 AM EST, the db column should reflect 7 AM EST instead).

推荐答案

在回答其余问题之前，我现在想建议一下，如果您可以选择将应用程序更改为使用UTC，它将为您节省很多时间现在和将来都充满悲痛.

Before the rest of my answer, I'd just like to recommend right now that if you have any option to change your application to use UTC it will save you a lot of grief now and in the future.

但是，考虑到您所要解决的问题，我假设这不是一个选择，并且您正在调整一个设计用于在传统服务器环境中使用MySQL的系统，您只需更改时区即可，期望该时区并且不能轻易适应使用UTC的代码逻辑.

However given the context of your question, I'm assuming that this isn't an option and that you're adapting a system designed to use MySQL in a traditional server environment where you can just change the timezone, and that there's code logic that expects this timezone and can't be easily adapted to use UTC.

但是，如果您真的需要将其存储为PST，请继续阅读.

But if you really need it to store as PST, read on.

您是正确的，默认情况下，mySql将使用服务器的时区来存储时间戳，但是您认为RDS实例根据其启动所在的AWS区域设置时区的假设是不正确的-所有RDS实例均以其时区设置为UTC的形式启动，并且无法更改此配置:

You're correct that mySql will use the server's timezone for timestamp storage by default, however your assumption that RDS instances have their timezones set based on the AWS region in which they are launched is incorrect - all RDS instances are launched with their timezone set as UTC, and this configuration can't be changed:

该时区当前不可修改.确实有一个 rds-describe-db-parameters中的参数值称为 "default_time_zone"，但标记为不可修改.

The time zone is currently not modifiable. There is indeed a Parameter value in rds-describe-db-parameters called "default_time_zone" but it's marked as not modifiable.

因此，您唯一的选择是将应用程序与数据库实例建立的每个连接上的时区设置为PST.您可以按照在此处找到的两个步骤，使用SET SESSION time_zone = 'PST'查询在应用程序进行的每个连接中执行: /p>

So your only option is to set the timezone on each connection your application makes to the database instance to PST. You can use the SET SESSION time_zone = 'PST' query to execute in every connection an application makes by following the two steps found here:

1. 创建以下存储过程(UTC-8是PST):

1. Create the following stored procedure (UTC-8 is PST):

DELIMITER |  
CREATE PROCEDURE mysql.store_time_zone ()  
   IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN     
       SET SESSION time_zone = '-8:00';  
   END IF 
|
DELIMITER ;

连接到您的实例，然后运行以下命令:

Connect to your instance, and run the following command:

$ rds-modify-db-parameter-group PARAMGROUP --parameters "name=init_connect, value='CALL mysql.store_time_zone', method=immediate"

您可能需要向将要连接到数据库的用户授予EXECUTE权限，否则可能会出现连接错误:

You may need to grant EXECUTE permissions to the users that will be connecting to the database, otherwise you may get a connection error:

GRANT EXECUTE ON PROCEDURE mysql.store_time_zone TO 'user'@'host';

现在，任何客户端针对RDS实例执行的每个查询都应使用PST，而无需修改任何应用程序逻辑，也不需要更新数据库中以前存储的任何时间戳.

Now every query executed against your RDS instance by any client should use PST without modifying any application logic and without needing to update any previously-stored timestamps in the database.

这篇关于如何为AWS RDS实例设置数据库时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！