TIME_BUCKET (日時)

構文


time_bucket.epsの説明が続きます
図time_bucket.epsの説明

timebucket_optional_clause::=


timebucket_optional_clause.epsの説明が続きます
図timebucket_optional_clause.epsの説明

目的

指定した時間隔の日時を取得するには、TIME_BUCKET (日時)を使用します。

TIME_BUCKETには、3つの必須引数と2つのオプション引数があります。

  • 最初の引数datetimeは、バケットへの入力です。

    3番目の引数originは、すべてのバケットが位置合せされるアンカーです。

    datetimeとoriginには、DATE、TIMESTAMP、TIMESTAMP WITH TIMEZONE、TIMESTAMP WITH LOCAL TIMEZONE、EPOCH TIME、BINARY_FLOAT、BINARY_DOUBLE、CHAR、式またはバインド変数を指定できます。

    EPOCH TIMEは、Oracle型NUMBERで表されます。これは、UTCでの1970年1月1日00:00:00以降の経過秒数です。EPOCH TIMEのサポート対象範囲は、SB8MINVAL(- 9223372036854775808、すべて含まれる)からSB8MAXVAL(9223372036854775807、すべて含まれる)までです。

    datetimeとoriginの場合は、暗黙的な変換があります。

    • それがBINARY_FLOATまたはBINARY_DOUBLEである場合は、暗黙的にNUMBERに変換されます。暗黙的な変換による精度の損失を考慮する必要があることに注意してください。

    • それがCHARである場合は、暗黙的にTIMESTAMPに変換されます。なお、CHARはセッションNLS_TIMESTAMP_FORMATと一致している必要があります。そうでない場合は、エラーが発生します。

    小数秒は、datetimeとoriginがEPOCH TIME、BINARY_FLOATまたはBINARY_DOUBLEである場合のみサポートされます。

    datetimeとoriginの有効範囲は、-4712-01-01 00:00:00から9999-12-31 23:59:59:00まで(境界値を含む)です。

  • 2番目の引数strideは、正のOracle INTERVAL、ISO 8601の時間間隔文字列、式またはバインド変数です。小数秒は、datetimeとoriginがEPOCH TIME、BINARY_FLOATまたはBINARY_DOUBLEである場合のみサポートされます。

    OracleのINTERVALでの有効な間隔は、INTERVAL YEAR TO MONTHとINTERVAL DAY TO SECONDという2種類です。年または月を指定した場合、その他の単位は、指定されていてもすべて無視されます。

    ISO 8601の時間間隔文字列の場合、years、months、days、hours、minutesおよびsecondsは、0と999999999の間の整数です。frac_secsは、.0と.999999999の間の、秒の小数部です。

    指定したISO 8601時間間隔文字列が、OracleのINTERVALの定義と一致している必要があります。Pは必須であり、この値に空白は使用できません。Tを指定した場合は、hours、minutesまたはsecondsを少なくとも1つ指定する必要があります。hoursは24時間に基づいています。

    たとえば、P100DT05Hは100日と5時間を示しています。P1Y2Mは、1年と2か月を示しています。P1M1DT5H30M30Sは、P1M (1か月を示している)と同等です。

    ISO 8601の時間間隔文字列の構文:


    iso_8601_time_interval_string.pngの説明が続きます
    図iso_8601_time_interval_string.pngの説明

    strideには正の値のみを使用してください。(OracleのINTERVALおよびISO 8601の時間間隔文字列は正または負にできます。)

    datetimeまたはoriginがEPOCH TIME、BINARY_FLOATまたはBINARY_DOUBLEである場合、strideにYEARやMONTHを含めることはできません。これは、monthが可変であり、28、29、30または31のいずれかになる可能性があるためです。

  • 4番目の引数はオプションであり、時間バケットの開始値または終了値が返されるようにするかどうかを指定します。時間バケットの開始値を返すにはSTARTを、終了値を返すにはENDを指定します。この値では大文字と小文字が区別されません。デフォルト値はSTARTです。

  • 5番目の引数はオプションであり、バケット(ストライド)の決定方法を制御します。

    ON OVERFLOW ROUND (デフォルト): バケットは、対応する月のoriginと同じ日に切り出されます。その日がない月の場合、バケットは、月の最終日に丸められます。

    ON OVERFLOW ERROR: バケットは、対応する月のoriginと同じ日に切り出されます。その日がない月の場合、エラーになります。

    LAST DAY OF MONTH: originが月の最終日で、strideにMONTHまたはYEAR(あるいはその両方)のみが含まれている場合、バケットは対応する月の最終日に切り出されます。

    たとえば、originが'1991-11-30'であり、strideが'P1M'である場合は:

    • ON OVERFLOW ROUNDの場合、各バケットの開始は次のようになります:

      ..., 1991-11-30, 1991-12-30, 1992-01-30, 1992-02-29, 1992-03-30, 1992-04-30,...

    • For ON OVERFLOW ERRORの場合、各バケットの開始は次のようになります:

      ..., 1991-11-30, 1991-12-30, 1992-01-30, error (or 1992-02-30), 1992-03-30, 1992-04-30,...

    • LAST DAY OF MONTHの場合、各バケットの開始は次のようになります:

      ..., 1991-11-30, 1991-12-31, 1992-01-31, 1992-02-29, 1992-03-31, 1992-04-30, ...

