複数のタイムゾーンでOracle DBを利用する場合の考慮事項をまとめておく。
- 夏時間についてはややこしくなるのでここでは触れない。
- DBサーバはLinux系を前提として記載している。
#タイムゾーン
知っておくべきタイムゾーンには下記の4種類がある。
- DBサーバのタイムゾーン
- アプリケーションがNET(リモート)接続の場合
- DBサーバOSのタイムゾーン
- DB起動時にTZ環境変数が設定されていればそちらが優先
- Grid Infrastructure利用時は「srvctl setenv database -db SID -t TZ='タイムゾーン'」で環境変数を設定し再起動が必要。リスナには設定不要
- アプリケーションがBEQ(内部)接続の場合
- DBサーバOSのタイムゾーン(BEQ接続ではアプリケーションはDBサーバ上で稼働)
- アプリケーション起動時にTZ環境変数が設定されていればそちらが優先
- アプリケーションがNET(リモート)接続の場合
- 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タイムゾーンの説明補足。