About Timezone in RDS for Oracle - いつまでたってもIT新人

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00

SQL> SELECT VALUE FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE WHERE ATTRIBUTE_NAME = 'DEFAULT_TIMEZONE';

VALUE
--------------------------------------------------------------------------------
Etc/UTC

After setting Timezon Option(Asia/Tokyo)

Oracle time zone

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00


SQL> SELECT SYSTIMESTAMP FROM dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-JUN-25 04.13.00.181364 AM +09:00

SQL> SELECT VALUE FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE WHERE ATTRIBUTE_NAME = 'DEFAULT_TIMEZONE';

VALUE
--------------------------------------------------------------------------------
Etc/UTC

For scheduler we should check DBA_SCHEDULER_GLOBAL_ATTRIBUTE and execute following SQL.
Setting the time zone for Oracle Scheduler jobs

EXEC DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone', 'Asia/Tokyo');

SQL> SELECT VALUE FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE WHERE ATTRIBUTE_NAME = 'DEFAULT_TIMEZONE';

VALUE
--------------------------------------------------------------------------------
Asia/Tokyo

DBTIMEZONE is the default time zone for the TIMESTAMP WITH LOCAL TIME ZONE data type, and it can be changed using the rdsadmin_util.alter_db_time_zone command.

Setting the database time zone

EXEC rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => 'Asia/Tokyo');
SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00

--reboot

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIMEZONE
----------
Asia/Tokyo

Check Item	SQL Example	Description
Database Timezone	SELECT DBTIMEZONE FROM DUAL;	The baseline for the database’s TIMESTAMP WITH LOCAL TIME ZONE. Separate from RDS TimeZone option.
Host-level Timezone	`SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS TZR TZD') FROM DUAL;`	Reflects the setting of the RDS TimeZone option.
Session Timezone	`SELECT SESSIONTIMEZONE FROM DUAL;`	Checks the current session’s timezone.
Scheduler Timezone	SELECT VALUE FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE WHERE ATTRIBUTE_NAME = ‘DEFAULT_TIMEZONE’;	Shows the Oracle Scheduler’s default timezone, which may differ from the host or session timezone.