ルール

  • 各バケットの終了値は、次のバケットの開始値と同じです。たとえば、バケットが2年で、スライスの開始値が2000-01-01である場合、バケットの終了値は、2001-12-31ではなく2002-01- 01になります。つまり、バケットには、開始値以降と終了値以前(終了値と等しくない)のdatetimeが含まれます。

  • 一般に、バケットのSTARTは、常にバケットのEND以前です。ただし、バケットの、有効時間範囲の両側で、STARTがENDと等しくなる可能性はあります。

  • originとdatetimeは、有効範囲内であるかぎり正にも負にもできます。originまたはdatetimeが有効範囲外の場合、あるいは戻り値が有効範囲外の場合、エラーが発生します。

  • 入力値の型がTIMESTAMP WITH TIME ZONEまたはTIMESTAMP WITH LOCAL TIME ZONEである場合は、時間バケットが夏時間の境界をまたいでいることがあります。この場合、その時間バケットの期間は、他の時間バケットと同じになります。

  • originとdatetimeがTIMESTAMP WITH TIME ZONEまたはTIMESTAMP WITH LOCAL TIME ZONEである場合、すべての算術計算はUTC時間に基づきます。

例

次の例では、NLS_DATE_FORMATでYYYY-MM-DDを使用します。ALTER SESSIONを使用して日付書式を設定します。 

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

例1

SELECT TIME_BUCKET (DATE ‘2022-06-29’, INTERVAL ‘5’ YEAR, DATE ‘2000-01-01’, START);

結果は次のとおりです。

2020-01-01

2022-06-29を含む5年の時間バケットは、2020-01-01(開始)から2025- 01-01(終了)までです。4番目の引数STARTが使用されているため、時間バケットの開始値2020-01-01が返されます。

例2

次の2つの問合せは同等です:

SELECT TIME_BUCKET ( DATE ‘-2022-06-29’, ‘P5M’, DATE ‘-2022-01-01’, END );

または:

SELECT TIME_BUCKET ( DATE ‘-2022-06-29’, INTERVAL ‘5’ MONTH, DATE ‘-2022-01-01’, END);

結果は次のとおりです。

-2022-11-01

-2022-06-29を含む5か月の時間バケットは、2022-06-01(開始)から-2022- 11-01(終了)までです。4番目の引数ENDが使用されているため、時間バケットの終了値2022-11-01が返されます。

例3

SELECT TIME_BUCKET ( DATE ‘2005-03-10’, 'P1Y', DATE ‘2004-02-29’ ON OVERFLOW ERROR );

結果は次のとおりです。

 ORA-01839: date not valid for month specified

'2005-03-10'を含む1年の時間バケットは、エラー('2005-02-29') (開始)からエラー('2006-02-29') (終了)までです。デフォルトの4番目の引数STARTが使用されているため、時間バケットの開始(エラー)が返されます。

例4
SELECT TIME_BUCKET ( DATE ‘2005-03-10’, 'P1Y', DATE ‘2004-02-29’ ON OVERFLOW ROUND );

結果は次のとおりです。

2005-02-28

'2005-03-10'を含む1年の時間バケットは、2月29日が2月28日に丸められるため、'2005-02-28' (開始)から'2006- 02-28' (終了)までです。デフォルトの4番目の引数STARTが使用されているため、時間バケットの開始('2005-02-28')が返されます。

例5

SELECT TIME_BUCKET ( DATE ‘2004-04-02’, ‘P1Y’, DATE ‘2003-02-28’ LAST DAY OF MONTH );

結果は次のとおりです。

2004-02-29

'2003-02-28'を含む1年の時間バケットは、'2004-02-28'がその月の最終日('2004-02-29')に丸められるため、'2004-02-29' (開始)から'2005- 02-28' (終了)までです。デフォルトの4番目の引数STARTが使用されているため、時間バケットの開始値'2004-02- 29'が返されます。