DATEDIFF

構文


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

目的

DATEDIFFは、年、月、日、週およびその他の時間単位での2つの日時値の差を計算します。

TIMESTAMPDIFFは、DATEDIFFのシノニムです。

セマンティクス

このファンクションは、time_unit、start_datetimeおよびend_datetimeという3つの引数を取り、開始時刻と終了時刻の間の時間単位の境界数を表す数値を戻します。戻り値は、終了日時が開始日時より後の場合は正の整数で、終了日時が開始日時より前の場合は負の整数です。

最初の引数time_unitは、YEAR、MONTH、DAY、WEEK、QUARTER、HOUR、MINUTE、SECOND、MILLISECOND、MICROSECONDおよびNANOSECONDのいずれかです。

2番目と3番目の引数start_datetime、end_datetimeは、データ型DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONEの値に評価される式である必要があります。

オプションの4番目の引数start_of_periodは、時間単位がWEEK、QUARTERまたはYEARの場合に指定でき、次のことを意味します:

  • 時間単位がWEEKの場合、start_of_periodは"週の開始"を意味します。指定できる値は、0、1、2、3、4、5、6、および7です。デフォルト値は0です。つまり、週の最初の日はNLS_TERRITORYパラメータによって決定されます。値1は月曜日、2は火曜日、3は水曜日、4は木曜日、5は金曜日、6は土曜日、7は日曜日を意味します。

  • 時間単位がQUARTERの場合、start_of_periodは"四半期の開始"を意味します。指定できる値は、1、2、3、4、5、6、7、8、9、10、11および12です。デフォルト値は1です。値1、4、7および10は、四半期が1月、4月、7月および10月の最初の日に開始することを意味します。4つの値はすべて同等です。値2、5、8および11は、四半期が2月、5月、8月および11月の最初の日に開始することを意味します。これらの4つの値も同等です。値3、6、9および12は、四半期が3月、6月、9月および12月の最初の日に開始することを意味します。繰り返しますが、この4つの値は同等です。

  • 時間単位がYEARの場合、start_of_periodは"開始月の年"を意味します。指定できる値は、1、2、3、4、5、6、7、8、9、10、11および12です。デフォルト値は1です。値1は、年が1月の最初の日に開始することを意味し、2は2月の最初の日に開始することを意味し、12は、年が12月の最初の日に開始することを意味します。

四半期の開始および年の開始のデフォルト以外の値は、会計年度が1月1日に開始されない会計四半期および年に関する計算に役立ちます。

DATEDIFFでは、非グレゴリオ暦はサポートされません。対応するNLS_CALENDAR設定の値がGREGORIANでない場合でも、計算は常にグレゴリオ暦で実行されます。

Oracle Locale Builderを使用して、NLS_TERRITORYの特定の値についてカレンダ週の最初の日を確認します。

時間単位がWEEK、QUARTERまたはYEARでない場合にstart_of_periodを指定すると、構文エラーになります。

時間単位の境界

次の日時値で始まる時間単位。

時間単位 単位の最初の日時

YEAR

start_of_periodパラメータによって決定される月の最初の日の各午前0時、またはパラメータが指定されていない場合は1月

QUARTER

start_of_periodパラメータによって決定される月の最初の日の各午前0時、またはパラメータが指定されていない場合は1月、4月、7月または10月

MONTH

各月の最初の日の午前0時

WEEK

start_of_periodパラメータ(前述の項を参照)、またはパラメータが指定されていない場合はNLS_TERRITORYの値によって決定される週の最初の日の各午前0時

DAY

午前0時ごと

HOUR

分、秒および小数秒がゼロである各値

MINUTE

秒および小数秒がゼロである各日時値

SECOND

小数秒がゼロである各値

MILLISECOND

小数点以下第4位から第9位がゼロである各値

MICROSECOND

小数点以下第7位から第9位がゼロである各値

NANOSECOND

各日時値

午前0時は、00:00:00.000000000です。時間単位は、前述の項で定義した値のいずれかで始まり、この単位の次の適切な開始値より1ナノ秒前の値で終わります。

開始日時と終了日時に指定された精度で小数秒がない場合(たとえば、TIMESTAMP(6)にナノ秒がない場合)、計算では欠落部分が0であるとみなされます。

2つの日時値の間に渡す時間は、開始値が単位の開始日時より前で、終了値が単位の開始日時より後である場合、単位の境界を超えます。

UTCへの開始日時と終了日時の変換

