How do I change the time zone of my Amazon RDS for Oracle instance?

Short description

The default time zone for an Amazon RDS for Oracle instance is Universal Time Coordinated (UTC). To change the time zone settings of your Amazon RDS for Oracle instance, use one of the following methods:

• Oracle time zone option
• Amazon RDS procedure
• Oracle time zone file autoupgrade feature
• Set the default time zone for Oracle Scheduler jobs

Resolution

Note: If you receive errors when you run the AWS Command Line Interface (AWS CLI) commands, then see Troubleshoot AWS CLI errors. Also, make sure that you're using the most recent AWS CLI version.

Oracle time zone option

Specific applications require you to change the SYSDATE and SYSTIMESTAMP to retrieve the current time of a specific time zone. The Oracle time zone option changes the time zone at the host level. This option requires SYSDATE and SYSTIMESTAMP to make a call to the operating system (OS) host to get an accurate time.

Note: Before you begin, review the considerations for setting the time zone. When you add the time zone option, a brief outage occurs while your DB instance is automatically restarted.

To use the Oracle time zone option, follow these steps:

1.    Run these commands:

Get the SYSDATE:

alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
select sysdate from dual;

Get the SYSTIMESTAMP:

select systimestamp from dual;

Note: Without the time zone option, the previous commands return the time in UTC.

2.    Add the time zone option.

3.    Run the commands from step 1 to confirm that your time zone has changed to your specified time zone.

For more information on values that can be used for the time zone option, see Available time zones.

Amazon RDS procedure

The Amazon RDS procedure rdsadmin.rdsadmin_util.alter_db_time_zone changes the time zone for certain data types, and doesn't change SYSDATE and SYSTIMESTAMP. The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns.

To use the Amazon RDS procedure to change your database time zone, complete these steps:

1.    Change the database time zone:

Note: Replace example-utc-+hh:00 with the +/- hour differential from UTC that you want to change to. For example, if you want to set your time to UTC+5, then replace the example string with +5:00. Also, this value can be changed to a named AWS Region.

EXEC rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz = example_utc_+hh:00);

For a list of time zone values, see Available time zones.

2.    Verify the time zone:

SELECT dbtimezone FROM DUAL;

3.    Restart your DB instance to see your time zone change.

For more information, see Setting the database time zone.

Oracle time zone file autoupgrade feature

The Oracle time zone file autoupgrade feature provides an automated way to upgrade the DST time zone file version in the DB instance.

To use the autoupgrade feature to change your time zone, complete these steps:

1.    Complete the prepare step before you update the time zone file.

2.    Identify the latest available version of the time zone file:

SELECT DBMS_DST.get_latest_timezone_version FROM dual;

3.    Use one of these methods to turn on the autoupgrade feature:

• Sign in to the AWS Management Console and add the TIMEZONE_FILE_AUTOUPGRADE option to the option group. Then, apply the option group to a new or existing DB instance.
• Use the add-option-option-group command in the AWS CLI to add the TIMEZONE_FILE_AUTOUPGRADE option.

Note: You might experience prolonged downtime when you add the TIMEZONE_FILE_AUTOUPGRADE option. For more information, see Downtime during the time zone file update. If you add the option to an existing option group, then any DB instances that use this option group automatically restart.

4.    If the Oracle release contains a newer DST version, then this option updates your time zone file.

5.    Verify the time zone file version:

SQL> SELECT * FROM V$TIMEZONE_FILE;

6.    Check your data after the update of the time zone file.

Note: To view the time zone file (DST) versions that come with each version, see the Amazon RDS for Oracle release notes. Also, make sure that you review strategies for updating your time zone file.

Set the default time zone for Oracle Scheduler jobs

To modify the time zone for DBMS_SCHEDULER, use the dbms_scheduler.set_scheduler_attribute procedure:

1.    View the DEFAULT_TIMEZONE scheduler attribute:

SELECT VALUE FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE 
WHERE ATTRIBUTE_NAME='DEFAULT_TIMEZONE';

2.    Set the DEFAULT_TIMEZONE scheduler attribute:

Note: Replace <example-time-zone-name> with the time zone that you want to use.

BEGIN
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
    attribute = 'default_timezone',
    value = <example-time-zone-name>
  );
END;
/

When you create a job with DBMS_SCHEDULER, the start date is either null or not null. See these conditions:

Start date is null

• If the start_date is set to a Region, then Oracle uses the session time zone. To set the session time zone, use the alter session set time_zone command or set the ORA_SDTZ environment variable on the client side.
• If the session time zone is an absolute offset, then the DEFAULT_TIMEZONE scheduler attribute is used.
• If the DEFAULT_TIMEZONE scheduler attribute is null, then the scheduler uses the systimestamp time zone.

Start date is not null

• Oracle uses the start_date time zone.
• If the time zone isn't specified in the start_date, then Oracle uses the session time zone.
• If the current_timestamp is used as the start_date, then Oracle uses the session time zone.
• If the systimestamp is used as the start_date, then Oracle uses the systimestamp offset.
• If sysdate is used for the start_date, then Oracle uses the session time zone.