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

複数のタイムゾーンでOracle DBを利用する場合の考慮事項をまとめておく。

* 夏時間についてはややこしくなるのでここでは触れない。
* DBサーバはLinux系を前提として記載している。

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

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

```SQL
-- デフォルトタイムゾーンの確認(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の履歴確認

```SQL
-- 自動メンテナンスタスクの履歴
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話】](http://www.atmarkit.co.jp/ait/articles/0505/27/news116.html)
[Changing Timezone Settings on Oracle Database Servers](https://minersoracleblog.wordpress.com/2014/09/19/changing-timezone-settings-on-oracle-database-servers/)

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

-