SQL の日付計算に ADD_MONTHS や DATE_ADD を使うのは避けよう

SQL で、たとえば半年後の日付を計算する場合、みなさんはどのように計算するでしょうか？ SQL 標準に従った書き方にすれば、より多くの環境で動作するクエリーになることが期待できます。この記事では datetime に interval を加減して日付を計算する方法をご紹介します。執筆にあたり SQL:2011 のドラフト版を参照しました。


## 構文

```
<datetime> +/- INTERVAL '<数値>' <単位> [* <かける数>]
```

datetime は、DATE、TIME、TIMESTAMP のいずれかのデータ型の値です。

`INTERVAL '<数値>' <単位>` は interval リテラルです。単位には YEAR, MONTH, YEAR TO MONTH, DAY を指定できます。

`*` を使って interval を乗算することもできます。


## 例

:::note info
半年後を求める
```sql
DATE '2023-01-23' + INTERVAL '6' MONTH
```
:::

:::note info
100 日前を求める
```sql
DATE '2023-01-23' - INTERVAL '1' DAY * 100
```
:::

:::note info
1 年 6 か月後を求める
```sql
DATE '2023-01-23' + INTERVAL '1-6' YEAR TO MONTH
```
単位に YEAR TO MONTH を使う場合は、年の数値に続けて `-` を書き、続けて月の数値を書きます。
:::

:::note info
10 か月と 10 日後を求める
```sql
DATE '2023-01-23' + INTERVAL '10' MONTH + INTERVAL '10' DAY
```
:::

:::note info
12 か月後を求める
```sql
DATE '2023-01-23' + INTERVAL '1' MONTH * 12
```
`INTERVAL '1' MONTH * 12` が計算されて `INTERVAL '1' YEAR` になり、1年後の日付が返ります。
:::

:::note alert
12 か月後というリテラルを用いる
```sql
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 か月) に制約されるためです。
:::

:::note alert
精度の不足
```sql
DATE '2000-02-29' + INTERVAL '400' YEAR
```
構文のところでご紹介しませんでしたが、単位には `(3)` のように精度を指定できます。指定しない場合は暗黙に 2 となり、400 のような 3 桁以上の数値はオーバーフローしてしまいます。
:::

:::note alert
INTERVAL の数値を単一引用符で囲わない
```sql
INTERVAL 6 MONTH
```
[MySQL の interval リテラル](https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-intervals)がこの形ですが、SQL 標準のリテラルではありません。Interval リテラルとして `INTERVAL '12 12:34:56.789' DAY TO SECOND` のような複雑な形式でも記述できるため、数値部分は単一引用符で囲うことになっています。
:::

:::note alert
PostgreSQL の interval リテラルを用いる
```sql
DATE '2023-01-31' + INTERVAL '2 months'
```
これも SQL 標準のリテラルではありませんので、使わないようにしましょう。
:::

:::note alert
存在しない日付を求める
```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'` になります。

https://docs.oracle.com/cd/F19136_01/sqlrf/ADD_MONTHS.html

同じことを ADD_MONTHS を使わずに実現するなら以下のようになります。

```sql
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' | ○ | ○ | × (2023012200[^casted]) | ○ |
| 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 | 例外[^exception] | × (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 リテラルも使えませんでした。


## 参考

http://www.wiscorp.com/sql20nn.zip

Part 2: Foundation (SQL/Foundation) のみ参照しました。


## 最後に

まだ使えない実装も多いですが、実装依存の関数を使うよりは幅広く対応できそうですので、実装が対応していたら標準に従った書き方をするに越したことはないと思います。

Oracle が最もきちんと標準に準拠していて、さすがだと思いました。Oracle でエラーが出るまで、私は精度が指定されていない場合は 2 とされることを見落としていました。ただ、その Oracle でさえ、構文のところでご紹介しなかった省略可能な正負の符号を追加すると動作しませんでした[^sign]。

もし誤りや、より良い書き方、補足すべきことなどがありましたら、コメントいただけると嬉しいです。

[^casted]: `*` のオペランドは数値だからと、1 日前の DATE が数値にキャストされ、100 倍されたようです 😅
[^exception]: 標準に、例外になるという記述があったわけではありませんが、グレゴリオ暦に基づく制約が守られていないのに例外が出ないのはおかしいと感じます。[SQL Test Suite](https://www.itl.nist.gov/div897/ctg/sql_form.htm) にも例外を期待していると思われる箇所がありました。が、ここまで揃って例外が出ないと心配になります。
[^sign]: 単一引用符の前が正しい符号の位置ですが、単一引用符の中に追加しないと動作しません。