Datum und Uhrzeit in SQL

Von allen Datentypen in SQL sind die Zeit-Datentypen die kompliziertesten. Die Komplexität ergibt sich aus mehreren Gründen, und hier sind einige davon:

• eine Vielzahl von Möglichkeiten, Datum und Uhrzeit anzugeben
• das Vorhandensein von Zeitzonen
• die Unklarheit bei der Berechnung bestimmter Werte auf Basis von Zeitdaten. Zum Beispiel die Schwierigkeit, das Alter zu berechnen.

Zeitdaten erzeugen

Zeitdaten lassen sich auf eine der folgenden Arten gewinnen:

• Daten aus einer vorhandenen Spalte mit einem Zeit-Datentyp kopieren
• Datum und Uhrzeit über eine Zeichenkettendarstellung angeben
• Zeitdaten durch den Aufruf eingebauter Funktionen erhalten, die einen Zeittyp zurückgeben

Zeichenkettendarstellung von Zeitdaten

Für die Angabe von Datum und Uhrzeit werden folgende Formate verwendet:

Dabei darf bei der Angabe des Datums jedes beliebige Satzzeichen als Trennzeichen zwischen den Teilen des Datums oder der Uhrzeit verwendet werden. Es ist auch möglich, das Datum ganz ohne Trennzeichen zusammengeschrieben anzugeben.

Beispiele für gültige Angaben von Zeitwerten über die Zeichenkettendarstellung:

MySQL 8.1
SELECT  CAST("2022-06-16 16:37:23" AS DATETIME) AS datetime_1,
        CAST("2014/02/22 16*37*22" AS DATETIME) AS datetime_2,
        CAST("20220616163723" AS DATETIME) AS datetime_3,
        CAST("2021-02-12" AS DATE) AS date_1,
        CAST("160:23:13" AS TIME) AS time_1,
        CAST("89" AS YEAR) AS year

MySQL 8.1
SELECT  CAST('2022-06-16 16:37:23' AS TIMESTAMP) AS timestamp_1,
        CAST('2014/02/22 16:37:22' AS TIMESTAMP) AS timestamp_2,
        CAST('20220616163723' AS TIMESTAMP) AS timestamp_3,
        CAST('2021-02-12' AS DATE) AS date_1,
        CAST('16:23:13' AS TIME) AS time_1

In der Abfrage oben wurde die Funktion CAST verwendet, um die Zeichenkette zwangsweise in ein Datum und eine Uhrzeit umzuwandeln. Sie ist nötig, wenn der Server keinen Zeitwert erwartet und die Zeichenkette deshalb nicht automatisch in den gewünschten Typ umwandelt. Mit der Typkonvertierung beschäftigen wir uns ausführlicher im Artikel „Funktionen zur Typkonvertierung, CAST“.

Funktionen zur Erzeugung von Datumswerten

Wenn man Zeitdaten aus einer Zeichenkette gewinnen muss, die keinem der Formate entspricht, die die Funktion CAST akzeptiert, kann man spezielle Funktionen zum Parsen von Datumswerten verwenden.

MySQL 8.1
SELECT STR_TO_DATE('November 13, 1998', '%M %d, %Y') AS date;

MySQL 8.1
SELECT TO_DATE('November 13, 1998', 'Month DD, YYYY') AS date;

Für die Erzeugung des aktuellen Datums oder der aktuellen Uhrzeit braucht man keine Zeichenkette zu erstellen, die anschließend in ein Datum umgewandelt wird, denn es gibt eingebaute Funktionen, um diese Werte zu bekommen.

MySQL 8.1
SELECT CURDATE(), CURTIME(), NOW();

MySQL 8.1
SELECT CURRENT_DATE, CURRENT_TIME, NOW();

Funktionen zum Extrahieren von Zeitdaten

Manchmal brauchen wir nicht das gesamte Datum, sondern nur einen bestimmten Teil davon, zum Beispiel den Monat oder das Jahr.

Unterschied zwischen DATETIME und TIMESTAMP

Zeitzonen

