- Newest
- Most votes
- Most comments
Please run the below query to check of the timezone option is being picked up from the parameter group. Check for source column and its value for each database.
select * FROM pg_settings WHERE name = 'TimeZone';
If source = configuration file, which is showing that the timezone value is being reflected from the parameter group itself.
If source = database, which is showing that the timezone value is set at the database level and is overriding the setting from the parameter group.
If source = user , then its set at user level.
Note that, the database level or role/user level or session level settings will override the timezone settings specified in the parameter group. The timezone change made via the parameter group should reflect in all databases unless the setting is explicitly made at the session/database/user level.
You need to make sure that there are no configurations at Database level or role/user level or session level that overrides the timezone settings specified in the parameter group. The order of settings is as follows:
- Session level configurations.
- Database level configurations.
- Role/User level configurations.
- Use the default settings (parameter group value).
For your reference, below are the commands for setting the timezone value at the user and session levels:
-
Session level postgres=> SET TimeZone="America/Los_Angeles";
-
User level postgres=> ALTER USER <user name> SET TimeZone="America/Los_Angeles";
-
Database level postgres=> ALTER DATABASE <database name> SET TimeZone="America/Los_Angeles";
I hope this helps. If this solution works for you, please mark it as the answer.
Hi jprice,
Aurora has cluster-level and instance-level parameter groups. Ensure that the timezone setting is correctly applied at the cluster level and not being overridden by an instance-level parameter group.
Ensure that the cluster isn't using the default parameter group. Default parameter groups are not modifiable.
I hope this helps! If this solution works for you, please accept the answer. Otherwise, do leave a comment, and I'll try to assist you with other ideas.
Good thoughts.
- Yes I have defined custom parameter groups and assigned them at the cluster and instance level.
- The cluster level group contains value America/Los_Angeles for the timezone parameter.
- The console UI will not allow adding the timezone parameter to the instance level group. It is not present there.
Yet the setting does not take effect.
It's possible that the SHOW timezone; command reflects session-level settings rather than cluster-level settings.
Relevant content
- asked a year ago
asked 2 years ago
AWS OFFICIALUpdated a year ago- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated a year ago
This was all good advice, nicely presented. It fit the understanding I had of how timezone configuration is supposed to work. But it was not 'working'. It turns out there were two issues.
Once I switched to psql for testing, everything became clear. Cluster, Database, User and Connection parameter settings worked exactly as described here.
So my solution is outside of Aurora RDS. It's to insure that Java code either has the right TZ defined when the JVM starts or to cause connection pools to set timezone as desired before lending a connection.
Thanks for the response.