MySQL bietet eine Anzahl von Funktionen, mit denen Sie datumsbezogene Berechnungen durchführen können, um etwa Altersangaben zu ermitteln oder Teile aus Datumsangaben zu extrahieren.
Um zu bestimmen, wie alt Ihre Haustiere jeweils sind,
berechnen Sie die Differenz im Jahresbestandteil des aktuellen
und des Geburtsdatums und ziehen den Wert 1
ab, sofern das aktuelle Datum im Kalenderjahr vor dem
Geburtsdatum liegt. Die folgende Abfrage zeigt für jedes
Haustier das Geburtsdatum, das aktuelle Datum und das Alter in
Jahren an.
mysql>SELECT name, geburtstag, CURDATE(),
->(YEAR(CURDATE())-YEAR(geburtstag))
->- (RIGHT(CURDATE(),5)<RIGHT(geburtstag,5))
->AS tieralter
->FROM haustier;
+----------+------------+------------+-----------+ | name | geburtstag | CURDATE() | tieralter | +----------+------------+------------+-----------+ | Fluffy | 1993-02-04 | 2003-08-19 | 10 | | Claws | 1994-03-17 | 2003-08-19 | 9 | | Buffy | 1989-05-13 | 2003-08-19 | 14 | | Fang | 1990-08-27 | 2003-08-19 | 12 | | Bowser | 1989-08-31 | 2003-08-19 | 13 | | Chirpy | 1998-09-11 | 2003-08-19 | 4 | | Whistler | 1997-12-09 | 2003-08-19 | 5 | | Slim | 1996-04-29 | 2003-08-19 | 7 | | Puffball | 1999-03-30 | 2003-08-19 | 4 | +----------+------------+------------+-----------+
Hierbei extrahiert YEAR()
die Jahreszahl
aus dem Datum, während mit RIGHT()
die
fünf Zeichen ganz rechts im Datum (MM-DD
,
also der Monat und der Tag) ermittelt werden. Der Teil des
Ausdrucks, der die Werte MM-DD
vergleicht,
ist entweder 1
oder 0
.
Hiermit wird von der Jahresdifferenz ggf. ein Jahr abgezogen,
sofern CURDATE()
(das aktuelle Datum) im
Kalenderjahr vor geburtstag
liegt. Der
gesamte Ausdruck wirkt ein wenig unübersichtlich, weswegen
ein Alias (tieralter
)
verwendet wird, um die Beschriftung der Ausgabespalte
sinnvoller zu gestalten.
Die Abfrage funktioniert zwar, aber das Ergebnis ließe sich
einfacher erfassen, wenn die Datensätze in einer bestimmten
Reihenfolge angezeigt würden. Dies ist durch Ergänzen der
Klausel ORDER BY name
möglich, die die
Ausgaben dem Namen nach sortiert:
mysql>SELECT name, geburtstag, CURDATE(),
->(YEAR(CURDATE())-YEAR(geburtstag))
->- (RIGHT(CURDATE(),5)<RIGHT(geburtstag,5))
->AS tieralter
->FROM haustier ORDER BY name;
+----------+------------+------------+-----------+ | name | geburtstag | CURDATE() | tieralter | +----------+------------+------------+-----------+ | Bowser | 1989-08-31 | 2003-08-19 | 13 | | Buffy | 1989-05-13 | 2003-08-19 | 14 | | Chirpy | 1998-09-11 | 2003-08-19 | 4 | | Claws | 1994-03-17 | 2003-08-19 | 9 | | Fang | 1990-08-27 | 2003-08-19 | 12 | | Fluffy | 1993-02-04 | 2003-08-19 | 10 | | Puffball | 1999-03-30 | 2003-08-19 | 4 | | Slim | 1996-04-29 | 2003-08-19 | 7 | | Whistler | 1997-12-09 | 2003-08-19 | 5 | +----------+------------+------------+-----------+
Um die Ausgabe nach dem Alter statt dem Namen zu sortieren,
verwenden Sie einfach eine andere ORDER
BY
-Klausel:
mysql>SELECT name, geburtstag, CURDATE(),
->(YEAR(CURDATE())-YEAR(geburtstag))
->- (RIGHT(CURDATE(),5)<RIGHT(geburtstag,5))
->AS tieralter
->FROM haustier ORDER BY tieralter;
+----------+------------+------------+-----------+ | name | geburtstag | CURDATE() | tieralter | +----------+------------+------------+-----------+ | Chirpy | 1998-09-11 | 2003-08-19 | 4 | | Puffball | 1999-03-30 | 2003-08-19 | 4 | | Whistler | 1997-12-09 | 2003-08-19 | 5 | | Slim | 1996-04-29 | 2003-08-19 | 7 | | Claws | 1994-03-17 | 2003-08-19 | 9 | | Fluffy | 1993-02-04 | 2003-08-19 | 10 | | Fang | 1990-08-27 | 2003-08-19 | 12 | | Bowser | 1989-08-31 | 2003-08-19 | 13 | | Buffy | 1989-05-13 | 2003-08-19 | 14 | +----------+------------+------------+-----------+
Eine ähnliche Abfrage kann verwendet werden, um das erreichte
Alter bereits verstorbener Tiere zu bestimmen. Welche Tiere
dies sind, ermitteln Sie durch die Überprüfung, ob für
todestag
der Wert NULL
lautet. Bei denjenigen Datensätzen, bei denen der Wert nicht
NULL
ist, berechnen Sie die Differenz
zwischen den Werten todestag
und
geburtstag
:
mysql>SELECT name, geburtstag, todestag,
->(YEAR(todestag)-YEAR(geburtstag)) - (RIGHT(todestag,5)<RIGHT(geburtstag,5))
->AS tieralter
->FROM haustier WHERE todestag IS NOT NULL ORDER BY tieralter;
+--------+------------+------------+-----------+ | name | geburtstag | todestag | tieralter | +--------+------------+------------+-----------+ | Bowser | 1989-08-31 | 1995-07-29 | 5 | +--------+------------+------------+-----------+
Die Abfrage verwendet todestag IS NOT NULL
statt todestag <> NULL
, da
NULL
ein Sonderwert ist, der nicht mithilfe
der üblichen Vergleichsoperatoren verglichen werden kann. Wir
werden später noch darauf eingehen. Siehe auch
Abschnitt 3.3.4.6, „Mit NULL
-Werten arbeiten“.
Was aber, wenn Sie nun wissen wollen, welche Tiere im
nächsten Monat Geburtstag haben? Für diese Art der
Berechnung sind Jahr und Tag irrelevant: Sie müssen lediglich
den Monat aus der Spalte geburtstag
extrahieren. MySQL bietet mehrere Funktionen zur Extraktion
von Datumsbestandteilen, z. B. YEAR()
,
MONTH()
und
DAYOFMONTH()
. MONTH()
ist für unsere Belange die passende Funktion. Um zu sehen,
wie sie funktioniert, führen Sie eine einfache Abfrage aus,
die den Wert von geburtstag
und
MONTH(geburtstag)
anzeigt:
mysql> SELECT name, geburtstag, MONTH(geburtstag) FROM haustier;
+----------+------------+-------------------+
| name | geburtstag | MONTH(geburtstag) |
+----------+------------+-------------------+
| Fluffy | 1993-02-04 | 2 |
| Claws | 1994-03-17 | 3 |
| Buffy | 1989-05-13 | 5 |
| Fang | 1990-08-27 | 8 |
| Bowser | 1989-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+-------------------+
Das Suchen von Tieren, die nächsten Monat Geburtstag haben,
ist ebenfalls ganz einfach. Nehmen wir einmal an, es wäre
April. In diesem Fall ist der Monatswert 4
– Sie suchen also nach Tieren, die im Mai (d. h. im Monat
5
) geboren sind. Das geht wie folgt:
mysql> SELECT name, geburtstag FROM haustier WHERE MONTH(geburtstag) = 5;
+-------+------------+
| name | geburtstag |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
Wenn der aktuelle Monat Dezember ist, gibt es eine kleine
Komplikation. Sie können nämlich nicht einfach die
Monatsnummer (12
) um den Wert
1
erhöhen – die Suche nach im Monat
13
geborenen Tieren brächte kein Ergebnis,
weil es diesen Monat nicht gibt. Schließlich suchen Sie nach
Tieren, die im Januar (Monat 1
) geboren
sind.
Sie können die Abfrage so verfassen, dass sie unabhängig vom
aktuellen Monat funktioniert, sodass Sie die Nummer eines
bestimmten Monats gar nicht verwenden müssen.
DATE_ADD()
erlaubt Ihnen das Addieren eines
bestimmten Zeitintervalls für ein gegebenes Datum. Wenn Sie
einen Monat zum aktuellen Wert von
CURDATE()
hinzuaddieren wollen, extrahieren
Sie den Monatsbestandteil mit MONTH()
. Das
Ergebnis ist der Monat, in dem Sie nach Geburtstagen suchen
müssen:
mysql>SELECT name, geburtstag FROM haustier
->WHERE MONTH(geburtstag) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
Eine andere Möglichkeit, diese Aufgabe zu lösen, besteht
darin, 1
hinzuzuaddieren und dann den auf
den aktuellen Monat folgenden Monat zu ermitteln, wobei
mithilfe der Modulofunktion (MOD
) der
Monatswert auf 0
gesetzt wird, wenn er
derzeit 12
beträgt:
mysql>SELECT name, geburtstag FROM haustier
->WHERE MONTH(geburtstag) = MOD(MONTH(CURDATE()), 12) + 1;
Beachten Sie, dass MONTH
eine Zahl zwischen
1
und 12
zurückgibt.
Ferner gibt MOD(something,12)
eine Zahl
zwischen 0
und 11
zurück. Insofern muss die Addition nach
MOD()
erfolgen, andernfalls würden wir von
November (11
) direkt zu Januar
(1
) springen.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.