　Oracle Database 上で時刻を示す関数と関連する初期化パラメーターを検証しました。本記事には含みませんが、Job Scheduler は別のタイムゾーン情報を持っています。

Red Hat Enterprise Linux のタイムゾーン

　検証で利用する Red Hat Enterprise Linux 8.8 ではタイムゾーンとして Asia/Tokyo (+09:00) を指定しています。確認は timedatectl コマンドを実行します。

OS Timezone

$ timedatectl
               Local time: Thu 2024-08-08 15:17:09 JST
           Universal time: Thu 2024-08-08 06:17:09 UTC
                 RTC time: Thu 2024-08-08 06:22:09
                Time zone: Asia/Tokyo (JST, +0900)
System clock synchronized: no
              NTP service: inactive
          RTC in local TZ: no

Oracle Database のタイムゾーン設定

　データベースのタイムゾーンは DBTIMEZONE 関数で確認できます。デフォルト値は '+00:00' です。変更する場合には ALTER DATABASE SET TIME_ZONE 文を実行します。インスタンス再起動後、有効になります。

DBTIMEZONE の確認と変更

SQL> SELECT DBTIMEZONE;

DBTIME
------
+00:00

SQL> ALTER DATABASE SET TIME_ZONE='Turkey';

データベースが変更されました。

SQL> SHUTDOWN IMMEDIATE
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。

SQL> STARTUP
ORACLEインスタンスが起動しました。

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             939524096 bytes
Database Buffers          654311424 bytes
Redo Buffers                4530176 bytes
データベースがマウントされました。
データベースがオープンされました。

SQL> SELECT DBTIMEZONE;

DBTIME
------
Turkey

アラートログの時刻出力は DBTIMEZONE とは関係なく OS 設定（timedatectl コマンド設定または環境変数 TZ）が利用されます。

アラートログの時刻出力

2024-08-08T15:51:41.202458+09:00
FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
Completed: Pluggable database FREEPDB1 opened read write
Completed: ALTER DATABASE OPEN

タイムゾーンと SQL 関数

　Oracle Database には日付や時刻を取得する複数の SQL 関数が提供されています。デフォルト状態では主に SESSIONTIMEZONE 属性または DB サーバーの OS タイムゾーンを使って日時を取得します。

関数名	データ型	タイムゾーン設定	備考
SYSDATE	DATE	OS時刻
SYSTIMESTAMP	TIMESTAMP WITH TIME ZONE	OS 時刻	タイムゾーン情報付
CURRENT_DATE	DATE	SESSIONTIMEZONE
CURRENT_TIMESTAMP	TIMESTAMP WITH TIME ZONE	SESSIONTIMEZONE	タイムゾーン情報付
LOCALTIMESTAMP	TIMESTAMP WITH LOCAL TIME ZONE	SESSIONTIMEZONE

　セッションのタイムゾーン（SESSIONTIMEZONE）は、クライアントの OS 設定または環境変数 TZ の値が使用されます。下記の例は、環境変数 TZ にグアムを指定し、SESSIONTIMEZONE 関数で確認しています。

SESSIONTIMEZONE の変化

$ export TZ='Pacific/Guam'
$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on 木 8月 8 14:22:53 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
に接続されました。

SQL> SELECT SESSIONTIMEZONE;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+10:00

SQL> SELECT CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
24-08-08 16:08:51.140911 +10:00

DBTIMEZONE の利用

　SYSDATE 関数、SYSTIMESTAMP 関数で DBTIMEZONE を利用するためには、初期化パラメーター time_at_dbtimezone （デフォルト off）を USER_SQL に変更します。この初期化パラメーターはインスタンス再起動後に有効化されます。

初期化パラメーター time_at_dbtimezone の変更

SQL> SHOW PARAMETER time_at_dbtimezone
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
time_at_dbtimezone                   string      USER_SQL

SQL> SELECT DBTIMEZONE;

DBTIME
------
Turkey

SQL> SELECT SYSTIMESTAMP;

SYSTIMESTAMP
---------------------------------------------------------------------------
24-08-08 09:20:21.628277 TURKEY

　初期化パラメーター time_at_dbtimezone を USER_SQL に変更した場合、SQL 関数のタイムゾーンは以下のようになります。

関数名	データ型	タイムゾーン設定	備考
SYSDATE	DATE	DBTIMEZONE
SYSTIMESTAMP	TIMESTAMP WITH TIME ZONE	DBTIMEZONE	タイムゾーン情報付
CURRENT_DATE	DATE	SESSIONTIMEZONE
CURRENT_TIMESTAMP	TIMESTAMP WITH TIME ZONE	SESSIONTIMEZONE	タイムゾーン情報付
LOCALTIMESTAMP	TIMESTAMP WITH LOCAL TIME ZONE	SESSIONTIMEZONE

よく似た初期化パラメーターとして sysdate_at_timezone がありますが、こちらは Autonomous Database のみで利用される初期化パラメーターのようです。

PDB 単位のタイムゾーン

　Oracle Database 23ai から、PDB 単位で DBTIMEZONE を指定できるようになりました。やり方は上記検証の CDB と同じです。ただし PDB の場合は、初期化パラメーター time_at_dbtimezone の設定値に DATABASE を指定できます。これはユーザー SQL 以外の時刻操作にも DBTIMEZONE が利用される設定です。検証中にこの初期化パラメーターの設定値を DATABASE に設定する操作が成功する場合と失敗する場合がありましたが原因は不明です。

PDB の DBTIMEZONE 変更

SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT

SQL> SELECT DBTIMEZONE;

DBTIME
------
Turkey

SQL> ALTER SESSION SET CONTAINER=FREEPDB1;

セッションが変更されました。

SQL> ALTER DATABASE SET TIME_ZONE='Pacific/Guam';

データベースが変更されました。

SQL> ALTER SYSTEM SET time_at_dbtimezone='USER_SQL' SCOPE=SPFILE;

システムが変更されました。

SQL> ALTER PLUGGABLE DATABASE FREEPDB1 CLOSE IMMEDIATE;

プラガブル・データベースが変更されました。

SQL> ALTER PLUGGABLE DATABASE FREEPDB1 OPEN;

プラガブル・データベースが変更されました。

SQL> SELECT DBTIMEZONE;

DBTIMEZONE
------------
Pacific/Guam

SQL> SELECT SYSTIMESTAMP;

SYSTIMESTAMP
---------------------------------------------------------------------------
24-08-08 16:22:16.513437 PACIFIC/GUAM

Author: Noriyoshi Shinoda / Date: August 8, 2024