タイムゾーン

知っておくべきタイムゾーンには下記の4種類がある。

DBサーバのタイムゾーン
- アプリケーションがNET(リモート)接続の場合
  - DBサーバOSのタイムゾーン
  - DB起動時にTZ環境変数が設定されていればそちらが優先
  - Grid Infrastructure利用時は「srvctl setenv database -db SID -t TZ='タイムゾーン'」で環境変数を設定し再起動が必要。リスナには設定不要
- アプリケーションがBEQ(内部)接続の場合
  - DBサーバOSのタイムゾーン(BEQ接続ではアプリケーションはDBサーバ上で稼働)
  - アプリケーション起動時にTZ環境変数が設定されていればそちらが優先
DBTIMEZONE
- DB作成時にOSのタイムゾーンがデフォルトで設定。TZ環境変数が設定されていればそちらが優先。
- select DBTIMEZONE from dual; で現在のDBTIMEZONEを確認
- 「DBサーバのタイムゾーン」とは連動も関連もしない。
- この型のカラムがDB上に存在しなければ変更可能。
  - alter system set time_zone='タイムゾーン'で変更。反映にはDB再起動必要。
セッションのタイムゾーン(SESSIONTIMEZONE)
- DBに接続するアプリケーション(DBから見たクライアント)が稼働するOSのタイムゾーン
- アプリケーション起動時にTZ環境変数が設定されていればそちらが優先
- select SESSIONTIMEZONE from dual; で現在のセッションのタイムゾーンを確認
- alter session set time_zone='タイムゾーン'で変更。セッションの切断まで有効。
Schedulerのタイムゾーン
- Oracle Scheduler job(自動メンテナンスタスク等)が動作するタイムゾーン。上記とは独立している。
- DB作成時にOSのタイムゾーンがデフォルトで設定。TZ環境変数が設定されていればそちらが優先。
- 下で詳しく説明

日時データ型

　下記の4種類のデータ型がある。データにタイムゾーン情報を持つのは3のみ。環境により日時が変わるのは4のみ。

No	型	暗黙の文字列変換パラメータ	説明
1	date	NLS_DATE_FORMAT	年月日＋時分秒
2	timestamp	NLS_TIMESTAMP_FORMAT	年月日＋時分秒＋小数秒
3	timestamp with time zone	NLS_TIMESTAMP_TZ_FORMAT	TIMEZONE付きtimestamp。
4	timestamp with local time zone	NLS_TIMESTAMP_FORMAT	登録・表示時に「セッションのタイムゾーン」に自動変換。

4の補足：タイムゾーンの計算にはSESSIONTIMEZONEとDBTIMEZONEの2つを使うと記載があり、このカラムがあるとDBTIMEZONEが変更できないことから、内部的にDBTIMEZONEで値を保持しており、値-DBTIMEZONE+SESSIONTIMEZONEで計算して表示しているのではないかと思われる。

現在日時の取得

　現在日時の取得は5つの関数があり、設定されたタイムゾーンで取得される。

No	関数	現在日時の型
1	sysdate	date
2	systimestamp	timestamp with time zone
3	current_date	date
4	current_timestamp	timestamp with time zone
5	localtimestamp	timestamp with local time zone

1,2は「DBサーバのタイムゾーン」で取得
3,4,5は「セッションのタイムゾーン」で取得

Schedulerのタイムゾーン

Schedulerのタイムゾーンの確認と変更

-- デフォルトタイムゾーンの確認(OSタイムゾーンが作成時に設定される)
select dbms_scheduler.stime from dual;
-- 現在の自動メンテナンスタスクの稼働タイムゾーンの確認(通常上記タイムゾーンと同じだが、異なる指定も可能)
select window_name,window_next_time from dba_autotask_window_clients order by 2;
-- デフォルトタイムゾーンの設定(US/Pacificに変更場合)
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Pacific');
-- タイムゾーンの設定をした時点で自動メンテナンスタスクのタイムゾーンも（稼働中のタスクを除き）変わる
-- 下記の様にウインドウ毎にSTART_DATEを指定することで、デフォルトと異なるタイムゾーンを指定可能。
-- 混乱を避けるため、できるだけデフォルトタイムゾーンを用いることが望ましい。
-- START_DATEを指定した場合、デフォルトタイムゾーンを再設定してもウインドウのタイムゾーンは変わらない。
EXEC  DBMS_SCHEDULER.SET_ATTRIBUTE ('MONDAY_WINDOW','START_DATE','18-03-19 22:00:00 UTC');

JOBの履歴確認

-- 自動メンテナンスタスクの履歴
select CLIENT_NAME,WINDOW_NAME,WINDOW_START_TIME,WINDOW_END_TIME
 from DBA_AUTOTASK_CLIENT_HISTORY order by 3;
-- JOB履歴
select job_name,last_start_date,next_run_date from all_scheduler_jobs order by 2;

WINDOW_START_TIME,WINDOWS_END_TIME,start_date,end_dateは「TIMESTAMP WITH TIME ZONE」型。
- 日時は「DBサーバのタイムゾーン」で記載され、「Schedulerのタイムゾーン」とは異なる場合がある。
- DBサーバのタイムゾーン変更時、前のタイムゾーンで記録される場合がある。
- 実行中タスクのWINDOWS_END_TIMEはOSのタイムゾーンで記録。

参考

日付データ演算の達人技を伝授する【第3話】
Changing Timezone Settings on Oracle Database Servers

変更履歴

2018/3/15 公開
2018/3/19 DB作成時のTZ環境変数の説明を追加。Schedulerタイムゾーンの説明補足。

Oracle DBの日時データ型とタイムゾーン

タイムゾーン

日時データ型

現在日時の取得

Schedulerのタイムゾーン

参考

変更履歴