SQL で、たとえば半年後の日付を計算する場合、みなさんはどのように計算するでしょうか？ SQL 標準に従った書き方にすれば、より多くの環境で動作するクエリーになることが期待できます。この記事では datetime に interval を加減して日付を計算する方法をご紹介します。執筆にあたり SQL:2011 のドラフト版を参照しました。
構文
<datetime> +/- INTERVAL '<数値>' <単位> [* <かける数>]
datetime は、DATE、TIME、TIMESTAMP のいずれかのデータ型の値です。
INTERVAL '<数値>' <単位> は interval リテラルです。単位には YEAR, MONTH, YEAR TO MONTH, DAY を指定できます。
* を使って interval を乗算することもできます。
例
半年後を求める
DATE '2023-01-23' + INTERVAL '6' MONTH
100 日前を求める
DATE '2023-01-23' - INTERVAL '1' DAY * 100
1 年 6 か月後を求める
DATE '2023-01-23' + INTERVAL '1-6' YEAR TO MONTH
単位に YEAR TO MONTH を使う場合は、年の数値に続けて - を書き、続けて月の数値を書きます。
10 か月と 10 日後を求める
DATE '2023-01-23' + INTERVAL '10' MONTH + INTERVAL '10' DAY
12 か月後を求める
DATE '2023-01-23' + INTERVAL '1' MONTH * 12
INTERVAL '1' MONTH * 12 が計算されて INTERVAL '1' YEAR になり、1年後の日付が返ります。
12 か月後というリテラルを用いる
DATE '2023-01-23' + INTERVAL '12' MONTH
MONTH の妥当な値は 0 〜 11 ですのでこれは誤りです。「the natural rules
for intervals according to the Gregorian calendar」(グレゴリオ暦に基づく interval への自然な制約)、つまり「there are 60 seconds in a minute, 60 minutes in an hour, 24 hours in a day, and 12 months in a year」(1 分は 60 秒、1 時間は 60 分、1 日は 24 時間、1 年は 12 か月) に制約されるためです。
精度の不足
DATE '2000-02-29' + INTERVAL '400' YEAR
構文のところでご紹介しませんでしたが、単位には (3) のように精度を指定できます。指定しない場合は暗黙に 2 となり、400 のような 3 桁以上の数値はオーバーフローしてしまいます。
INTERVAL の数値を単一引用符で囲わない
INTERVAL 6 MONTH
MySQL の interval リテラルがこの形ですが、SQL 標準のリテラルではありません。Interval リテラルとして INTERVAL '12 12:34:56.789' DAY TO SECOND のような複雑な形式でも記述できるため、数値部分は単一引用符で囲うことになっています。
PostgreSQL の interval リテラルを用いる
DATE '2023-01-31' + INTERVAL '2 months'
これも SQL 標準のリテラルではありませんので、使わないようにしましょう。
存在しない日付を求める
DATE '2023-01-31' + INTERVAL '1' MONTH
2 月 31 日は存在しないので「data exception — datetime field overflow」例外条件が引き起こされます。
Oracle の ADD_MONTHS 関数との違い
Oracle の ADD_MONTHS 関数は、月末日に月を足すと、月末日が返されます。たとえば ADD_MONTHS(DATE '2023-02-28', 1) の結果は DATE '2023-03-31' になります。
同じことを ADD_MONTHS を使わずに実現するなら以下のようになります。
DATE '2023-02-28' + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY
他にも、計算結果が存在しない日になる場合も最終日が返るといった違いがあります。
対応状況
Interval 型は SQL-92 からありますが、実際のところどの程度動作するのでしょうか？ いくつかの実装で試してみました。
| SQL | 期待結果 | Oracle 19c | PostgreSQL 15 | MySQL 5.7 | H2 2.1.214 |
|---|---|---|---|---|---|
| DATE '2023-01-23' + INTERVAL '1' MONTH | DATE '2023-02-23' | ○ | ○ | ○ | ○ |
| DATE '2023-01-23' + INTERVAL '1-6' YEAR TO MONTH | DATE '2024-07-23' | ○ | ○ | × (ERROR 1064 (42000)) | ○ |
| DATE '2023-01-23' - INTERVAL '1' DAY * 100 | DATE '2022-10-15' | ○ | ○ | × (20230122001) | ○ |
| DATE '2023-01-23' - INTERVAL '100' DAY (3) | DATE '2022-10-15' | ○ | × (ERROR: syntax error at or near "(") | × (ERROR 1064 (42000)) | ○ |
| DATE '2023-01-23' - INTERVAL '100' DAY | 例外 | ○ (ORA-01873: the leading precision of the interval is too small) | × (DATE '2022-10-15') | × (DATE '2022-10-15') | × (DATE '2022-10-15') |
| DATE '2023-01-23' + INTERVAL '12' MONTH | 例外2 | × (DATE '2024-01-23') | × (DATE '2024-01-23') | × (DATE '2024-01-23') | × (DATE '2024-01-23') |
| DATE '2023-01-31' + INTERVAL '1' MONTH | 例外 | ○ (ORA-01839: date not valid for month specified) | × (DATE '2023-02-28') | × (DATE '2023-02-28') | × (DATE '2023-02-28') |
DB2 11.5 は interval リテラルが使えず、SQL Server 2022、SQLite 3.41.1 は date リテラルも interval リテラルも使えませんでした。
参考
Part 2: Foundation (SQL/Foundation) のみ参照しました。
最後に
まだ使えない実装も多いですが、実装依存の関数を使うよりは幅広く対応できそうですので、実装が対応していたら標準に従った書き方をするに越したことはないと思います。
Oracle が最もきちんと標準に準拠していて、さすがだと思いました。Oracle でエラーが出るまで、私は精度が指定されていない場合は 2 とされることを見落としていました。ただ、その Oracle でさえ、構文のところでご紹介しなかった省略可能な正負の符号を追加すると動作しませんでした3。
もし誤りや、より良い書き方、補足すべきことなどがありましたら、コメントいただけると嬉しいです。
-
*のオペランドは数値だからと、1 日前の DATE が数値にキャストされ、100 倍されたようです 😅 ↩ -
標準に、例外になるという記述があったわけではありませんが、グレゴリオ暦に基づく制約が守られていないのに例外が出ないのはおかしいと感じます。SQL Test Suite にも例外を期待していると思われる箇所がありました。が、ここまで揃って例外が出ないと心配になります。 ↩
-
単一引用符の前が正しい符号の位置ですが、単一引用符の中に追加しないと動作しません。 ↩