はじめに
Oracle AI Database 26ai (23.26.1) で新たに SQL関数 datediff が追加されました。この関数は2つの日時値の間に時間単位の境界が何回通過されたかを数えます。
経過時間ではなく境界通過回数を返す点がポイントです。たとえば「2025年12月31日 23:59:59」から「2026年1月1日 00:00:00」のわずか1秒間でも、年・月・日の境界をそれぞれ1回通過するため、datediff は year・month・day それぞれで 1 を返します。
このため datediff は年齢計算など完全な単位数を必要とする計算には向きません。一方で、「今週中に何件の注文があったか」「この2つの日は同じ月か」など、カレンダー境界の通過有無を判定する用途に適しています。
対応している時間単位は years から nanoseconds まで幅広く、週（week）・四半期（quarter）の境界判定にも使えます。さらにオプションの第4引数で週の開始曜日や年・四半期の開始月をカスタマイズできるため、ロケールや業務カレンダーに合わせた柔軟な運用が可能です。
以下のような用途で利用できます。
- 2つの日時が同じ年・同じ月・同じ週に属するかを1クエリで一括判定する
- 週の開始曜日をセッションの NLS 設定または第4引数で明示的に指定する
- 会計年度（例：2月始まり）に合わせた年・四半期境界での集計を行う
- タイムゾーンをまたいだ日時を UTC 基準で比較する
関数の動作を確認してみました。
事前準備
前提条件
- Oracle AI Database バージョン: 23.26.1 以上
- 必要な権限: データベースへの接続権限（特別な権限は不要）
手順
Step 1: datediff の基本動作を確認（年境界）
まず、datediff 関数の基本構文を確認します。
datediff ( <unit>, <start date>, <end date> )
datediff ( <unit>, <start date>, <end date>, <start of period> )
-
unit: 時間単位（year / quarter / month / week / day / hour / minute / second / nanosecond） -
start date: 開始日時 -
end date: 終了日時 -
start of period（省略可）: 週・年・四半期の境界起点を指定する第4引数
結果は終了日時が開始日時より後であれば正の値、前であれば負の値、境界を通過しなければ 0 を返します。
以下のクエリで、固定の開始日（2025年12月31日 23:59:59）から3つの年初日時への年境界通過回数を確認します。
with dates ( year_start ) as (
values ( date'2024-01-01' ), ( date'2025-01-01' ), ( date'2026-01-01' )
)
select year_start,
datediff (
year,
to_date ( '2025-DEC-31 23:59:59', 'YYYY-MON-DD HH24:MI:SS' ),
year_start
) years
from dates;
結果：
YEAR_STAR YEARS
--------- ----------
01-JAN-24 -1
01-JAN-25 0
01-JAN-26 1
2024年1月1日は開始より前にあるため -1、同じ年内の2025年1月1日は 0、2026年1月1日は年境界を1回通過するため 1 となります。
Step 2: 年・月・日の境界を同時に比較
わずか1秒（2025-12-31 23:59:59 → 2026-01-01 00:00:00）の移動でも、年・月・日それぞれの境界を通過することを確認します。
with dates ( year_end, year_start ) as (
values (
to_date ( '2025-DEC-31 23:59:59', 'YYYY-MON-DD HH24:MI:SS' ),
to_date ( '2026-JAN-01 00:00:00', 'YYYY-MON-DD HH24:MI:SS' )
)
)
select datediff ( year, year_end, year_start ) years,
datediff ( month, year_end, year_start ) months,
datediff ( day, year_end, year_start ) days
from dates;
結果：
YEARS MONTHS DAYS
---------- ---------- ----------
1 1 1
わずか1秒でも year・month・day それぞれ1回境界を通過するため、すべて 1 となります。
Step 3: 週境界と NLS_TERRITORY の影響を確認
週（week）の境界は例外的で、NLS の地域設定（nls_territory）によって週の開始曜日が変わります。同じ日付でも結果が異なる場合があります。
- 米国（America）: 日曜日が週の開始
- 英国（United Kingdom）: 月曜日が週の開始（ISO 標準）
2026年1月4日（日曜日）→ 1月5日（月曜日）の遷移を America と United Kingdom で比較します。to_char ( dt, 'd' ) は現在の NLS 設定での曜日番号を返します。
-- America 設定（日曜始まり）
alter session set nls_territory = America;
with dates ( year_end, year_start ) as (
values (
to_date ( '2026-JAN-04 23:59:59', 'YYYY-MON-DD HH24:MI:SS' ),
to_date ( '2026-JAN-05 00:00:00', 'YYYY-MON-DD HH24:MI:SS' )
)
)
select datediff ( week, year_end, year_start ) weeks,
to_char ( year_end, 'd' ) start_day_number
from dates;
結果（America）：
WEEKS S
---------- -
0 1
-- United Kingdom 設定（月曜始まり）
alter session set nls_territory = 'United Kingdom';
with dates ( year_end, year_start ) as (
values (
to_date ( '2026-JAN-04 23:59:59', 'YYYY-MON-DD HH24:MI:SS' ),
to_date ( '2026-JAN-05 00:00:00', 'YYYY-MON-DD HH24:MI:SS' )
)
)
select datediff ( week, year_end, year_start ) weeks,
to_char ( year_end, 'd' ) start_day_number
from dates;
結果（United Kingdom）：
WEEKS S
---------- -
1 7
America 設定では日曜日（D=1）から月曜日への移動は同じ週内のため 0、United Kingdom 設定では日曜日（D=7）から月曜日への移動は新しい週の開始のため 1 になります。
Step 4: 第4引数で週の開始曜日を明示指定
NLS 設定に依存せず、クエリ内で週の開始曜日を明示指定するには datediff の第4引数を使います。
週の第4引数の有効値：
| 値 | 意味 |
|---|---|
| 0 | デフォルト（NLS 設定に従う） |
| 1 | 月曜日始まり |
| 2 | 火曜日始まり |
| 3 | 水曜日始まり |
| 4 | 木曜日始まり |
| 5 | 金曜日始まり |
| 6 | 土曜日始まり |
| 7 | 日曜日始まり |
週の開始を木曜日（4）に設定すると、2025年12月31日（水曜日）→ 2026年1月1日（木曜日）の移動が木-木の境界通過となるため 1 を返します。
with dates ( year_end, year_start ) as (
values (
to_date ( '2025-DEC-31 23:59:59', 'YYYY-MON-DD HH24:MI:SS' ),
to_date ( '2026-JAN-01 00:00:00', 'YYYY-MON-DD HH24:MI:SS' )
)
)
select datediff ( week, year_end, year_start ) weeks_nls_start,
datediff ( week, year_end, year_start, 4 ) weeks_starting_thurs
from dates;
結果：
WEEKS_NLS_START WEEKS_STARTING_THURS
------------- --------------------
0 1
Step 5: 第4引数で年・四半期の開始月をカスタマイズ
年（year）と四半期（quarter）でも第4引数を使って境界の起点月を変更できます。有効値は 1〜12（1=1月、2=2月、…、12=12月）です。
四半期の場合、差が3の値は同じ結果になります（例: 2, 5, 8, 11 はすべて「2月・5月・8月・11月」区切りの四半期）。
年・四半期の開始を2月（2）に設定すると、2025年12月31日 → 2026年1月1日の移動は年境界・四半期境界のどちらも通過しないため 0 を返します。
with dates ( year_end, year_start ) as (
values (
to_date ( '2025-DEC-31 23:59:59', 'YYYY-MON-DD HH24:MI:SS' ),
to_date ( '2026-JAN-01 00:00:00', 'YYYY-MON-DD HH24:MI:SS' )
)
)
select datediff ( year, year_end, year_start, 2 ) years,
datediff ( quarter, year_end, year_start, 2 ) quarters
from dates;
結果：
YEARS QUARTERS
---------- ----------
0 0
制限事項
| 制限 | 詳細 |
|---|---|
| 最小バージョン | Oracle AI Database 23.26.1 以上が必要です |
| 第4引数の対応単位 |
week・year・quarter のみ有効。それ以外の単位で第4引数を指定するとエラーになります |
| インデックス利用の制限 |
datediff 式インデックスはクエリの呼び出しと完全一致する場合のみ利用可能。条件の柔軟性が下がります |
おわりに
今回の検証で確認できたポイントは以下の通りです。
-
datediffは経過時間ではなく時間単位の境界通過回数を返す関数であり、同じ1秒間でも年・月・日の複数単位で1を返すことがある - 第4引数を使うことで週の開始曜日・年や四半期の開始月をクエリ単位で明示指定でき、NLS 設定への依存を排除できる
この関数は「この2つのレコードは同じ週か？」「前月から今月に移ったか？」といったカレンダー境界の判定が必要な業務ロジックに最適です。会計カレンダーや週のロジックを持つ集計クエリをシンプルに記述したいエンジニアにとって、特に価値ある機能です。