データ型強制後のstart_datetimeとend_datetimeの両方がDATEまたはTIMESTAMPデータ型の場合、DATEDIFFはタイムゾーンを考慮せずに計算を実行します。

それ以外の場合、計算を実行する前に、start_datetimeとend_datetimeの両方がUTCタイムゾーンに変換されます。

たとえば、次の問合せでは、start_datetimeは2024/04/08 06:00:00 AM UTC時間です。end_datetimeは、2024/04/08 07:00:00 AM UTC時間です。start_datetimeとend_datetimeは太平洋時間で異なる日ですが、UTC時間に変換された場合、同じ日に属します。 

SELECT DATEDIFF (DAY, TIMESTAMP '2024-04-07 23:00:00 -07:00', TIMESTAMP '2024-04-08 00:00:00 -07:00');

結果: 0。次の例では、start_datetimeおよびend_datetimeはPDT時間の同じ日に属しているように見えますが、UTC時間の異なる日に属しています。 

SELECT DATEDIFF (DAY, TIMESTAMP '2023-04-08 16:00:00 -07:00', TIMESTAMP '2023-04-08 17:00:00 -07:00');

結果: 1。前述の問合せは次と同等です: 

SELECT DATEDIFF (DAY, TIMESTAMP '2023-04-08 23:00:00 +00:00', TIMESTAMP '2023-04-09 00:00:00 +00:00');
結果: 1

例

例1: 週の開始

start_of_periodが7の場合、日曜日は週の最初の日です。次のstart_datetimeとend_datetimeは隣接しており、時間が1ナノ秒だけ異なりますが、前者は土曜日で、後者は日曜日です。したがって、この時間は、start_datetimeとend_datetimeの間の週境界を超えるため、問合せは1を戻します。 

SELECT DATEDIFF (WEEK, TIMESTAMP '2005-12-31 23:59:59.9999999', TIMESTAMP '2006-01-01 00:00:00.0000000', 7);

結果: 1

例2: 四半期の開始

カレンダ年は4つの四半期で構成されます。デフォルトでは、第1四半期は1月1日から3月31日まで、第2四半期は4月1日から6月30日まで、第3四半期は7月1日から9月30日まで、第4四半期は10月1日から12月31日までです。次の2つの例では、結果はこれらのデフォルトの四半期境界に基づきます。

SELECT DATEDIFF (QUARTER, DATE '2023-03-31', DATE '2023-04-01');

結果: 1

SELECT DATEDIFF (QUARTER, DATE '2023-04-01', DATE '2023-06-30');

結果: 0

例3: 年の開始

カレンダ年は常に1月1日から始まります。ユーザーは、会計四半期と同様に、時間単位がYEARの場合、start_of_periodパラメータを使用して会計年度の開始を指定できます。

次の例では、会計年度が6月1日から始まる2つのタイムスタンプ間の会計年度の境界数を計算します。 

SELECT DATEDIFF (YEAR, TIMESTAMP '2005-05-31 23:59:59.9999999', TIMESTAMP '2006-06-01 00:00:00.0000000', 6);

結果: 2

例4: MILLISECOND/MICROSECOND/NANOSECOND

時間単位: SECOND

時間単位がSECONDの場合、'1996-11-09 09:26:50'と'1996-11-09 10:26:51'の差のみが計算されます

SELECT DATEDIFF (SECOND, TIMESTAMP '1996-11-09 09:26:50.13', TIMESTAMP '1996-11-09 10:26:50.12');

結果: 3601

時間単位: MILLISECOND

時間単位がMILLISECONDの場合、小数秒の最初の3桁が保持されます。 

SELECT DATEDIFF (MILLISECOND, TIMESTAMP '1996-11-09 09:26:50.13', TIMESTAMP '1996-11-09 10:26:51.12');

結果: 3600990

時間単位: MICROSECOND

時間単位がMICROSECONDの場合、小数秒の最初の6桁が保持されます。 

SELECT DATEDIFF (MICROSECOND, TIMESTAMP '1996-11-09 10:26:51.13', TIMESTAMP '1996-11-09 10:26:51.12');

結果: -10000

時間単位: NANOSECOND

時間単位がNANOSECONDの場合、小数秒の9桁がすべて保持されます。 

SELECT DATEDIFF (NANOSECOND, TIMESTAMP '1996-11-09 10:26:51.13', TIMESTAMP '1996-11-09 10:26:51.12');

結果: -10000000