4 日時データ型とタイム・ゾーン・サポート
この章の内容は次のとおりです。
4.1 日時データ型、期間データ型およびタイム・ゾーン・サポートの概要
企業は様々なタイム・ゾーンにまたがってトランザクションを実行しています。Oracle Databaseの日時データ型、期間データ型およびタイム・ゾーン・サポートにより、イベントとトランザクションの時間に関して一貫性のある情報を格納できます。
ノート:
この章では、Oracle Databaseの日時データ型と期間データ型について説明します。特に明記している場合を除き、ANSIデータ型や他の種類のデータ型については説明しません。
4.2 日時および期間データ型
日時データ型は、DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONEおよびTIMESTAMP WITH LOCAL TIME ZONEです。日時データ型の値をDatetimesと呼ぶこともあります。
期間データ型は、INTERVAL YEAR TO MONTHおよびINTERVAL DAY TO SECONDです。期間データ型の値は、「期間」とも呼ばれます。
DatatimesとIntervalsは、どちらも複数のフィールドで構成されます。これらのフィールドの値は、データ型の値によって決まります。Oracle Databaseのすべての日時および期間データ型に適用されるフィールドは、次のとおりです。
-
YEAR -
MONTH -
DAY -
HOUR -
MINUTE -
SECOND
TIMESTAMP WITH TIME ZONEには、次のフィールドも含まれます。
-
TIMEZONE_HOUR -
TIMEZONE_MINUTE -
TIMEZONE_REGION -
TIMEZONE_ABBR
TIMESTAMP WITH LOCAL TIME ZONEはタイム・ゾーン情報の内部格納には使用されませんが、TZH:TZMまたはTZR TZD書式要素が指定されている場合は、SQL出力にローカルのタイム・ゾーン情報を表示できます。
次の項では、日時データ型と期間データ型の詳細を説明します。
-
関連項目:
-
日時フィールドと時間隔フィールドの有効な値については、『Oracle Database SQL言語リファレンス』を参照してください。
-
書式要素の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
-
4.2.1 日時データ型
この項には次のトピックが含まれます:
4.2.1.1 DATEデータ型
DATEデータ型は、日付および時刻の情報を格納するために使用します。日付および時刻の情報は、文字データ型および数値データ型で表現できますが、DATEデータ型には特別に対応付けられているプロパティがあります。DATE値ごとに、次の世紀、年、月、日、時間、分および秒の情報が格納されます。
日付値は次の方法で指定できます。
-
日付値をリテラルとして指定
-
TO_DATE関数を使用して文字値または数値を日付値に変換
日付は、ANSI日付リテラルまたはOracle Databaseの日付値として指定できます。
ANSI日付リテラルには時間部分がなく、次の書式で正確に指定する必要があります。
DATE 'YYYY-MM-DD'
次にANSI日付リテラルの例を示します。
DATE '1998-12-25'
または、次の例に示すようにOracle Databaseの日付値を指定することもできます。
TO_DATE('1998-DEC-25 17:30','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN')Oracle Databaseの日付値のデフォルト書式は、NLS_DATE_FORMATおよびNLS_DATE_LANGUAGE初期化パラメータから導出されます。この例の日付書式には、日を表す2桁の数値、月名の略称、年の4桁および24時間指定が含まれています。NLS_DATE_LANGUAGEの指定が含まれていますが、これはどのロケールでも'DEC'はMONに有効な値ではないためです。
デフォルト日付書式の文字値が日付式に使用されている場合は、自動的に日付値に変換されます。
時刻要素を含めずに日付値を指定した場合、デフォルトの時刻として午前0時に設定されます。日付値を指定する場合に日付を指定しないと、デフォルト日付である現在の月の最初の日が採用されます。
Oracle DatabaseのDATE列には、常に日付と時刻の両方のフィールドが含まれます。時刻部分を指定せずに問合せに日付書式を使用する場合は、DATE列の時刻フィールドを午前0時に設定する必要があります。TRUNC(date)SQL関数を使用すると、時刻フィールドを確実に午前0時に設定できます。また、問合せを使用して等価性または非等価性(=または!=)のかわりに大小の関係(<、<=、>=または>)のテストもできます。そうでない場合、Oracle Databaseは、正しい結果を戻さない場合があります。
関連項目:
-
DATEデータ型の詳細は、
『Oracle Database SQL言語リファレンス』を参照してください。 -
リテラル、
MMなどの書式要素およびTO_DATE関数の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
4.2.1.2 TIMESTAMPデータ型
TIMESTAMPデータ型は、DATEデータ型の拡張機能です。年、月、日、時間、分および秒の値が格納されます。DATEデータ型では格納されない小数秒も格納されます。
TIMESTAMPデータ型は次のように指定します。
TIMESTAMP [(fractional_seconds_precision)]
fractional_seconds_precisionはオプションで、SECOND日時フィールドの小数部の桁数を指定します。範囲0から9の数値を指定できます。デフォルトは6です。
たとえば、'26-JUN-02 09:39:16.78'は16.78秒を示します。78は2桁のため、小数秒の精度は2です。
TIMESTAMPリテラルは、次のような書式で指定できます。
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'
この例に示す書式を使用して、次のようにTIMESTAMPをリテラルとして指定します。
TIMESTAMP '1997-01-31 09:26:50.12'
NLS_TIMESTAMP_FORMAT初期化パラメータの値により、文字列からTIMESTAMPデータ型への変換時のタイムスタンプ書式が決定されます。NLS_DATE_LANGUAGEにより、MONなど、文字データの使用言語が決定されます。
関連項目:
-
TIMESTAMPデータ型の詳細は、
『Oracle Database SQL言語リファレンス』を参照してください。
4.2.1.3 TIMESTAMP WITH TIME ZONEデータ型
TIMESTAMP WITH TIME ZONEはTIMESTAMPのバリアントで、値にはタイム・ゾーン・リージョン名またはタイム・ゾーン・オフセットが含まれます。タイム・ゾーン・オフセットは、ローカル時間とUTC(協定世界時、以前のグリニッジ標準時)との時差(時間および分単位)です。TIMESTAMP WITH TIME ZONEデータ型は、次のように指定します。
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
fractional_seconds_precisionはオプションで、SECOND日時フィールドの小数部の桁数を指定します。
TIMESTAMP WITH TIME ZONEは、次のようにリテラルとして指定できます。
TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
2つのTIMESTAMP WITH TIME ZONE値がUTCで同じ時刻を表す場合は、データに格納されたTIME ZONEオフセットにかかわらず、同一であるとみなされます。たとえば、次の2つの式の値は同じです。
TIMESTAMP '1999-01-15 8:00:00 -8:00' TIMESTAMP '1999-01-15 11:00:00 -5:00'
UTCオフセットはTZR(タイム・ゾーン・リージョン)書式要素で置き換えることができます。次の式は、タイム・ゾーン・リージョンにAmerica/Los_Angelesを指定しています。
TIMESTAMP '1999-01-15 8:00:00 America/Los_Angeles'
標準時間から夏時間への切替時に境界を明確にするには、TZR書式要素および対応するTZD書式要素の両方を使用します。TZD書式要素は、夏時間情報を含むタイム・ゾーン・リージョンの略称です。たとえば、米国太平洋標準時間の場合はPST、米国太平洋夏時間の場合はPDTです。次のように指定すると、確実に夏時間の値が戻されます。
TIMESTAMP '1999-10-29 01:30:00 America/Los_Angeles PDT'
ERROR_ON_OVERLAP_TIMEセッション・パラメータをTRUEに設定していて、TZD書式要素を追加しない場合、日時の値が不明確な場合はエラーが戻されます。ERROR_ON_OVERLAP_TIMEがFALSE(デフォルト値)に設定されている場合、あいまいな日時は標準時間として解析されます。
TIMESTAMP WITH TIME ZONEデータ型のデフォルトの日付書式は、NLS_TIMESTAMP_TZ_FORMAT初期化パラメータの値により決定されます。
関連項目:
-
TIMESTAMP WITH
TIMEZONEデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
小数秒の精度の詳細は、「TIMESTAMPデータ型」を参照してください
-
書式要素の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
-
ERROR_ON_OVERLAP_TIMEセッション・パラメータの設定の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
4.2.1.4 TIMESTAMP WITH LOCAL TIME ZONEデータ型
TIMESTAMP WITH LOCAL TIME ZONEはTIMESTAMPのもう1つのバリアントです。TIMESTAMP WITH TIME ZONEとは異なり、データベースに格納されるデータはデータベース・タイムゾーンに対して正規化され、タイムゾーン・オフセッは列データの一部として格納されません。ユーザーがデータを検索すると、そのユーザーのローカル・セッションのタイム・ゾーンで戻されます。タイム・ゾーン・オフセットは、ローカル時間とUTC(協定世界時、以前のグリニッジ標準時)との時差(時間および分単位)です。
TIMESTAMP WITH LOCAL TIME ZONEデータ型は次のように指定します。
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
fractional_seconds_precisionはオプションで、SECOND日時フィールドの小数部の桁数を指定します。
TIMESTAMP WITH LOCAL TIME ZONEのリテラルはありませんが、TIMESTAMP WITH LOCAL TIME ZONE列にTIMESTAMPリテラルとTIMESTAMP WITH TIME ZONEリテラルを挿入できます。
TIMESTAMP WITH LOCAL TIME ZONEデータ型のデフォルトの日付書式は、NLS_TIMESTAMP_FORMAT初期化パラメータの値により決定されます。
関連項目:
-
TIMESTAMP WITH LOCAL TIME ZONEデータ型の詳細は、
『Oracle Database SQL言語リファレンス』を参照してください。 -
小数秒の精度の詳細は、「TIMESTAMPデータ型」を参照してください
4.2.1.5 日時データ型への値の挿入
-
適切なNLS書式値に基づく書式を使用した文字列の挿入
-
リテラルの挿入
-
暗黙的な変換の実行対象となるリテラルの挿入
-
TO_TIMESTAMP、TO_TIMESTAMP_TZまたはTO_DATESQL関数の使用
次の例に、日時データ型にデータを挿入する方法を示します。
関連項目:
TO_TIMESTAMPまたはTO_TIMESTAMP_TZ SQL関数の詳細は、「日時SQL関数」を参照してください
例4-1 DATE列へのデータの挿入
日付書式を設定します。
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
c_id列とc_dt列を含むtable_dt表を作成します。c_id列はNUMBERデータ型で、データの入力方法を識別できます。c_dt列はDATEデータ型です。
SQL> CREATE TABLE table_dt (c_id NUMBER, c_dt DATE);
日付を文字列として挿入します。
SQL> INSERT INTO table_dt VALUES(1, '01-JAN-2003');
同じ日付をDATEリテラルとして挿入します。
SQL> INSERT INTO table_dt VALUES(2, DATE '2003-01-01');
同じ日付をTIMESTAMPリテラルとして挿入します。タイム・ゾーン情報は削除されます。
SQL> INSERT INTO table_dt VALUES(3, TIMESTAMP '2003-01-01 00:00:00 America/Los_Angeles');
TO_DATE関数を使用して同じ日付を挿入します。
SQL> INSERT INTO table_dt VALUES(4, TO_DATE('01-JAN-2003', 'DD-MON-YYYY'));
データを表示します。
SQL> SELECT * FROM table_dt; C_ID C_DT ---------- -------------------- 1 01-JAN-2003 00:00:00 2 01-JAN-2003 00:00:00 3 01-JAN-2003 00:00:00 4 01-JAN-2003 00:00:00
例4-2 TIMESTAMP列へのデータの挿入
タイムスタンプ書式を設定します。
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';
c_id列とc_ts列を含むtable_ts表を作成します。c_id列はNUMBERデータ型で、データの入力方法を識別できます。c_ts列はTIMESTAMPデータ型です。
SQL> CREATE TABLE table_ts(c_id NUMBER, c_ts TIMESTAMP);
日付と時刻を文字列として挿入します。
SQL> INSERT INTO table_ts VALUES(1, '01-JAN-2003 2:00:00');
同じ日付および時刻をTIMESTAMPリテラルとして挿入します。
SQL> INSERT INTO table_ts VALUES(2, TIMESTAMP '2003-01-01 2:00:00');
同じ日付および時刻をTIMESTAMP WITH TIME ZONEリテラルとして挿入します。値はTIMESTAMP値に変換されます。つまり、タイム・ゾーン情報が削除されます。
SQL> INSERT INTO table_ts VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');
データを表示します。
SQL> SELECT * FROM table_ts; C_ID C_TS ---------- ----------------------------- 1 01-JAN-03 02:00:00.000000 AM 2 01-JAN-03 02:00:00.000000 AM 3 01-JAN-03 02:00:00.000000 AM
この3つの方法では、いずれも同じ値が格納されることに注意してください。
例4-3 TIMESTAMP WITH TIME ZONEデータ型へのデータの挿入
タイムスタンプ書式を設定します。
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';
タイム・ゾーンを'-07:00'に設定します。
SQL> ALTER SESSION SET TIME_ZONE='-7:00';
c_id列とc_tstz列を含むtable_tstz表を作成します。c_id列はNUMBERデータ型で、データの入力方法を識別できます。c_tstz列はTIMESTAMP WITH TIME ZONEデータ型です。
SQL> CREATE TABLE table_tstz (c_id NUMBER, c_tstz TIMESTAMP WITH TIME ZONE);
日付と時刻を文字列として挿入します。
SQL> INSERT INTO table_tstz VALUES(1, '01-JAN-2003 2:00:00 AM -07:00');
同じ日付および時刻をTIMESTAMPリテラルとして挿入します。値はTIMESTAMP WITH TIME ZONEリテラルに変換されます。つまり、TIMESTAMP値にセッションのタイム・ゾーンが追加されます。
SQL> INSERT INTO table_tstz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');
同じ日付および時刻をTIMESTAMP WITH TIME ZONEリテラルとして挿入します。
SQL> INSERT INTO table_tstz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -8:00');
データを表示します。
SQL> SELECT * FROM table_tstz; C_ID C_TSTZ ---------- ------------------------------------ 1 01-JAN-03 02:00.00:000000 AM -07:00 2 01-JAN-03 02:00:00.000000 AM -07:00 3 01-JAN-03 02:00:00.000000 AM -08:00
方法3の場合はタイム・ゾーンが異なることに注意してください。これは、TIMESTAMP WITH TIME ZONEリテラルの一部としてタイム・ゾーン情報が指定されているためです。
例4-4 TIMESTAMP WITH LOCAL TIME ZONEデータ型へのデータの挿入
タイム・ゾーンがUTC-7である米国コロラド州デンバーで入力されるデータを考えます。
SQL> ALTER SESSION SET TIME_ZONE='-07:00';
c_id列とc_tsltz列を含むtable_tsltz表を作成します。c_id列はNUMBERデータ型で、データの入力方法を識別できます。c_tsltz列はTIMESTAMP WITH LOCAL TIME ZONEデータ型です。
SQL> CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);
日付と時刻を文字列として挿入します。
SQL> INSERT INTO table_tsltz VALUES(1, '01-JAN-2003 2:00:00');
同じデータをTIMESTAMP WITH LOCAL TIME ZONEリテラルとして挿入します。
SQL> INSERT INTO table_tsltz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');
同じデータをTIMESTAMP WITH TIME ZONEリテラルとして挿入します。データはTIMESTAMP WITH LOCAL TIME ZONE値に変換されます。つまり、入力したタイム・ゾーン(-08:00)はセッションのタイム・ゾーン値(-07:00)に変換されます。
SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');
データを表示します。
SQL> SELECT * FROM table_tsltz; C_ID C_TSLTZ ---------- ------------------------------------ 1 01-JAN-03 02.00.00.000000 AM 2 01-JAN-03 02.00.00.000000 AM 3 01-JAN-03 03.00.00.000000 AM
時間が2から3に変更され、UTC-8として入力した情報がローカル・タイム・ゾーンに変更されていることに注意してください。
4.2.1.6 TIMESTAMPデータ型の選択
TIMESTAMPデータ型は、イベントの時間を記録する日時値(タイム・ゾーンなし)が必要な場合に使用します。たとえば、組立ラインの作業場にいる従業員がタイムカードに出社と退社を記録した時刻情報を格納できます。これは常にローカル時刻であるため、タイムゾーンの部分を保存する必要がありません。TIMESTAMPデータ型では、格納に7バイトまたは11バイトが使用されます。
TIMESTAMP WITH TIME ZONEデータ型を使用するのは、日時値が指定されたローカル時刻を表す場合、またはタイム・ゾーン情報を値で記録する必要がある場合です。ローカル時間で設定された予定を考えます。夏時間ルールなどのタイム・ゾーン定義が変更された場合、指定ローカル日時を調整する必要があります。調整しないと、値が不正確になります。このデータ型は、そのような影響をほとんど受けません。
TIMESTAMP WITH TIME ZONEデータ型では、タイム・ゾーン情報が格納されるため、データの格納にTIMESTAMPおよびTIMESTAMP WITH LOCAL TIME ZONEデータ型よりも2バイト多い13バイトが必要です。タイム・ゾーンは、タイム・ゾーン・リージョン名またはUTCからのオフセットとして格納されます。データはそのまま表示または計算に使用でき、追加の処理は必要ありません。TIMESTAMP WITH TIME ZONE列は主キーとして使用できません。TIMESTAMP WITH TIME ZONE列で索引が作成されると、ファンクション索引となります。
TIMESTAMP WITH LOCAL TIME ZONEデータ型は、タイム・ゾーン情報のないタイムスタンプの格納に使用されます。データは、クライアントとの間でやり取りされるたびにデータベースのタイム・ゾーンに対して正規化されます。格納には11バイトが必要です。
TIMESTAMP WITH LOCAL TIME ZONEデータ型は、元のタイム・ゾーンは重要ではないが、イベントの相対時刻が重要で、夏時間を正確に調製する必要がない場合に適しています。データベースのタイム・ゾーン間でこのデータ型が実行するタイム・ゾーン変換は、夏時間調整により非対称です。このデータ型はタイム・ゾーン情報を維持しないため、夏時間と標準時間のいずれであるか、秋の調整で近接する値を区別しません。個別のインスタンス間の混合により、ユーザーの通常の作業時間内に調整が発生する場合は特に、アプリケーションが予期しない動作をします。
夏時間への移行日が頻繁に更新され期間も変則的なブラジルやイスラエルなどは、特にタイム・ゾーン調整の問題の影響を受けやすいリージョンです。これらの地域からの時間情報がキーとなるアプリケーションの場合は、別の日時データ型の利用を考慮できます。
4.2.2 期間データ型
期間データ型は、期間の格納に使用されます。主な用途は分析関数です。たとえば、株価の移動平均の計算に使用できます。特定のパーセンタイルに対応する値を判別するには、期間データ型を使用する必要があります。また、期間データ型は履歴表の更新にも使用できます。
この項には次のトピックが含まれます:
-
関連項目:
移動平均や逆パーセンタイルなどの分析関数の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください
4.2.2.1 INTERVAL YEAR TO MONTHデータ型
INTERVAL YEAR TO MONTHは、YEARおよびMONTH日時フィールドを使用して期間を格納します。INTERVAL YEAR TO MONTHは次のように指定します。
INTERVAL YEAR [(year_precision)] TO MONTH
year_precisionは、YEAR日時フィールドの桁数です。0から9までの値を使用できます。year_precisionのデフォルト値は2です。
時間隔値は、リテラルとして指定できます。時間隔リテラルを指定するには様々な方法があります。次に、123年と2か月という時間隔を指定する例を示します。年の精度は3です。
INTERVAL '123-2' YEAR(3) TO MONTH
関連項目:
INTERVAL YEAR TO MONTHデータ型を使用して時間隔リテラルを指定する方法の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
4.2.2.2 INTERVAL DAY TO SECONDデータ型
INTERVAL DAY TO SECONDは、日付、時、分および秒で期間を格納します。このデータ型は次のように指定します。
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
day_precisionは、DAY日時フィールドの桁数です。0から9までの値を使用できます。デフォルトは2です。
fractional_seconds_precisionは、SECOND日時フィールドの小数部の桁数です。0から9までの値を使用できます。デフォルトは6です。
次に、4日、5時間、12分、10秒および0.222秒の時間隔を指定する一例を示します。小数秒の精度は3です。
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
時間隔値は、リテラルとして指定できます。時間隔リテラルを指定するには様々な方法があります。
関連項目:
INTERVAL DAY TO SECONDデータ型を使用して時間隔リテラルを指定する方法の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
4.3 日時および時間隔の演算と比較
この項には次のトピックが含まれます:
4.3.1 日時および時間隔の演算
日付(DATE)、タイムスタンプ(TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE)および時間隔(INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH)の各データに対する演算操作を実行できます。タイムスタンプ・データ型を期間データ型と併用すると、演算操作で最大精度を維持できます。
日付値とタイムスタンプ値に対する演算操作には、NUMBER定数を使用できます。タイムスタンプ値はOracleで内部的に日付値に変換されてから、NUMBER定数を使用して演算操作が実行されます。これは、日付値とタイムスタンプ値の両方を含む操作中には、小数秒に関する情報が失われることを意味します。日時式および時間隔式では、NUMBER定数が日数として解析されます。
各DATE値には、時間要素が含まれます。多くの日付操作の結果には、小数部があります。この小数部は、日を単位として表されています。たとえば、1.5日は36時間です。DATEデータに対する一般的な操作では、これらの小数部もOracle Databaseの組込みSQL関数により戻されます。たとえば、組込みMONTHS_BETWEEN SQL関数は、2つの日付間の月数を戻します。結果の小数部は、月(1か月は31日)を単位として表されます。
Oracleデータベースでは、すべてのタイムスタンプ演算がUTC時間で実行されます。TIMESTAMP WITH LOCAL TIME ZONEデータの場合は、日時値がデータベースのタイム・ゾーンからUTCに変換され、演算の実行後にデータベースのタイム・ゾーンに逆変換されます。TIMESTAMP WITH TIME ZONEデータの場合、日時値には常にUTCが使用されるため、変換は必要ありません。
関連項目:
-
DATEへの暗黙的な変換を実行する関数については、「日時SQL関数」を参照してください
4.3.2 日時の比較
日付値とタイムスタンプ値を比較する場合、Oracleデータベースではデータが高精度のデータ型に変換されてから比較されます。たとえば、TIMESTAMP WITH TIME ZONEデータ型のデータをTIMESTAMPデータ型のデータと比較する場合は、セッションのタイム・ゾーンを使用してTIMESTAMPデータがTIMESTAMP WITH TIME ZONEに変換されます。
日付およびタイムスタンプ・データの変換の優先順位は次のとおりです。
-
DATE -
TIMESTAMP -
TIMESTAMPWITHLOCALTIMEZONE -
TIMESTAMPWITHTIMEZONE
どのデータ型のペアの場合も、優先順位リストで小さい番号を持つデータ型が、大きい番号を持つデータ型に変換されます。
4.4 日時SQL関数
日時関数は、日付(DATE)、タイムスタンプ(TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE)および時間隔(INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH)の値を処理します。
日時関数の一部は、Oracle DatabaseのDATEデータ型用に設計されています。引数としてタイムスタンプ値を指定すると、Oracle Databaseでは内部的に入力の型がDATE値に変換されます。ROUNDおよびTRUNC関数の場合、内部変換は実行されません。
次の表に、Oracle DatabaseのDATEデータ型用に設計された日時関数を示します。
表4-1 DATEデータ型用に設計された日時関数
| 関数 | 説明 |
|---|---|
|
ノート: この関数への入力として使用できるタイム・ゾーンの数は限定されています。 |
|
次の表に、その他の日時関数を示します。
表4-2 その他の日時関数
次の表に夏時間(DST)アップグレード・プロセスに関連する関数を示します。
表4-3 タイム・ゾーン変換関数
| タイム・ゾーン関数 | 説明 |
|---|---|
関連項目:
-
Oracle Database日時関数の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
-
Oracle Databaseの夏時間機能の詳細は、「夏時間のサポート」を参照してください
-
夏時間に関連するセッション・パラメータ
ERROR_ON_OVERLAP_TIMEの詳細は、「夏時間セッション・パラメータ」を参照してください -
夏時間のアップグレード・プロセスで使用される初期化パラメータ
DST_UPGRADE_INSERT_CONVの詳細は、「夏時間アップグレード・パラメータ」を参照してください
4.5 日時およびタイム・ゾーン・パラメータと環境変数
この項には次のトピックが含まれます:
4.5.1 日時書式パラメータ
次の表に、日時書式パラメータの名前と説明を示します。
表4-4 日時書式パラメータ
| パラメータ | 説明 |
|---|---|
|
|
|
|
|
|
|
|
|
デフォルト値はNLS_TERRITORYから導出されます。
値は、初期化パラメータ・ファイル内で設定することで指定できます。初期化パラメータ・ファイル内の値を変更する場合、インスタンスを再起動して変更を有効にする必要があります。クライアントの場合は、値をクライアント環境変数として指定することもできます。Javaクライアントの場合、NLS_TERRITORYの値はJREのデフォルト・ロケールから導出されます。初期化パラメータ・ファイルに指定されている値は、JDBCセッションで使用されません。
セッション中に値を変更するには、ALTER SESSION文を使用します。
関連項目:
4.5.2 タイム・ゾーン環境変数
-
ORA_TZFILE。クライアントおよびOracle Databaseサーバー上でタイム・ゾーンを指定できます。Oracle DatabaseサーバーでORA_TZFILEを指定した場合、この環境変数が有効になるのはデータベースの作成時のみです。 -
ORA_SDTZ。デフォルトのセッション・タイム・ゾーンを指定します。
4.5.3 夏時間セッション・パラメータ
ERROR_ON_OVERLAP_TIMEは、Oracle Databaseによる不明確な日時境界値の処理方法を決定するセッション・パラメータです。不明確な日時値は、夏時間と標準時間の間の切替時に発生することがあります。
可能な値はTRUEとFALSEです。ERROR_ON_OVERLAP_TIMEがTRUEの場合は、不明確な日時値が検出されるとエラーが戻されます。ERROR_ON_OVERLAP_TIMEがFALSEの場合、不明確な日時値は標準時間とみなされます。デフォルト値はFALSEです。
4.5.4 夏時間アップグレード・パラメータ
DST_UPGRADE_INSERT_CONVは、夏時間(DST)アップグレード・プロセスのアップグレード・ウィンドウでのみ使用する初期化パラメータです。これは、DSTのアップグレードで変更されるTIMESTAMP WITH TIME ZONE列のある表にのみ適用されます。
(DBMS_DSTパッケージで説明されている)DSTパッチ適用プロセスのアップグレード・ウィンドウでは、TIMESTAMP WITH TIMEZONEデータを含む表に対する新規タイム・ゾーン・バージョンへの変換が行われます。まだ変換されていない表の列には、前のタイム・ゾーン・バージョンを反映するTIMESTAMP WITH TIMEZONEが残っています。SELECT文の発行時に、これらの列のデータを新規タイム・ゾーン・バージョンに変換済であるかのように表すために、新規バージョンに変換するための変換演算子がこれらの列にデフォルトで追加されます。ただし、変換演算子を追加すると、問合せ速度が低下してTIMESTAMP WITH TIMEZONE列で索引が使用できなくなることがあります。そのため、OracleにはパラメータDST_UPGRADE_INSERT_CONVが用意されています。このパラメータは、まだアップグレードされていない表のTIMESTAMP WITH TIMEZONE列の上に内部演算子を割り当てるかどうかを指定します。デフォルト値はTRUEです。この変換プロセスがTIMESTAMP WITH TIMEZONE列に影響しないことが明らかな場合は、このパラメータをFALSEに設定してかまいません。
DSTパッチ適用プロセス全体で、このパラメータをTRUEに設定することをお薦めします。デフォルトではTRUEに設定されています。ただし、TRUEに設定すると、未変換の表に対する問合せのパフォーマンスが低下する可能性があります。これは、アップグレード・ウィンドウについてのみ該当することに注意してください。
4.6 タイム・ゾーン・ファイルの選択
Oracle Databaseタイム・ゾーン・ファイルには、有効なタイム・ゾーン名が含まれています。タイム・ゾーンごとに次の情報も含まれています。
-
協定世界時(UTC)からのオフセット。
-
夏時間への移行時間。
-
標準時間と夏時間の略称。
Oracle Databaseでは複数のバージョンのタイム・ゾーン・ファイルが提供されており、各バージョンに関連付けられている2種類のファイルがあります。1つはデータベースで定義されているすべてのタイム・ゾーンを含む大きいファイルで、もう1つは最も一般的に使用されるタイム・ゾーンのみを含む小さいファイルです。大きいバージョンのファイルの名前はtimezlrg_version_number.dat、小さいバージョンのファイルの名前はtimezone_version_number.datで、version_numberはそのタイム・ゾーン・ファイルのバージョン番号になります。タイム・ゾーン・ファイルは$ORACLE_HOME/oracore/zoneinfoディレクトリに格納され、デフォルトのタイム・ゾーン・ファイルは、バージョン番号が最も高い大きいタイム・ゾーン・ファイルです。Oracle Database19cでは、デフォルトのタイム・ゾーン・ファイルは$ORACLE_HOME/oracore/zoneinfo/timezlrg_32.datです。
タイム・ゾーン・ファイルの例を次に示します。
$ORACLE_HOME/oracore/zoneinfo/timezlrg_4.dat -- large version 4 $ORACLE_HOME/oracore/zoneinfo/timezone_4.dat -- small version 4 $ORACLE_HOME/oracore/zoneinfo/timezlrg_5.dat -- large version 5 $ORACLE_HOME/oracore/zoneinfo/timezone_5.dat -- small version 5
データベース作成プロセスでは、サーバーのタイム・ゾーン・バージョンを選択します。このバージョンは固定ですが、アップグレード・プロセスを実行してより新しいバージョンにすることは可能です。異なるバージョンのタイム・ゾーン・ファイルをクライアントとサーバーで使用できますが、これは行わないことをお薦めします。あるバージョンのクライアントと別のバージョンのサーバー間の通信には、パフォーマンス・ペナルティがあるためです。TIMESTAMP WITH TIME ZONE(TSTZ)データがUTCではなくローカル・タイムスタンプを使用して転送されるため、パフォーマンス・ペナルティが発生します。
サーバー側では、Oracle Databaseには常に大きいファイルが使用されます。クライアント側では、大きいファイルまたは小さいファイルのいずれかを使用できます。クライアントで大きいタイム・ゾーン・ファイルを使用している場合は、小さいファイルに切り替えたときに情報が失われないことが確実である場合を除き、引き続き大きいファイルを使用する必要があります。小さいファイルを使用する場合、クライアントで小さいタイム・ゾーン・ファイルに存在しないデータを問い合せることがないことを確認してください。問い合せた場合、エラーが発生します。
クライアントまたはサーバー上の特定のタイム・ゾーン・ファイルを有効にして使用できます。2つの状況で、サーバーのタイム・ゾーン・ファイルを有効にする場合があります。1つは、タイム・ゾーンをターゲット・バージョンにアップグレードする場合です。詳細は、「タイム・ゾーン・ファイルおよびタイム・ゾーン・データ付きタイムスタンプのアップグレード」を参照してください。もう1つは、新しいデータベースを作成する場合です。この場合、選択したタイム・ゾーン・ファイルを指すようにORA_TZFILE環境変数を設定できます。
クライアント上の特定のタイム・ゾーン・ファイルを有効にするには、ORA_TZFILEを、必要なタイム・ゾーン・ファイルに設定します。ORA_TZFILEを設定しない場合、Oracle Databaseでは、最新バージョンのタイム・ゾーン・ファイルが自動的に選択されて使用されます。
関連項目:
クライアントで夏時間をアップグレードする方法の詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください
ノート:
Oracle Databaseのタイム・ゾーン・データは、IANA関数のWebサイトにある公開共有情報から導出されます。Oracle Databaseのタイム・ゾーン・データには、このWebサイトから入手可能な最新データが反映されていない場合があります。
次の文を入力すると、データベースとともにインストールされるタイム・ゾーン・ファイルからタイム・ゾーンの名前と略称のリストを取得できます。
SELECT TZNAME, TZABBREV FROM V$TIMEZONE_NAMES ORDER BY TZNAME, TZABBREV;
デフォルトのタイム・ゾーン・ファイルの場合、この文の出力は次のようになります。
TZNAME TZABBREV -------------------- ---------- Africa/Abidjan GMT Africa/Abidjan LMT ... Africa/Algiers CEST Africa/Algiers CET Africa/Algiers LMT Africa/Algiers PMT Africa/Algiers WET Africa/Algiers WEST ... WET LMT WET WEST WET WET 2137 rows selected.
上の出力では、Africa/Abidjanタイム・ゾーンには2つのタイム・ゾーン略称が、またAfrica/Algiersタイム・ゾーンには6つの略称が関連付けられています。次の表に、タイム・ゾーン略称の一部とその意味を示します。
| タイム・ゾーン略称 | 意味 |
|---|---|
|
LMT |
ローカル標準時間 |
|
PMT |
パリ標準時間 |
|
WET |
西ヨーロッパ時間 |
|
WEST |
西ヨーロッパ夏時間 |
|
CET |
中央ヨーロッパ時間 |
|
CEST |
中央ヨーロッパ夏時間 |
|
EET |
東ヨーロッパ時間 |
|
EEST |
東ヨーロッパ夏時間 |
1つの略称が複数のタイム・ゾーンに関連付けられている場合があるため注意してください。たとえば、CETは、ヨーロッパのタイム・ゾーンに加えてAfrica/AlgiersとAfrica/Casablancaの両方に関連付けられています。
略称ごとにタイム・ゾーン名が繰り返されないタイム・ゾーン・リストが必要な場合は、次の問合せを使用します。
SELECT UNIQUE TZNAME FROM V$TIMEZONE_NAMES;
たとえば、バージョン11には次のような出力が含まれます。
TZNAME -------------------- Africa/Addis_Ababa Africa/Bissau Africa/Ceuta ... Turkey US/East-Indiana US/Samoa
デフォルトのタイム・ゾーン・ファイル、つまり、大きいタイム・ゾーン・ファイルには、350を超える一意のタイム・ゾーン名が含まれています。小さいタイム・ゾーン・ファイルには、180を超える一意のタイム・ゾーン名が含まれています。
関連項目:
-
有効なOracle Databaseのタイム・ゾーン名のリストは、「タイム・ゾーン・リージョン名」を参照してください
-
Oracle Databaseソフトウェア・インストールで提供される
$ORACLE_HOME/oracore/zoneinfo/timezdif.csvには、タイム・ゾーン・ファイルの各バージョンで変更されたタイム・ゾーンがすべてリストされています。 -
アップグレードの詳細は、『Oracle Databaseアップグレード・ガイド』を参照してください。
4.7 タイム・ゾーン・ファイルおよびタイム・ゾーン・データ付きタイムスタンプのアップグレード
Oracle Databaseによって提供されるタイム・ゾーン・ファイルを定期的に更新して、様々なタイム・ゾーン・リージョンに対する変更を反映します。データベースで現在使用しているタイム・ゾーン・ファイルを特定するには、V$TIMEZONE_FILEビューを問い合せます。
ノート:
各Oracle Databaseリリースには、リリース時点で最新のタイム・ゾーン・ファイルと、複数の旧バージョンのファイルが含まれています。Oracle Databaseのリリース間では、様々なタイム・ゾーン・リージョンの移行ルールの変更を反映するために、新しいタイム・ゾーン・ファイルがパッチ・セットまたは個別のパッチで提供される場合があります。古いタイム・ゾーン・ファイル・バージョンでは、タイム・ゾーン・ファイルを最新バージョンにすぐにアップグレードしなくても、アップグレードされたデータベースを実行できます。
夏時間(DST)移行ルールの変更
夏時間の実施の時期や方法に関する規則の変更は、政府が行います。政府による変更が行われると、Oracleでは、タイム・ゾーン・データ付きタイムスタンプを処理するための、一連の新規移行ルールを提供します。
夏時間が開始または終了する移行期には、タイム・ゾーン・データ付きタイムスタンプを処理する際に、問題(データ消失など)が発生する可能性があります。この移行を処理するために、PL/SQLパッケージDBMS_DSTとutltz_*スクリプトが用意されています。
Oracle DatabaseはTIMESTAMP WITH TIME ZONEデータ型のデータを内部的に格納するため、DST移行ルールに対する変更は、このデータ型の既存データに影響を与える可能性があります。ユーザーがタイム・ゾーン付きのタイムスタンプを入力すると、Oracle Databaseは、そのタイム・ゾーンの移行ルールに基づいて、そのデータをUTCに変換し、元のタイム・ゾーンIDとともに、そのデータをディスクに格納します。データを取り出すときは、UTCからの逆変換が起こります。たとえば、以前のバージョン2の移行ルールの場合、TIMESTAMP '2007-11-02 12:00:00 America/Los_Angeles'は、UTCの値'2007-11-02 20:00:00'と次のタイム・ゾーンIDとして格納されました。ロサンゼルス時間は、UTC-8時間(PST)として格納されました。バージョン3の移行ルールの場合、同じ日のオフセットは-7時間(PDT)です。2007年から、DSTの有効期間は今までよりも長くなりました(11月の第1日曜日、つまり2007年は11月4日まで)。先ほどのタイムスタンプをユーザーが取り出すと、格納されていたUTC時間に新しいオフセットが適応され、TIMESTAMP '2007-11-02 13:00:00 America/Los_Angeles'になります。以前のデータとバージョン3が適用されたデータの間には1時間の差があります。
ノート:
移行ルールが更新されているタイム・ゾーン・リージョンの場合、この項で説明したアップグレード・プロセスは、該当するDSTルール変更の有効日よりも未来を示すタイムスタンプに対してのみ影響します。たとえば、2007年よりも前のタイムスタンプには、'America/Los_Angeles'タイム・ゾーン・リージョンに対するバージョン3の変更による影響はありません。
タイム・ゾーン・ファイルおよびタイム・ゾーン・データ付きタイムスタンプのアップグレードの準備
タイム・ゾーン・データ、つまり、データベースのTIMESTAMP WITH TIME ZONE (TSTZ)データを実際にアップグレードする前に、アップグレードの影響がどのようになるかを確認する必要があります。一般的に、アップグレード・プロセスには、2つの個別のサブプロセス、つまり準備とアップグレードがあると考えることができます。アップグレードの準備をするには、準備ウィンドウを起動して、更新する必要のあるデータベース内のデータ量を確認します。アップグレードするには、アップグレード・ウィンドウを起動します。それは、データへの変更が実際に発生したときです。
必須ではありませんが、準備ステップを実行することをお薦めします。アップグレードの際に変更するデータ量の確認に加えて、アップグレードに必要な時間を見積ることができ、発生する可能性のあるセマンティック・エラーも発見できます。
タイム・ゾーン・ファイルおよびタイム・ゾーン・データ付きタイムスタンプをアップグレードする方法
次のいずれかの方法を使用して、最近リリースされたタイム・ゾーン・ファイルに基づいて、データベース内のタイム・ゾーン・データをアップグレードできます。
utltz_*スクリプトを使用するアップグレード方法は、Oracle Database 18cから導入され、DBMS_DSTパッケージを使用する方法と比べて実装が簡単です。
4.7.1 utltz_*スクリプトを使用したタイム・ゾーン・データのアップグレード
この項では、utltz_*スクリプトを使用したタイム・ゾーン・データ・アップグレードに関連する次の項目について説明します。
ノート:
utltz_*スクリプトを使用するこのアップグレード方法は、Oracle Database 18cから導入され、データベース内のタイム・ゾーン・データのアップグレードについて、DBMS_DSTパッケージを使用するもう1つのアップグレード方法と比べて実装が簡単です。
4.7.1.1 準備ウィンドウ
準備ウィンドウでは、$ORACLE_HOME/rdbms/adminディレクトリにある次のいずれかのスクリプトを実行して、タイム・ゾーン・データ・アップグレード中にデータベース内で更新する必要があるデータの量を確認できます。
-
utltz_countstats.sqlこのスクリプトは、
TIMESTAMP WITH TIME ZONE(TSTZ)データを含むすべての表のnum_rowsのオプティマイザ統計を示します。ノート:
データベース・オプティマイザ統計が最新の場合のみ
utltz_countstats.sqlスクリプトを実行し、そうでない場合はutltz_countstar.sqlスクリプトを実行してください。utltz_countstats.sqlスクリプトを実行する場合、utltz_countstar.sqlスクリプトを実行する必要はありません。 -
utltz_countstar.sqlこのスクリプトは、
TIMESTAMP WITH TIME ZONE(TSTZ)データを含むすべての表のcount(*)操作の結果を示します。ノート:
utltz_countstar.sqlスクリプトは、実行を完了するのに長時間かかる場合があります。
DBMS_SCHEDULER表には、通常、大量のタイム・ゾーン・データが含まれます。この表のデータが必要でない場合は、アップグレード・ステップを実行する前に、次のコマンドを使用して削除してください。このコマンドを実行する前に、メイン・ジョブを停止してください。これは、連鎖するジョブの一部のメイン・ジョブがまだ実行されている場合、DBMS_SCHEDULER表のデータがすべて削除されない可能性があるためです。
exec dbms_scheduler.purge_log;大量のタイム・ゾーン・データを含む可能性がある他の表は、SYS.WRI$_OPTSTAT_HISTGRM_HISTORYおよびSYS.WRI$_OPTSTAT_HISTHEAD_HISTORYの各表です。このデータが必要ではない場合、次のコマンドを使用して削除できます。
-- check the number of rows in the tables
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
-- check the data retention period of the stats
-- the default value is 31
select systimestamp - dbms_stats.get_stats_history_availability from dual;
-- disable stats retention
exec dbms_stats.alter_stats_history_retention(0);
-- remove all the stats
exec DBMS_STATS.PURGE_STATS(systimestamp);
-- check the result of the purge operation
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
タイム・ゾーン・データ・アップグレードが完了したら、次のコマンドを使用して、データ保持期間を元の値に設定できます。
exec dbms_stats.alter_stats_history_retention(31);4.7.1.2 アップグレード・ウィンドウ
アップグレード・ウィンドウでは、$ORACLE_HOME/rdbms/adminディレクトリにある次のスクリプトを実行して、データベース内のタイム・ゾーン・データをアップグレードできます。
-
$ORACLE_HOMEディレクトリからutltz_upg_check.sqlスクリプトを実行します。spool utltz_upg_check.log @utltz_upg_check.sql spool offスクリプトが正常に実行された後、次の情報が画面に表示されます。
INFO: A newer RDBMS DST version than the one currently used is found. INFO: Note that NO DST update was yet done. INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update. INFO: Note that the utltz_upg_apply.sql script will INFO: restart the database 2 times WITHOUT any confirmation or prompt.スクリプトは、次の情報を
alert.logファイルにも書き込みます。utltz_upg_checkで新しいRDBMS DSTv new_time_zone_versionが正常に見つかり、実行にnumber_of_minutes分かかりました。utltz_upg_check.sqlスクリプトが次のエラーを表示する場合、画面に表示される前のメッセージを確認し、適切に続行してください。ORA-20xxx: スクリプトの停止 - 前のメッセージを参照してください... -
utltz_upg_check.sqlスクリプトを正常に実行した後、$ORACLE_HOMEディレクトリからutltz_upg_apply.sqlスクリプトを実行します。spool utltz_upg_apply.log @utltz_upg_apply.sql spool offノート:
utltz_upg_apply.sqlスクリプトを実行するための前提条件を次に示します。-
タイム・ゾーン・アップグレード・プロセス中は、アプリケーションでデータベース内のタイム・ゾーン・データの問合せや挿入を行わないでください。
-
RAC環境では、単一のデータベース・インスタンスとしてRACデータベースを起動する必要があります。
-
マルチテナント環境では、CDBで
utltz_upg_apply.sqlスクリプトを実行する前に、すべてのPDBを停止する必要があります。
また、次の点にも注意してください。
-
utltz_upg_apply.sqlスクリプトの実行中に、データベースが自動的に複数回再起動されます。 -
utltz_upg_apply.sqlスクリプトは通常、utltz_upg_check.sqlスクリプトよりも実行にかかる時間が短くなります。
utltz_upg_apply.sqlスクリプトが正常に実行された後、次の情報が画面に表示されます。INFO: The RDBMS DST update is successfully finished. INFO: Make sure to exit this sqlplus session. INFO: Do not use it for timezone related selects.これで、
Registry$database表のTZ_VERSION列が新しいタイム・ゾーン・バージョンで更新されます。スクリプトによって次のエラー・メッセージが表示される場合、画面に表示されている前のメッセージを確認し、適切に続行してください。
ORA-20xxx: スクリプトの停止 - 前のメッセージを参照してください... -
ノート:
utltz_upg_check.sqlおよびutltz_upg_apply.sqlの実行中に何が起こっているか確認する場合、次のコマンドを実行してください。
set PAGES 1000
-- query the V$SESSION_LONGOPS view
select TARGET, TO_CHAR(START_TIME,'HH24:MI:SS - DD-MM-YY'),
TIME_REMAINING, SOFAR, TOTALWORK, SID, SERIAL#, OPNAME
from V$SESSION_LONGOPS
where sid in
(select SID from V$SESSION where CLIENT_INFO = 'upg_tzv')
and
SOFAR < TOTALWORK
order by START_TIME;
-- query the V$SESSION and V$SQLAREA views
select S.SID, S.SERIAL#, S.SQL_ID, S.PREV_SQL_ID,
S.EVENT#, S.EVENT, S.P1TEXT, S.P1, S.P2TEXT,
S.P2, S.P3TEXT, S.P3, S.TIME_REMAINING_MICRO,
S.SEQ#, S.BLOCKING_SESSION, BS.PROGRAM "Blocking Program",
Q1.SQL_TEXT "Current SQL", Q2.SQL_TEXT "Previous SQL"
from V$SESSION S, V$SQLAREA Q1, V$SQLAREA Q2, V$SESSION BS
where S.SQL_ID = Q1.SQL_ID(+) and
S.PREV_SQL_ID = Q2.SQL_ID(+) and
S.BLOCKING_SESSION = BS.SID(+) and
S.CLIENT_INFO = 'upg_tzv';ノート:
マルチテナント環境でのタイム・ゾーン・アップグレードに関連するノートは、次のとおりです。
-
CDBのタイム・ゾーン・バージョンを更新しても、PDBのタイム・ゾーン・バージョンは変更されません。
-
PDBのタイム・ゾーン・バージョンを更新しても、他のPDBのタイム・ゾーン・バージョンは変更されません。
-
新しいPDBには、常に
PDB$SEEDのタイム・ゾーン・バージョンが割り当てられます。 -
PDB$SEEDには、常にCDB作成時のタイム・ゾーン・バージョンが割り当てられます。PDB$SEEDのタイム・ゾーン・バージョンは変更できません。
4.7.2 DBMS_DSTパッケージを使用したタイム・ゾーン・データのアップグレード
この項では、DBMS_DSTパッケージを使用したタイム・ゾーン・データ・アップグレードに関連する次の項目について説明します。
4.7.2.1 準備ウィンドウ
準備ウィンドウでは、次のステップを使用して、タイム・ゾーン・アップグレード・プロセス中に影響を受けるデータに関する情報を取得できます。
- 後で
$ORACLE_HOME/oracore/zoneinfoへ移行する、必要なタイム・ゾーン・ファイルをインストールします。必要なバージョンがversion_numberである場合は、ファイルtimezlrg_version_number.datを追加する必要があります。ファイルtimezone_version_number.datは、ユーザーの判断により後から追加できます。これらのファイルは、My Oracle Supportで入手できます。最新のバージョンに、そのバージョンのリリース後に適正な政府によってロールバックされたDSTルール変更が含まれていないかぎり、バージョンは入手可能な最新バージョンである必要があります。 - オプションで、次の表を作成できます。
-
DBMS_DST.CREATE_ERROR_TABLEプロシージャを使用して、アップグレード・プロセス中に生成されたエラーを含むエラー表。この表を明示的に作成しない場合、デフォルトで使用される表はsys.dst$error_tableです。 -
DBMS_DST.CREATE_AFFECTED_TABLEプロシージャを使用して、影響を受けたタイムスタンプとタイム・ゾーン情報を含む表。この表を明示的に作成しない場合、デフォルトで使用される表はsys.dst$affected_tablesです。 -
DBMS_DST.CREATE_TRIGGER_TABLEプロシージャを使用して、無効化されたTSTZ表のトリガー情報を格納するトリガー表。この表を明示的に作成しない場合、デフォルトで使用される表はsys.dst$trigger_tableです。アップグレード・ウィンドウでは、Oracle Databaseにより、まずTSTZ表上のトリガーが無効化され、その後、影響を受けるTSTZデータのアップグレードが実行されることに注意してください。Oracle Databaseでは、sys.dst$trigger_table表内の無効にされたトリガーの情報も保存されます。表内の影響を受けるTSTZデータのアップグレードの終了後、無効化されたトリガーは、sys.dst$trigger_table表から情報を読み込まれて有効になり、sys.dst$trigger_table表からその情報が削除されます。アップグレード・ウィンドウでインスタンスが予期せず停止するなどの致命的エラーが発生した場合、sys.dst$trigger_table表をチェックして、アップグレードする前のアクティブな以前の状態に戻っていないトリガーがないかを確認します。
-
- プロシージャ
DBMS_DST.BEGIN_PREPARE(new_version)を実行します。new_versionは、ステップ1で選択したタイム・ゾーン・ファイルのバージョンです。 - プロシージャ
DBMS_DST.FIND_AFFECTED_TABLESを実行し、オプションでステップ2で作成したカスタム表の名前をパラメータとして渡して、影響を受けるデータに関する情報を収集します。sys.dst$affected_tablesまたは対応するカスタム表を問い合せて、影響を受ける列を検証します。また、sys.dst$affected_tables.error_countまたはカスタム表内の対応するerror_count列で、可能性のあるエラーをチェックすることが特に重要です。エラー件数が1以上の場合、sys.dst$error_tableまたは対応するカスタム・エラー表をチェックして、アップグレードで発生する可能性のあるエラーの種類を確認できます。アップグレード・エラー処理を参照してください。 - プロシージャ
DBMS_DST.END_PREPAREを実行することによって、準備ウィンドウを終了します。
ノート:
-
準備ウィンドウで一度に実行できるDBAは、1つのみです。また、アップグレードを実行する前に、すべてのエラーを修正する必要があります。
-
タイム・ゾーン・ファイルを更新するために使用可能なパッチのマトリクスは、Oracle SupportのDocument ID 412160.1を参照してください。
関連項目:
DBMS_DSTパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
4.7.2.2 アップグレード・ウィンドウ
アップグレード・ウィンドウでは、次のステップを使用してタイム・ゾーン・データをアップグレードできます。
ノート:
タイム・ゾーン列付きのタイムスタンプを含む表は、更新可能な状態であることが必要です。たとえば、列に検証済および無効なCHECK制約があると更新の妨げになるため、このようなCHECK制約を列に含めることはできません。
表のサイズが2GBを超える場合は、パラレル・オプションを使用することをお薦めします。また、発生するすべてのセマンティック・エラーをOracleで処理することをお薦めします。
エラー表、トリガー表または影響を受ける表のCREATE文を実行する際は、スキーマ名ではなく表名のみを渡す必要があります。これは、CREATE文の実行元となるスキーマ内に表が作成されるためです。
関連項目:
DBMS_DSTパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
4.7.2.3 アップグレードの例
この例では、デフォルトのタイム・ゾーン・バージョンが14であるOracle Database 11gリリース2へのDSTの動作の更新を示します。まず、現在のデータベースでタイム・ゾーンのバージョン3が使用され、タイム・ゾーン・データ付きタイムスタンプを含む既存の表tがあるとします。
ユーザーscottとしてデータベースに接続し、次の文を実行します。
DROP TABLE t;
CREATE TABLE t (c NUMBER, mark VARCHAR(25), ts TIMESTAMP WITH TIME ZONE);
INSERT INTO t VALUES(1, 'not_affected',
to_timestamp_tz('22-sep-2006 13:00:00 america/los_angeles',
'dd-mon-yyyy hh24:mi:ss tzr tzd'));
INSERT INTO t VALUES(4, 'affected_err_exist',
to_timestamp_tz('11-mar-2007 00:30:00 america/st_johns',
'dd-mon-yyyy hh24:mi:ss tzr tzd'));
INSERT INTO t VALUES(6, 'affected_no_err',
to_timestamp_tz('11-mar-2007 01:30:00 america/st_johns',
'dd-mon-yyyy hh24:mi:ss tzr tzd'));
INSERT INTO t VALUES(14, 'affected_err_dup',
to_timestamp_tz('21-sep-2006 23:30:00 egypt',
'dd-mon-yyyy hh24:mi:ss tzr tzd'));
COMMIT;
次に、オプションで、準備ウィンドウを起動して、影響を受けるデータおよびオーバーラップや存在しない時間などの潜在的なセマンティック・エラーをチェックできます。それには、タイム・ゾーン・バージョン14に移行する準備のためのウィンドウを起動する必要があります。必要な権限は付与されていることを前提とします。これらの権限は、DBMS_DSTパッケージで制御されます。
関連項目:
DBMS_DSTパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
例として、最初にウィンドウを準備します。
connect / as sysdba set serveroutput on EXEC DBMS_DST.BEGIN_PREPARE(14); A prepare window has been successfully started. PL/SQL procedure successfully completed.
引数14を使用すると、この文でタイム・ゾーン・バージョン14が使用されます。このウィンドウが正常に起動された後、次の例に示すように、DATABASE_PROPERTIES内のDSTのステータスをチェックできます。
SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;
次のような出力が表示されます。
PROPERTY_NAME VALUE --------------------------- --------- DST_PRIMARY_TT_VERSION 3 DST_SECONDARY_TT_VERSION 14 DST_UPGRADE_STATE PREPARE
次に、DBMS_DST.FIND_AFFECTED_TABLESを実行して、バージョン3からバージョン14へのアップグレードにより影響を受けるデータベース内のすべての表を検索できます。この表には、表所有者、表名、列名、行数およびエラー件数が含まれます。ここで、デフォルトのエラー表(sys.dst$error_table)および影響を受ける表(sys.dst$affected_table)を使用するか、独自の表を作成するかを選択できます。この例では、DBMS_DST.CREATE_ERROR_TABLEおよびDBMS_DST.CREATE_AFFECTED_TABLEを使用して独自の表を作成し、次に示すように、FIND_AFFECTED_TABLESに渡します。
ユーザーscottとしてデータベースに接続し、次の文を実行します。
EXEC DBMS_DST.CREATE_AFFECTED_TABLE('my_affected_tables');
EXEC DBMS_DST.CREATE_ERROR_TABLE('my_error_table');
これらの表内に行がないことを確認してください。これを行うには、表を次のように切り捨てます。
TRUNCATE TABLE my_affected_tables; TRUNCATE TABLE my_error_table;
次に、FIND_AFFECTED_TABLESを実行して、アップグレードで影響を受ける表を確認します。
connect / as sysdba
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES(affected_tables => 'scott.my_affected_tables',
log_errors => TRUE,
log_errors_table => 'scott.my_error_table');
END;
/
次に、影響を受ける表をチェックします。
SELECT * FROM scott.my_affected_tables; TABLE_OWNER TABLE_NAME COLUMN_NAM ROW_COUNT ERROR_COUNT ----------- ---------- ---------- --------- ----------- SCOTT T TS 3 2
次に、エラー表をチェックします。
SELECT * FROM scott.my_error_table; TABLE_OWNER TABLE_NAME COLUMN_NAME ROWID ERROR_NUMBER ----------- ---------- ----------- ------------------ ------------ SCOTT T TS AAAPW3AABAAANzoAAB 1878 SCOTT T TS AAAPW3AABAAANzoAAE 1883
これらのエラーは、アップグレード・エラー処理の説明に従って修正できます。その後、次の文に示すように準備ウィンドウを終了します。
EXEC DBMS_DST.END_PREPARE; A prepare window has been successfully ended. PL/SQL procedure successfully completed.
これが終了したら、DATABASE_PROPERTIES内のDSTステータスをチェックできます。
SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name; PROPERTY_NAME VALUE ------------------------ -------- DST_PRIMARY_TT_VERSION 3 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE
次に、アップグレード・ウィンドウを使用して、影響を受けるデータをアップグレードできます。これを行うには、最初にアップグレード・ウィンドウを起動します。DBMS_DST.BEGIN_UPGRADEを実行する前に、データベースをUPGRADEモードでオープンする必要があります。Oracle RACでは、1つのインスタンスのみを起動できます。BEGIN_UPGRADEにより、すべてのディクショナリ表が1つのトランザクションでアップグレードされるため、起動は全体として成功または失敗します。プロシージャの実行中、TSTZデータのあるすべてのユーザー表は、アップグレード進行中としてマークされます。詳細は、『Oracle Databaseアップグレード・ガイド』を参照してください。
また、アップグレード・ウィンドウを起動できるのは、SYSDBAのみです。データベースをUPGRADEモードでオープンせずにBEGIN_UPGRADEを起動すると、次のエラーが発生します。
EXEC DBMS_DST.BEGIN_UPGRADE(14); BEGIN DBMS_DST.BEGIN_UPGRADE(14); END; * ERROR at line 1: ORA-56926: database must be in UPGRADE mode in order to start an upgrade window ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DST", line 1021 ORA-06512: at line 1
したがって、BEGIN_UPGRADEでは、TSTZデータを含むシステム表がアップグレードされ、(TSTZデータを含む)ユーザー表がUPGRADE_IN_PROGRESSプロパティでマーク付けされます。これは、この例で後述するDBA_TSTZ_TABLESでチェックできます。
BEGIN_UPGRADEには、セマンティック・エラーを処理する2つのパラメータがあります。1つはerror_on_overlap_time(エラー番号ORA-1883)で、もう1つはerror_on_nonexisting_time(エラー番号ORA-1878)です。パラメータでデフォルト設定のFALSEが使用されると、Oracleではデフォルトの変換を使用してデータが変換され、エラーは表示されません。これら意味と、エラーの処理方法の詳細は、アップグレード・エラー処理を参照してください。
ディクショナリ表のアップグレードの際、次のコールにより、いくつかのデフォルト値に基づいてセマンティック・エラーが自動的に修正されます。セマンティック・エラーを無視せず、ディクショナリ表でこのようなエラーが発生する場合、BEGIN_UPGRADEは失敗します。これらのセマンティック・エラーは、sys.dst$error_tableに移入されます。
EXEC DBMS_DST.BEGIN_UPGRADE(14); An upgrade window has been successfully started. PL/SQL procedure successfully completed.
これが終了した後、次のようにしてDATABASE_PROPERTIES内のDSTステータスをチェックできます。
SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name; PROPERTY_NAME VALUE ------------------------ -------- DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 3 DST_UPGRADE_STATE UPGRADE
次に、どのユーザー表にUPGRADE_IN_PROGRESSのマークが付いているかチェックします。
SELECT owner, table_name, upgrade_in_progress FROM dba_tstz_tables; OWNER TABLE_NAME UPGRADE_IN_PROGRESS ----- ------------------------- ------------------- SYS WRI$_OPTSTAT_AUX_HISTORY NO SYS WRI$_OPTSTAT_OPR NO SYS OPTSTAT_HIST_CONTROL$ NO SYS SCHEDULER$_JOB NO SYS KET$_AUTOTASK_STATUS NO SYS AQ$_ALERT_QT_S NO SYS AQ$_KUPC$DATAPUMP_QUETAB_S NO DBSNMP MGMT_DB_FEATURE_LOG NO WMSYS WM$VERSIONED_TABLES NO SYS WRI$_OPTSTAT_IND_HISTORY NO SYS OPTSTAT_USER_PREFS$ NO SYS FGR$_FILE_GROUP_FILES NO SYS SCHEDULER$_WINDOW NO SYS WRR$_REPLAY_DIVERGENCE NO SCOTT T YES IX AQ$_ORDERS_QUEUETABLE_S YES ...
この出力では、ディクショナリ表(SYSスキーマ内)はBEGIN_UPGRADEでアップグレードされています。SCOTT.Tなどのユーザー表はアップグレードされていず、進行中です。
ここで、DBMS_DST.UPGRADE_DATABASEを使用してユーザー表のアップグレードを実行できます。すべての表をアップグレードする必要があり、そうしないと、END_UPGRADEプロシージャを使用してアップグレード・ウィンドウを終了できません。このステップを行う前に、データベースをnormalモードで再起動する必要があります。次に構文の例を示します。
VAR numfail number;
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => TRUE,
error_on_nonexisting_time => TRUE);
DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:'|| :numfail);
END;
/
エラーが発生した場合、エラーを修正し、個々の表でUPGRADE_TABLEを使用します。その場合、マテリアライズド・ビュー・ベース表、マテリアライズド・ビュー・ログ表およびマテリアライズド・ビュー・コンテナ表などのマテリアライズド・ビューに関連する表の処理が必要になることがあります。これらの表のアップグレードの際に、考慮が必要ないくつかの事項があります。まず、ベース表およびそのマテリアライズド・ビュー・ログ表を、アトミックにアップグレードする必要があります。次に、すべてのベース表およびマテリアライズド・ビュー・ログ表のアップグレードが終了してから、マテリアライズド・ビュー・コンテナ表をアップグレードする必要があります。一般に、Oracle Databaseでデフォルト処理を実行して、セマンティック・エラーを処理することをお薦めします。
この例では、UPGRADE_DATABASEを実行後、SCOTT.Tでエラーがいくつか発生したとします。その場合、次の問合せを使用してこれらのエラーをチェックできます。
SELECT * FROM sys.dst$error_table; TABLE_OWNER TABLE_NAME COLUMN_NAME ROWID ERROR_NUMBER ----------- ---------- ----------- ------------------ ------------ SCOTT T TS AAAO2XAABAAANrgAAD 1878 SCOTT T TS AAAO2XAABAAANrgAAE 1878
出力に、番号1878のエラーが表示されています。このエラーは、存在しない時間に対してエラーが発生したことを示します。
SCOTT.Tにマテリアライズド・ビュー・ログscott.mlog$_tがあり、SCOTT.Tに単一のマテリアライズド・ビューがあると想定して、この例を続けます。ここで、この1878エラーが修正されているとします。
最後に、表、マテリアライズド・ビュー・ログおよびマテリアライズド・ビューを次のようにしてアップグレードできます。
VAR numfail number;
BEGIN
DBMS_DST.UPGRADE_TABLE(:numfail,
table_list => 'SCOTT.t, SCOTT.mlog$_T',
parallel => TRUE,
continue_after_errors => FALSE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => TRUE,
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
atomic_upgrade => TRUE);
DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:'|| :numfail);
END;
/
VAR numfail number;
BEGIN
DBMS_DST.UPGRADE_TABLE(:numfail,
table_list => 'SCOTT.MYMV_T',
parallel => TRUE,
continue_after_errors => FALSE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => TRUE,
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
atomic_upgrade => TRUE);
DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:'|| :numfail);
END;
/
atomic_upgradeパラメータを使用すると、表とそのマテリアライズド・ビュー・ログのアップグレードを組み合せることができます。
すべての表のアップグレード後、END_UPGRADEを起動して、次に示すようにアップグレード・ウィンドウを終了します。
VAR numfail number;
BEGIN
DBMS_DST.END_UPGRADE(:numfail);
DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:'|| :numfail);
END;
/
影響を受けるすべての表が正常にアップグレードされた場合はアップグレード・ウィンドウが終了し、そうでない場合は正常にアップグレードされなかった表の数が出力に表示されます。
4.7.2.4 アップグレード・エラー処理
アップグレードの際、発生する可能性があるセマンティック・エラーは主に3つあります。1つ目は存在する時間が存在しない時間になる場合、2つ目は時間が重複する場合、3つ目は重複する時間が重複しない時間になる場合です。
1つ目のエラーの例として、2007年に太平洋標準時間(PST)が太平洋夏時間(PDT)に変更される場合を考えます。この変更は、バージョン3 (およびそれ以降の32までのバージョン)に基づいて2007年3月11日午前2時に行われ、時計が午前3時まで1時間進められると、午前2時と午前3時の間にギャップが発生します。バージョン2では、この時間の変更は、2007年4月1日に発生します。タイム・ゾーン・ファイルをバージョン2からバージョン3にアップグレードすると、2007年3月11日の午前2時と午前3時の間の時間は無効であり、ERROR_ON_NONEXISTING_TIMEがTRUEに設定されている場合、エラー(ORA-1878)が発生します。したがって、存在しない時間の問題が起こります。ERROR_ON_NONEXISTING_TIMEが、このパラメータのデフォルトであるFALSEに設定されている場合、このエラーはレポートされず、OracleではUTC時間が保持されます。バージョン2とバージョン3のどちらも同じUTCタイムスタンプに変換されるため、たとえば、バージョン2の"Mar-11-2007 02:30 PST"は、バージョン3では"Mar-11-2007 03:30 PDT"になります。
2つ目のエラーでは、PDTからPSTに変更する場合を例に考えます。たとえば、2007年のバージョン3では、2007年11月4日に変更が発生し、このとき、午前2時から午前1時に時間が戻ります。これは、2007年11月4日の<午前1時、午前2時>内の時間が、PSTとPDTで1回ずつ、2回存在することを意味します。バージョン2では、この移行は、2007年10月28日午前2時に発生します。したがって、バージョン2では一意に識別される2007年11月4日の<午前1時、午前2時>内のタイムスタンプは、ERROR_ON_OVERLAP_TIMEがTRUEに設定されている場合、バージョン3ではエラー(ORA-1883)になります。このパラメータをデフォルト設定のFALSEのままにすると、UTCタイムスタンプ値が保持され、エラーは発生しません。この状況では、バージョン2からバージョン3に変更すると、バージョン2のタイムスタンプ"Nov-04-2007 01:30 PST"は、バージョン3では"Nov-04-2007 01:30 PST"になります。
3つ目のエラーは、重複する時間が重複しない時間になる場合に発生します。2007年のPDTからPSTへの移行を例にすると、バージョン2の2007年10月28日の<午前1時、午前2時>は重なっている期間です。したがって、バージョン2では"Oct-28-2007 01:30 PDT"と"Oct-28-2007 01:30 PST"はどちらも有効なタイムスタンプです。ERROR_ON_OVERLAP_TIMEがTRUEに設定されている場合、バージョン2からバージョン3へのアップグレード時にORA-1883エラーが発生します。ただし、ERROR_ON_OVERLAP_TIMEがFALSE(このパラメータのデフォルト値)に設定されている場合は、ローカル時間が保持されるため、エラーはレポートされません。この場合、バージョン2の"Oct-28-2007 01:30 PDT"と"Oct-28-2007 01:30 PST"は、いずれもバージョン3では同じ"Oct-28-2007 01:30 PDT"に変換されます。ERROR_ON_OVERLAP_TIMEをFALSEに設定した場合は、一部の時間の順序が逆になる可能性があることに注意してください。たとえば、バージョン2で"Oct-28-2007 01:45 PDT"にスケジュールされているジョブ(ジョブA)は、"Oct-28-2007 01:30 PST"にスケジュールされているジョブ(ジョブB)より先に実行されると考えられます。バージョン3へのアップグレード後、ジョブAは"Oct-28-2007 01:45 PDT"にスケジュールされ、ジョブBはそのまま"Oct-28-2007 01:30 PDT"にスケジュールされるため、ジョブBがジョブAより先に実行されます。連鎖していないスケジュール済ジョブは特定の順序で実行されるよう保証されていませんが、スケジュール済ジョブを設定する際には、この問題を考慮する必要があります。
4.8 異なるバージョンのタイム・ゾーン・ファイルで動作するクライアントとサーバー
Oracle Database 11gリリース11.2以上では、クライアントとサーバーで異なるバージョンのタイム・ゾーン・ファイルを使用できます。この動作モードはOracle Databaseの以前のリリースではサポートされていませんでした。このような混合モードで使用するには、クライアントとサーバーの両方がOracle Database 11gリリース11.2以上である必要があります。
OCI、JDBC、Pro*CおよびSQL*Plusの各クライアントは、クライアント側タイム・ゾーン・ファイルを更新しなくてもデータベース・サーバーとの通信を続行できるようになりました。その他の製品では、製品固有のドキュメントに明記されていないかぎり、このようなクライアントは、クライアントと異なるタイム・ゾーン・ファイルを使用するデータベース・サーバーでは運用できないと考える必要があります。そうでない場合、クライアントでのリージョンIDベースのTIMESTAMP WITH TIMEZONE値の計算結果に一貫性がなくなる可能性があります。これは、クライアントとサーバーの異なるバージョンのタイム・ゾーン・ファイル間で影響を受けるタイム・ゾーン・リージョンに対して、異なる夏時間(DST)ルールが有効であるためです。
アプリケーションが異なるデータベースに直接またはデータベース・リンクを介して接続する場合は、すべてのデータベースに同じバージョンのタイム・ゾーン・ファイルを使用することをお薦めします。そうでない場合、これらのデータベースのTIMESTAMP WITH TIMEZONE値の計算結果に一貫性がなくなる可能性があります。これは、様々なデータベース・サーバーの異なるバージョンのタイム・ゾーン・ファイル間で影響を受けるタイム・ゾーン・リージョンに対して、異なるDSTルールが有効であるためです。
4.9 データベースのタイム・ゾーンの設定
データベースのタイム・ゾーンは、データベースの作成時に、CREATE DATABASE文のSET TIME_ZONE句を使用して設定します。データベースのタイム・ゾーンを設定しなければ、デフォルトでサーバーのオペレーティング・システムのタイム・ゾーンに設定されます。
タイム・ゾーンは、指定のリージョンまたはUTCからの絶対オフセットに設定できます。タイム・ゾーンを指定のリージョンに設定するには、次のような文を使用します。
CREATE DATABASE db01 ... SET TIME_ZONE='Europe/London';
たとえば、タイム・ゾーンをUTCからのオフセットに設定するには、次のような文を使用します。
CREATE DATABASE db01 ... SET TIME_ZONE='-05:00';
有効なオフセットの範囲は-12:00から+14:00です。
ノート:
データベースのタイム・ゾーンが関連するのは、TIMESTAMP WITH LOCAL TIME ZONE列のみです。データベース間でのデータ転送時にデータ変換を回避してパフォーマンスを改善するために、データベースのタイム・ゾーンはUTC(0:00)に設定することをお薦めします。これは、分散データベース、レプリケーションおよびエクスポートとインポートには特に重要です。
データベースのタイム・ゾーンは、ALTER DATABASE文のSET TIME_ZONE句を使用して変更できます。たとえば、次のようにします。
ALTER DATABASE SET TIME_ZONE='Europe/London'; ALTER DATABASE SET TIME_ZONE='-05:00';
データベースにTIMESTAMP WITH LOCAL TIME ZONE列を含む表があり、その列にデータがあると、ALTER DATABASE SET TIME_ZONE文はエラーを返します。
変更は、データベースを停止して再起動するまで有効になりません。
次の問合せを入力すると、データベースのタイム・ゾーンを確認できます。
SELECT dbtimezone FROM DUAL;
4.10 セッションのタイム・ゾーンの設定
デフォルトのセッション・タイム・ゾーンは、ORA_SDTZ環境変数を使用して設定できます。ユーザーがTIMESTAMP WITH LOCAL TIME ZONEデータを検索すると、そのユーザー・セッションのタイム・ゾーンで戻されます。セッションのタイム・ゾーンは、TIMESTAMP値がTIMESTAMP WITH TIME ZONEまたはTIMESTAMP WITH LOCAL TIME ZONEデータ型に変換される場合にも有効です。
ノート:
セッションのタイム・ゾーンを設定しても、SYSDATEおよびSYSTIMESTAMP SQL関数から戻される値には影響しません。SYSDATEは、データベースの起動時に有効だったデータベース・サーバーのオペレーティング・システムのタイム・ゾーンを考慮して、データベースが常駐するオペレーティング・システムの日時を戻します。
ORA_SDTZ環境変数は次の値に設定できます。
-
オペレーティング・システムのローカルのタイム・ゾーン(
'OS_TZ') -
データベースのタイム・ゾーン(
'DB_TZ') -
UTCからの絶対オフセット(
'-05:00'など) -
タイム・ゾーン・リージョン名(
'Europe/London'など)
UNIX環境(Cシェル)でORA_SDTZを設定するには、次のような文を使用します。
% setenv ORA_SDTZ 'OS_TZ' % setenv ORA_SDTZ 'DB_TZ' % setenv ORA_SDTZ 'Europe/London' % setenv ORA_SDTZ '-05:00'
Microsoft Windows環境でORA_SDTZ変数を設定するときに、レジストリ、システム環境変数、またはコマンド・プロンプト・ウィンドウで、タイム・ゾーン値を引用符で囲まないでください。
ORA_SDTZ変数のデフォルト値は、変数が設定されていない場合や無効な値に設定されている場合に使用され、'OS_TZ'です。
ALTER SESSION文のSET TIME_ZONE句を使用して、特定のSQLセッションに対するタイム・ゾーンを変更できます。
TIME_ZONEは次の値に設定できます。
-
セッション開始時のデフォルトのローカル・タイム・ゾーン(
local) -
データベースのタイム・ゾーン(
dbtimezone) -
UTCからの絶対オフセット(
'+10:00'など) -
タイム・ゾーン・リージョン名(
'Asia/Hong_Kong'など)
次のようなALTER SESSION文を使用します。
ALTER SESSION SET TIME_ZONE=local; ALTER SESSION SET TIME_ZONE=dbtimezone; ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong'; ALTER SESSION SET TIME_ZONE='+10:00';
次の問合せを入力すると、現行のセッションのタイム・ゾーンを確認できます。
SELECT sessiontimezone FROM DUAL;
4.11 AT TIME ZONE句を使用したタイム・ゾーンの変換
日時SQL式には、次のいずれかを使用できます。
-
日時列
-
日時値となる複合式
日時式には、AT LOCAL句またはAT TIME ZONE句を含めることができます。AT LOCAL句を含めると、結果は現在のセッションのタイム・ゾーンで戻されます。AT TIME ZONE句を含める場合は、その句で次のいずれかの設定を使用します。
-
タイム・ゾーン・オフセット: 文字列
'(+|-)HH:MM'で、タイム・ゾーンをUTCからのオフセットとして指定します。たとえば、'-07:00'はUTCから7時間遅れのタイム・ゾーンを指定します。UTC時間が午前11時の場合、'-07:00'タイム・ゾーンの時間は午前4時です。 -
DBTIMEZONE: Oracle Databaseでは、データベース作成時に(明示的またはデフォルトで)設定されたデータベースのタイム・ゾーンを使用します。 -
SESSIONTIMEZONE: Oracleでは、デフォルトまたは最新のALTER SESSION文で設定されたセッションのタイム・ゾーンが使用されます。 -
タイム・ゾーン・リージョン名: Oracle Databaseでは、タイム・ゾーン・リージョン名が示すタイム・ゾーンの値を返します。たとえば、
Asia/Hong_Kongを指定できます。 -
式: 式で有効なタイム・ゾーン書式の文字列が戻されると、Oracle Databaseはそのタイム・ゾーンの入力を返します。そうでない場合は、エラーが戻ります。
次の例では、America/New_Yorkタイム・ゾーンの日時値をAmerica/Los_Angelesタイム・ゾーンの日時値に変換しています。
例4-5 他のタイム・ゾーンへの日時値の変換
SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York')
AT TIME ZONE 'America/Los_Angeles' "West Coast Time"
FROM DUAL;
West Coast Time
----------------------------------------------------------
01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES4.12 夏時間のサポート
Oracle Databaseは、指定のタイム・ゾーンで夏時間が有効かどうかを自動的に判別し、対応するローカル時間を返します。Oracle Databaseで夏時間が特定のタイム・ゾーンで有効かどうかを判別するには、通常、日時値で十分です。夏時間の開始期間または終了期間は境界事例です。たとえば、米国東部では、夏時間が有効になる時点で、時刻が午前1時59分59秒から午前3時0分0秒に変わります。午前2時0分0秒～午前2時59分59秒の時間隔は存在しません。その時間隔に該当する値は無効です。夏時間の終了時には、時刻が午前2時0分0秒から午前1時0分1秒に変わります。午前1時0分1秒～午前2時0分0秒の時間隔が繰り返されます。この時間隔に含まれる値は、2回発生するため不明確となります。
このような境界事例を解決するために、Oracle DatabaseではTZRおよびTZD書式要素を使用します。TZRは、日時入力文字列でのタイム・ゾーン・リージョンを表します。たとえば、'Australia/North'、'UTC'および'Singapore'などです。TZDは、夏時間情報を含むタイム・ゾーン・リージョンの略称を表します。たとえば、米国太平洋標準時間の場合は'PST'、米国太平洋夏時間の場合は'PDT'です。TZRおよびTZD書式要素に対して有効な値のリストを表示するには、V$TIMEZONE_NAMES動的パフォーマンス・ビューのTZNAME列とTZABBREV列を問い合せます。
関連項目:
-
ERROR_ON_OVERLAP_TIMEセッション・パラメータの詳細は、
『Oracle Database SQL言語リファレンス』を参照してください。 -
有効なタイム・ゾーン名のリストは、「タイム・ゾーン・リージョン名」を参照してください
4.12.1 例: 日時計算への夏時間の影響
TIMESTAMPデータ型はタイム・ゾーン値を受け入れず、夏時間は計算されません。
TIMESTAMP WITH TIME ZONEおよびTIMESTAMP WITH LOCAL TIME ZONEデータ型は、次のように動作します。
-
タイム・ゾーン・リージョンが日時値に関連付けられている場合、データベース・サーバーではリージョンの夏時間規則が認識され、その規則が計算に使用されます。
-
夏時間を使用しないリージョンについては、夏時間は計算されません。
これ以降は、日時データ型を使用する例について説明します。例ではglobal_orders表を使用します。この表には、TIMESTAMPデータ型のorderdate1列とTIMESTAMP WITH TIME ZONEデータ型のorderdate2列が含まれます。global_orders表は次のように作成されます。
CREATE TABLE global_orders ( orderdate1 TIMESTAMP(0),
orderdate2 TIMESTAMP(0) WITH TIME ZONE);
INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM',
'28-OCT-00 11:24:54 PM America/New_York');
例4-6 TIMESTAMP WITH TIME ZONEおよびTIMESTAMPを使用した夏時間計算の比較
SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR
FROM global_orders;
出力結果は、次のようになります。
ORDERDATE1+INTERVAL'8'HOUR ORDERDATE2+INTERVAL'8'HOUR -------------------------- -------------------------- 29-OCT-00 07.24.54.000000 AM 29-OCT-00 06.24.54.000000 AM AMERICA/NEW_YORK
この例は、各列に8時間追加した場合の影響を示しています。この期間には、夏時間の境界(夏時間から標準時間への切替え)が含まれています。orderdate1列はTIMESTAMPデータ型で、夏時間情報は使用されないため、8時間内に発生する切替えにあわせた調整は実行されません。TIMESTAMP WITH TIME ZONEデータ型では切替えにあわせて調整されるため、orderdate2列はorderdate1列より1時間早い時刻を示します。
例4-7 TIMESTAMP WITH LOCAL TIME ZONEおよびTIMESTAMPを使用した夏時間計算の比較
TIMESTAMP WITH LOCAL TIME ZONEデータ型では、セッション環境にあわせて設定されたTIME_ZONEの値が使用されます。次の文は、TIME_ZONEセッション・パラメータの値を設定してglobal_orders表を作成します。global_orders表には、TIMESTAMPデータ型の1列とTIMESTAMP WITH LOCAL TIME ZONEデータ型の1列があります。
ALTER SESSION SET TIME_ZONE='America/New_York';
CREATE TABLE global_orders ( orderdate1 TIMESTAMP(0),
orderdate2 TIMESTAMP(0) WITH LOCAL TIME ZONE );
INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM',
'28-OCT-00 11:24:54 PM' );
両方の列に8時間を追加します。
SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR FROM global_orders;
orderdate2の日時値にはタイム・ゾーン・リージョンが関連付けられているため、Oracle Databaseサーバーではそのリージョンに夏時間規則が使用されます。そのため、出力は例4-6と同じになります。TIMESTAMPデータ型の場合は夏時間が計算されないため、2つの計算には1時間の時差があり、計算は夏時間境界にまたがっています。
例4-8 夏時間を使用しないリージョンについては計算されない夏時間
タイム・ゾーン・リージョンをUTCに設定します。UTCでは、夏時間は使用されません。
ALTER SESSION SET TIME_ZONE='UTC';
global_orders表を切り捨てます。
TRUNCATE TABLE global_orders;
global_orders表に値を挿入します。
INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM',
TIMESTAMP '2000-10-28 23:24:54 ' );
各列に8時間を追加します。
SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR FROM global_orders;
出力は次のようになります。
ORDERDATE1+INTERVAL'8'HOUR ORDERDATE2+INTERVAL'8'HOUR -------------------------- --------------------------- 29-OCT-00 07.24.54.000000000 AM 29-OCT-00 07.24.54.000000000 AM UTC
UTCタイム・ゾーン・リージョンの場合は夏時間が計算されないため、時刻は同じです。