MySQL 如何为 AWS RDS 实例设置数据库时区

How to set database timezone for a AWS RDS instance

提问by Rpj

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 实例上使用最新的 MySQL 服务器，并且我们已将其配置为在美国东部数据中心运行。我们假设任何新的 Date() 或 Time.now() 调用都将在数据库服务器运行的时区中存储日期。

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).

有没有办法将我在美国东部运行的 AWS RDS 实例指向 PST 时区，以便日期的任何持久性都将存储在 PST 而不是 EST 中的值。（即，如果在美国东部时间上午 10 点左右存储对象，则 db 列应改为反映东部时间上午 7 点）。

回答by Ryan Weir

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.

在我回答其余部分之前，我现在只想建议您，如果您可以选择将应用程序更改为使用 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.

但是，鉴于您的问题的上下文，我假设这不是一个选项，并且您正在调整旨在在传统服务器环境中使用 MySQL 的系统，您可以在其中更改时区，并且有代码逻辑需要这个时区，并且不能很容易地适应使用 UTC。

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

但是，如果您确实需要将其存储为 PST，请继续阅读。

---

---

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:

默认情况下，mySql 将使用服务器的时区进行时间戳存储是正确的，但是您假设 RDS 实例的时区设置基于它们启动的 AWS 区域是不正确的- 所有 RDS 实例启动时都将它们的时区设置为UTC，并且此配置无法更改：

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

时区当前不可修改。确实有一个rds-describe-db-parameters名为“default_time_zone”的参数值，但它被标记为不可修改。

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:

因此，您唯一的选择是将您的应用程序与数据库实例建立的每个连接上的时区设置为 PST。您可以SET SESSION time_zone = '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 ;
   

2. 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"
   

3. You may need to grant EXECUTEpermissions 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';
   

1. 创建以下存储过程（UTC-8 是 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 ;
   

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

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

3. 您可能需要向EXECUTE将连接到数据库的用户授予权限，否则您可能会收到连接错误：

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

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.

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