Oracle´s Problem mit INTERVAL-Berechnungen

Möchte man zu einem Datum ein oder mehrere Monate addieren, so gibt es grundsätzlich zwei Möglichkeiten. Man nutzt die SQL Funktion ADD_MONTHS

SELECT ADD_MONTHS(sysdate,1) FROM dual;

oder den Datentyp INTERVAL

SELECT sysdate + INTERVAL '1' MONTH FROM dual;

Soweit die Theorie. Leider gibt es in der Praxis ein kleines Problem. Schauen wir uns an, was passiert, wenn wir zum 31. Jänner 2023 ein Monat addieren:

SELECT ADD_MONTHS(TO_DATE('31.01.2023','DD.MM.YYYY'),1) FROM dual;

ADD_MONTH
---------
28-FEB-23

Nachdem der 31. Jänner der letzte Tag im Monat ist, liefert ADD_MONTH ebenfalls den letzten Tag des Monats. Überprüfen wir das mit einem Schaltjahr:

SELECT ADD_MONTHS(TO_DATE('31.01.2024','DD.MM.YYYY'),1) FROM dual;

ADD_MONTH
---------
29-FEB-24

Und wieder kommt der letzte Tag des Monats. Soweit so gut.

Wie verhält sich das Ganze, wenn man mit dem INTERVAL rechnet?

Probieren wir zum 15. Jänner 2023 ein Interval von einem Monat zu addieren:

SELECT TO_DATE('15.01.2023','DD.MM.YYYY') + INTERVAL '1' MONTH FROM dual;

TO_DATE('
---------
15-FEB-23

Soweit so gut, wir bekommen den 15. Feb 2023. Versuchen wir es jetzt ebenfalls mit dem 31. Jänner 2023.

SELECT TO_DATE('31.01.2023','DD.MM.YYYY') + INTERVAL '1' MONTH FROM dual;

ERROR at line 1:
ORA-01839: date not valid for month specified

Warum bekommt man jetzt einen Fehler?

Das liegt an der Implementierung des Datentyps INTERVAL. Wenn man ein Monat addiert, wird im Datum nur eine Eins zum Monat addiert – und den 31. Februar gibt es nicht. Schauen wir uns an, was mit dem 29. Jänner 2023 und 2024 passiert.

SELECT TO_DATE('29.01.2023','DD.MM.YYYY') + INTERVAL '1' MONTH FROM dual;

ERROR at line 1:
ORA-01839: date not valid for month specified

SELECT TO_DATE('29.01.2024','DD.MM.YYYY') + INTERVAL '1' MONTH FROM dual;

TO_DATE('
---------
29-FEB-24

Was lernt man daraus?

Der INTERVAL Datentyp dient zum Ermitteln bzw. Abspeichern von großen Zeiträumen. Er ist NICHT für Datumsarithmetik gedacht. Wie verhält es sich mit INTERVAL für Zeitarithmetik, gibt es hier die gleichen Probleme? Addieren wir eine Sekunde zu 11:59:59 und schauen was herauskommt:

alter session set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';

SELECT TO_DATE('15.01.2023 11:59:59','DD.MM.YYYY HH24:MI:SS') + INTERVAL '1' SECOND FROM dual;

TO_DATE('15.01.2023
-------------------
15.01.2023 12:00:00

Es kommt das korrekte Ergebnis heraus. Anscheinend wird der INTERVAL Datentyp (Seconds to Days) korrekt umgesetzt. Prüfen wir das, indem wir zum 31. Jan 2023 30 Tage addieren.

SELECT TO_DATE('31.01.2023','DD.MM.YYYY') + INTERVAL '30' DAY FROM dual;

TO_DATE('3
----------
02.03.2023

Hier kommt ebenfalls das korrekte Ergebnis.

Zusammenfassung

Oracle kennt zwei Datentype INTERVAL:

INTERVAL DAY TO SECOND … kurze Zeiträume, nutzbar für Zeitarithmetik
INTERVAL YEAR TO MONTH … lange Zeiträume, nur bedingt für Datumsarithmetik

Leider wurde bei der Implementierung ein grundsätzlicher Unterschied gemacht. Die Zeitarithmetik bei INTERVAL DAY TO SECOND wurde, so wie man es sich erwartet, umgesetzt. Bedauerlicherweise hat Oracle bei INTERVAL YEAR TO MONTH die Datumsarithmetik nicht eingebaut. Somit muss man die SQL Funktionen ADD_MONTHS und ADD_YEARS nutzen, wenn man korrekte Ergebnisse bei der Datumsberechnung benötigt.

Wie verhalten sich andere Datenbanken?

Andere Datenbanken verhalten sich beim Addieren eines Monats eher so wie erwartet:

PostgreSQL

postgres=> select date'2023-03-31' + interval '1' month;
      ?column?
---------------------
 2023-04-30 00:00:00
(1 Zeile)

postgres=> select date'2023-01-31' + interval '1' month;
      ?column?
---------------------
 2023-02-28 00:00:00
(1 Zeile)

postgres=> select date'2020-01-31' + interval '1' month;
      ?column?
---------------------
 2020-02-29 00:00:00
(1 Zeile)

MariaDB

MariaDB> select str_to_date ('31.03.2023','%d.%m.%Y') + INTERVAL '1' MONTH;
+------------------------------------------------------------+
| str_to_date ('31.03.2023','%d.%m.%Y') + INTERVAL '1' MONTH |
+------------------------------------------------------------+
| 2023-04-30                                                 |
+------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB> select str_to_date ('31.01.2023','%d.%m.%Y') + INTERVAL '1' MONTH;
+------------------------------------------------------------+
| str_to_date ('31.01.2023','%d.%m.%Y') + INTERVAL '1' MONTH |
+------------------------------------------------------------+
| 2023-02-28                                                 |
+------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB> select str_to_date ('31.01.2024','%d.%m.%Y') + INTERVAL '1' MONTH;
+------------------------------------------------------------+
| str_to_date ('31.01.2024','%d.%m.%Y') + INTERVAL '1' MONTH |
+------------------------------------------------------------+
| 2024-02-29                                                 |
+------------------------------------------------------------+
1 row in set (0.001 sec)