Oracleで不具合を生む禁じ手「INTERVAL YEAR TO MONTHで日付操作」

※こちらはOracle限定の話となります。

日付操作について

SQLで日付操作する時に使用されるINTERVALがあります。
使い方としては以下のような形で使用できます。

select sysdate + INTERVAL '1' DAY from dual;

DAYの部分をYEARやMONTHにを変えることで操作することができます。

そもそもINTERVALとは何か

少し古い仕様書ですが、Oracleのリンクを記載します。

ここに書かれている通り、INTERVALとはSQLの操作ではなく期間を表す変数です。
なのでINTERVAL '1' DAYで一つの値を表します。

INTERVALは厳密にはINTERVAL YEAR TO MONTH、INTERVAL DAY TO SECONDの二つの型に分かれます。つまりX年Y月加算することはできますが、X年Y月Z日加算することは型の境界を超えるので一回で計算できません。

1年2ヶ月と3日前の日付を計算するSQL
NG:SELECT  sysdate - (INTERVAL '1-2-3' YEAR TO DAY)  AS  time_value FROM dual


OK:SELECT  sysdate - (INTERVAL '1-2' YEAR TO MONTH) - (INTERVAL '3' DAY)  AS  time_value FROM dual

発生する不具合

これがなぜ不具合を生むことになるかというと、月末の日付の調整してくれないという問題があるためです。
末日を判断してくれないことでなにが起こるかというと、存在しない日付が生成されエラーになるということです。

例)
1/31の１ヶ月後とすると2/31

ただ、なぜか　- (INTERVAL '1' DAY)はエラーなく正常な値を返してくれます。
(これを正しく返してくれるのであればX年やY月の操作でも正しく返してくれても良いのに)

SELECT  to_date('20240301','yyyymmdd') - INTERVAL '1' day AS  time_value FROM dual
→2024-02-29 00:00:00.000

SELECT  to_date('20240229','yyyymmdd') + INTERVAL '1' day AS  time_value FROM dual
→2024-03-01 00:00:00.000

エラーコード

実際に実行すると以下のエラーコードとエラーメッセージが返されます。

ORA-01839: 指定された月に対して日付が無効です
原因: 日付で指定された月単位の日付が、指定された月に対して無効でした。月単位の日付(DD)は、1から月の日数の間である必要があります。

処置: 指定した月に対する有効な月単位の日付を入力してください。

なぜ禁じ手なのか

このSQLを基本的に使用してはいけない理由は以下の通りです。

1.時と場合によって動くSQLになる

先に書いた通り末日処理がされないことで、あるときは動くがある時は動かないという問題が起きる。
2月に作成した時はうまく動いたのに他の月ではうまく動かない場合が発生し、テストで見逃されてしまう可能性が出てきます。

正常に常に動かすためには末日を考慮した処理をする必要が出てきます。
結果として、処理が複雑化してしまうという問題を生んでしまいます。

2.INTERVALにはデフォルトで制限がある

INTERVALの型に設定できるデフォルトの値の制限があります。

INTERVAL YEAR TO MONTH→99年11ヶ月が最大
INTERVAL DAY TO SECOND→99日23時間59分59秒が最大

100年後を計算することは実際の運用上ないと思いますが、100日後の計算は現実的にありうると思われます。
登録日のX日後までに何かしないというような期限を持たせる、期限までの日付は時と場合によって変わるような登録を行うときに何も指定しなければ99日以降の計算をSQLで行うとエラーが発生します。

対処方法

1.Oracleのadd_monthsなどの関数を使用して計算する

上記関数を使用すると1/31の1月後の計算でも計算のエラーが出なくなります。

SELECT ADD_MONTHS(to_date('20240130','yyyymmdd'),1) FROM dual
→2024-02-29 00:00:00.000

存在しない日付をした場合は、計算された存在しない日付より以前に存在する最も近い日付が返却されます
2024年の1/31の1ヶ月後も1/30の1ヶ月後も2/29になります。

ただこちらにも問題があり、末日の１ヶ月後とすると次の月の末日が返されるという動きになります。
そのため2024年2月28日にADD_MONTHSで一月後とすると2024年3月28日となりますが、2023年2月28日にADD_MONTHSで一月後とすると2023年3月31日となります。

2.日付単位で加算する

1ヶ月=31日もしくは30日(1年は365日)で仕様の中で定義して計算する。さらに計算するもとのデータがDate型の場合は数値計算で日付の計算ができます。

1日後の計算
SELECT  to_date('20240229','yyyymmdd') +1 AS  time_value FROM dual

もしくはSQLを見たときにすぐにどのような操作がされているか分かるようにするため、INTERVAL '1' DAYを使用する。
※DAY(3)などと桁数を指定することができるので、デフォルトの2桁を超えた3桁の1年を365日としても実行することはできる

SELECT  INTERVAL '365' day(3) AS  time_value FROM dual

3.プログラム側で指定する

1や2は日付操作の結果がデータベースに依存するということで、SQLを実行するプログラム側で日付をそのものを計算してしまうことで、DBの依存から切り離すのも手だと思われます。
仮にプログラム側で定義できれば、データベースを変更する際にSQLのエラーに悩まされることがなくなります。

まとめ

INTERVALでの日時操作に関するエラーについてまとめました。
最新のOracle環境で確かめられて言いませんが、同じようなエラーのことを書いている記事があったのでこの仕様は現在でも有効かと思います。
個人的には、対処方法の2もしくは3での対応をするのが現実的に不具合になりにくいのではと思いました。