Da Menschen auf der ganzen Welt wollen, dass der Mittag in etwa dem höchsten Sonnenstand entspricht, gab es nie das Ziel, eine universelle Zeit zu nutzen, und die Welt wurde in 24 Zeitzonen aufgeteilt.

Als Bezugspunkt der Zeit wird UTC (Coordinated Universal Time) verwendet. Alle anderen Zeitzonen lassen sich über die Anzahl der Stunden Versatz zu UTC beschreiben. So lässt sich die Zeitzone von Moskau zum Beispiel als UTC+3 beschreiben.

Die Zeitzone ist eine der Einstellungen des Datenbankservers und kann festgelegt werden:

• global
• für den aktuellen Benutzer
• für die aktuelle Benutzersitzung

MySQL 8.1
SET GLOBAL time_zone = '+03:00';    // global
SET time_zone = '+03:00';           // für den aktuellen Benutzer
SET @@session.time_zone = '+03:00'; // für die aktuelle Benutzersitzung

MySQL 8.1
ALTER DATABASE mydb SET timezone = 'Europe/Moscow';  // global für die Datenbank
ALTER USER myuser SET timezone = 'Europe/Moscow';    // für einen bestimmten Benutzer
SET TIME ZONE 'Europe/Moscow';                       // für die aktuelle Sitzung
SET TIME ZONE '+03:00';                              // für die aktuelle Sitzung

Beispiele für Aufgaben mit Datum und Uhrzeit

Wir möchten gesondert auf die häufigsten Aufgaben rund um den Zeit-Datentyp eingehen, bei denen oft Fehler gemacht werden.

Alter bestimmen

Wenn die Aufgabe lautet, das Alter einer Person anhand ihres Geburtsdatums zu ermitteln, ist man oft versucht, die Differenz zwischen dem aktuellen Jahr und dem Geburtsjahr zu berechnen:

MySQL 8.1
SELECT YEAR(NOW()) - YEAR('2003-07-03 14:10:26');

MySQL 8.1
SELECT EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM TIMESTAMP '2003-07-03 14:10:26');

Das Problem dieses Ansatzes ist, dass er nicht berücksichtigt, ob die Person ihren Geburtstag in diesem Jahr schon gehabt hat oder nicht. Das heißt, ist zum Zeitpunkt der Abfrage der 3. Juli (07-03) bereits eingetreten, hat die Person ihren Geburtstag gefeiert und ist bereits 20 Jahre alt, andernfalls ist sie nach wie vor 19. Die Differenz der Funktionen ist also nicht hilfreich — in beiden Fällen ergibt sie 20 Jahre.

Wenn das Alter über die Jahresdifferenz zu bestimmen also nicht funktioniert, könnte der Wunsch entstehen, das Alter über die Tagesdifferenz zwischen zwei Daten zu ermitteln, diese Differenz dann durch die Anzahl der Tage im Jahr zu teilen und abzurunden:

MySQL 8.1
SELECT FLOOR(DATEDIFF(NOW(), '2003-07-03 14:10:26') / 365);

MySQL 8.1
SELECT FLOOR(EXTRACT(DAY FROM NOW() - TIMESTAMP '2003-07-03 14:10:26') / 365);

Diese Lösung ist deutlich genauer als die vorherige. Sie ist aber nicht absolut genau, weil es Schaltjahre gibt, in denen das Jahr 366 Tage hat. Auch wenn der Fehler bei der Altersberechnung für eine einzelne Person aufgrund der Schaltjahre recht gering ist, kann sich dieser Fehler bei Berechnungen, etwa der Bestimmung des Durchschnittsalters für eine bestimmte Liste von Personen, summieren und die tatsächlichen Werte verzerren.

Wie bestimmt man das Alter dann korrekt?

MySQL 8.1
SELECT TIMESTAMPDIFF(YEAR, '2003-07-03 14:10:26', NOW());

MySQL 8.1
SELECT EXTRACT(YEAR FROM AGE(NOW(), TIMESTAMP '2003-07-03 14:10:26'));