Unser täglich MySQL-Rant gib uns heute (ok, der erste Teil ist zugegebenermaßen nur ein Verständnisproblem)…

Ich wurde heute von der Frage eines Studenten überrascht, warum man, wenn man vom 19. April 19 Tage abzieht und dann noch einen weiteren Tag abzieht, als Ergebnis beim 1. April landet.

Genaugenommen ging es ihm um die folgende MySQL-Anweisung aus einem älteren Software-Projekt:

mysql> select CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY;
+----------------------------------------------------------+
| CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY |
+----------------------------------------------------------+
| 2016-04-01                                               |
+----------------------------------------------------------+
1 row in set (0.00 sec)

Hmm, was ist denn, wenn wir den Tag am Ende nicht abziehen?

mysql> select CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 0 DAY;
+----------------------------------------------------------+
| CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 0 DAY |
+----------------------------------------------------------+
| 2016-03-31                                               |
+----------------------------------------------------------+
1 row in set (0.00 sec)

Ok, …, auch interessant. Bei der Suche nach einer Erklärung irritierte mich erstmal, was das INTERVAL in der Anweisung sollte. Ist das ein Cast, oder was?

Die (nach wie vor grottige) MySQL-Doku kennt INTERVAL in der Übersicht der Datums- und Zeitfunktionen nicht als separate Anweisung oder Funktion, sondern lediglich als Schlüsselwort innerhalb von Datumsfunktionen wie z.B. DATE_ADD.

Erst bei deren Beschreibung findet man die nette Information, dass man mittels INTERVAL Datumsberechnungen durchführen kann und zwar mit der Syntax:

date + INTERVAL expr unit

Und damit erkennt man dann auch, wie die originale Anweisung interpretiert wird, nämlich so:

mysql> select CURRENT_DATE - INTERVAL (DAYOFMONTH(CURRENT_DATE) - 1) DAY;
+------------------------------------------------------------+
| CURRENT_DATE - INTERVAL (DAYOFMONTH(CURRENT_DATE) - 1) DAY |
+------------------------------------------------------------+
| 2016-04-01                                                 |
+------------------------------------------------------------+
1 row in set (0,00 sec)

Ok, Rätsel gelöst, ein paar Nebenerkenntnisse gibt es dann doch noch…

Spaßig wird die Sache z.B., wenn man mal ohne INTERVAL arbeitet:

mysql> select CURRENT_DATE - DAYOFMONTH(CURRENT_DATE);
+-----------------------------------------+
| CURRENT_DATE - DAYOFMONTH(CURRENT_DATE) |
+-----------------------------------------+
|                                20160400 |
+-----------------------------------------+
1 row in set (0.00 sec)

Und ja, derartig ungültige Daten darf man sogar in Tabellen abspeichern! Die Doku dazu liest sich MySQL-typisch unbesorgt – man darf halt einfach nicht zuviel “erwarten”, dann geht alles gut 🙂

MySQL permits you to store dates where the day or month and day are zero in a DATE or DATETIME column. […] In this case, you simply store the date as ‘2009-00-00’ or ‘2009-01-00’.

If you store dates such as these, you should not expect to get correct results for functions such as DATE_SUB() or DATE_ADD() that require complete dates.[…]

Die Behandlung des nichtexistenten Jahres 0 kann dann auch niemanden mehr überraschen:

MySQL permits you to store a “zero” value of ‘0000-00-00’ as a “dummy date.” This is in some cases more convenient than using NULL values, and uses less data and index space.

Dies bringt mich zu PostgreSQL, wo selbstverständlich jedwede ungültigen Daten verboten sind. Der zugehörige Abschnitt aus dem Manual gehört zu meinen Lieblingstellen:

The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. This definition applies to all Gregorian calendar countries.

There is no century number 0, you go from -1 century to 1 century. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican.

Nur der Vollständig halber: in PostgreSQL kommt die Berechnung des Monatsersten ohne unschöne Subtraktionen aus – das Äquivalent zu ersten SQL-Anweisung in diesem Blogeintrag lautet:

#select date_trunc('month', current_date)::date;

 date_trunc 
------------
 2016-04-01
(1 row)