MySQL-Datum- und Zeitleitfaden: Datentypen, Funktionen, Formatierung und Bereichsabfragen

1. Einführung

MySQL ist ein weit verbreitetes Datenbankverwaltungssystem zum Erstellen von Webanwendungen und Datenbanken, und der korrekte Umgang mit Datumsdaten ist besonders wichtig. Zum Beispiel tauchen datumbezogene Daten in vielen Szenarien auf: Verwaltung von Blogbeiträgen, Verfolgung der Verkaufshistorie von Produkten und Speicherung der Anmeldehistorie von Benutzern. Durch die korrekte Verwaltung von Datumsdaten können Sie Daten effizient verarbeiten und den Benutzern genaue Informationen bereitstellen.

In diesem Leitfaden erklären wir umfassend alles von den grundlegenden Datums‑/Zeit‑Datentypen und der Verwendung von Datumsfunktionen in MySQL bis hin zu datumsbasierten Berechnungen und Bereichsabfragen. Dieser Inhalt richtet sich an Anfänger bis Fortgeschrittene, und wir werden praktische Abfragebeispiele aus realen Anwendungsfällen einbinden – sodass er im täglichen Arbeiten nützlich ist.

Durch das Lesen dieses Artikels können Sie Folgendes tun:

• Die Eigenschaften der MySQL-Datums‑/Zeit‑Datentypen verstehen und den passenden Typ für Ihre Daten auswählen.
• Datumsfunktionen verwenden, um das aktuelle Datum, vergangene Daten und zukünftige Daten einfach abzurufen und zu manipulieren.
• Daten für bestimmte Zeiträume extrahieren, indem Sie Datumsbereichssuchen und Vergleiche durchführen.

Nun schauen wir uns die Grundlagen der MySQL‑Daten im nächsten Abschnitt an.

2. Überblick über MySQL-Datums-/Zeit-Datentypen

Bei der Verwaltung von Daten in MySQL ist es wichtig, den richtigen Datums‑/Zeit‑Datentyp je nach Daten und Anwendungsfall zu wählen. MySQL bietet mehrere Datentypen zur Handhabung von Datum und Zeit, jeder mit unterschiedlichen Eigenschaften und Verwendungszwecken. In diesem Abschnitt erklären wir die wichtigsten Datums‑/Zeit‑Typen und ihre typischen Anwendungsfälle im Detail.

DATE

Der DATE‑Typ speichert nur das Datum (Jahr, Monat, Tag) und enthält keine Zeitkomponente. Der unterstützte Bereich reicht von „1000-01-01“ bis „9999-12-31“, sodass er Daten von Jahr 1000 bis 9999 verarbeiten kann. Er eignet sich für rein datumsbezogene Informationen, bei denen die Zeit keine Rolle spielt, wie Geburtstage oder Jubiläen.

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

Der TIME‑Typ speichert Zeit (Stunden, Minuten, Sekunden). Der unterstützte Bereich reicht von „-838:59:59“ bis „838:59:59“. Da er negative Zeiten darstellen kann, lässt er sich auch zur Angabe von Zeitdifferenzen verwenden. Er ist nützlich zur Erfassung von Arbeitsstunden oder Aufgabendauern.

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

Der DATETIME‑Typ speichert sowohl Datum als auch Zeit. Der unterstützte Bereich reicht von „1000-01-01 00:00:00“ bis „9999-12-31 23:59:59“. Er ist unabhängig von Zeitzonen, und Sie können den gespeicherten Wert exakt so abrufen, wie er gespeichert wurde. Er eignet sich zur Aufzeichnung von Zeitstempeln vergangener Ereignisse oder zukünftiger Termine.

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

Der TIMESTAMP‑Typ speichert Datum und Zeit und konvertiert Werte automatisch basierend auf der Zeitzone des Servers. Der unterstützte Bereich reicht von „1970-01-01 00:00:01 UTC“ bis „2038-01-19 03:14:07 UTC“. Er ist mit der Unix‑Epochzeit kompatibel und eignet sich zur Aufzeichnung von Zeitstempeln und Änderungshistorien. Da TIMESTAMP standardmäßig die aktuelle Zeit setzen kann, ist er praktisch, um Erstellungs‑ und Aktualisierungszeiten automatisch zu protokollieren.

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

Der YEAR‑Typ speichert nur das Jahr. Der unterstützte Bereich reicht von „1901“ bis „2155“. Er ist nützlich, wenn ein bestimmtes Jahr dargestellt werden soll, z. B. das Herstellungsjahr oder das Gründungsjahr.

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

Vergleich und Anwendungsfälle jedes Datums-/Zeit-Typs

Zusammenfassung

MySQL‑Datums‑/Zeitdatentypen sollten basierend auf den Merkmalen Ihrer Daten und der geplanten Verwendung ausgewählt werden. Im nächsten Abschnitt werfen wir einen genaueren Blick auf die Unterschiede zwischen DATETIME und TIMESTAMP, die besonders leicht zu verwechseln sind.

3. Unterschiede zwischen DATETIME und TIMESTAMP

Bei der Arbeit mit Datum und Uhrzeit in MySQL werden DATETIME und TIMESTAMP häufig verwendet, aber es gibt wichtige Unterschiede zwischen ihnen. Beide speichern Jahr, Monat, Tag, Stunde, Minute und Sekunde, unterscheiden sich jedoch darin, wie sie Zeitzonen handhaben und welchen Zeitbereich sie speichern können. Sie sollten basierend auf Ihrem Anwendungsfall wählen. In diesem Abschnitt erklären wir die Unterschiede und Eigenschaften von DATETIME und TIMESTAMP im Detail.

Eigenschaften von DATETIME

• Nicht von Zeitzonen abhängig : Der DATETIME‑Typ wird nicht von der Zeitzoneneinstellung des Servers beeinflusst. Sie können den gespeicherten Wert exakt so abrufen, wie er gespeichert wurde.
• Bereich : Er unterstützt von 1000-01-01 00:00:00 bis 9999-12-31 23:59:59.
• Anwendungsfälle : Geeignet für Daten, die Sie ohne Abhängigkeit von einer bestimmten Zeitzone speichern möchten, z. B. historische Ereignisse oder zukünftige Zeitpläne.

Beispiel: Speichern eines Ereignisdatums/-zeit

Zum Beispiel, wenn Sie einen „universellen“ Datum‑/Zeitwert unverändert behalten möchten, ist DATETIME eine gute Wahl. Das folgende Beispiel zeichnet ein Ereignis auf, das zu einem bestimmten Datum und einer bestimmten Uhrzeit geplant ist.

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

In dieser Tabelle wird das in event_datetime gespeicherte Datum/Uhrzeit nicht von Zeitzonen beeinflusst, und der Wert wird exakt so abgerufen, wie er gespeichert wurde.

Eigenschaften von TIMESTAMP

• Von Zeitzonen abhängig : Der TIMESTAMP‑Typ wird basierend auf der Zeitzoneneinstellung des Servers gespeichert und abgerufen. Beim Verschieben von Daten zwischen Servern in unterschiedlichen Zeitzonen erfolgt eine entsprechende Umwandlung.
• Bereich : Er unterstützt von 1970-01-01 00:00:01 UTC bis 2038-01-19 03:14:07 UTC (basierend auf dem Unix‑Zeitbereich).
• Auto‑Update‑Unterstützung : Mit DEFAULT CURRENT_TIMESTAMP und ON UPDATE CURRENT_TIMESTAMP kann MySQL automatisch den aktuellen Zeitstempel beim Einfügen oder Aktualisieren von Daten aufzeichnen.
• Anwendungsfälle : Am besten geeignet, um automatisch Erstellungs‑ oder Aktualisierungszeitpunkte zu verfolgen, z. B. wenn ein Zeitstempel bei jeder Aktualisierung eines Datensatzes geändert werden soll.

Beispiel: Speichern von Erstellungs‑ und Aktualisierungszeiten für Beiträge

Dieses Beispiel nutzt die Auto‑Update‑Funktion von TIMESTAMP, um Erstellungs‑ und Aktualisierungszeiten für Blog‑Beiträge zu speichern.

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Mit dieser Einstellung wird created_at beim ersten Erstellen des Beitrags aufgezeichnet, und updated_at wird bei jeder Bearbeitung des Beitrags automatisch aktualisiert.

Vergleichstabelle: DATETIME vs TIMESTAMP

Wie man wählt

• Wählen Sie DATETIME, wenn Sie keine Zeitzoneneffekte wünschen : Zum Beispiel, wenn Sie eine Ereigniszeit als feste lokale Zeit für ein bestimmtes Land/eine bestimmte Region speichern möchten, kann DATETIME geeignet sein.
• Wählen Sie TIMESTAMP, wenn Sie Auto‑Update oder Zeitzonenhandhabung benötigen : Zum Beispiel, wenn Sie automatisch aufzeichnen möchten, wann Datenbankzeilen aktualisiert werden, ist TIMESTAMP praktisch.

Zusammenfassung

DATETIME und TIMESTAMP haben unterschiedliche Eigenschaften. Durch die Wahl des richtigen Typs für Ihren Zweck können Sie Datums‑ und Zeitdaten effizienter verwalten. Im nächsten Abschnitt schauen wir uns die wesentlichen Datumsfunktionen in MySQL an.

4. Grundlagen der MySQL‑Datum/Uhrzeit‑Funktionen

MySQL bietet zahlreiche Funktionen für die Arbeit mit Datum und Uhrzeit – vom Abrufen des aktuellen Datums/Uhrzeit bis zum Hinzufügen oder Subtrahieren von Intervallen. Diese Funktionen sind nützlich für Datenbankadministration und Analyse. In diesem Abschnitt erklären wir die Grundlagen der MySQL‑Datum‑/Uhrzeit‑Funktionen und ihre typischen Anwendungsfälle.

Funktionen zum Abrufen des aktuellen Datums/Uhrzeit

Drei gängige Funktionen zum Abrufen des aktuellen Datums und der Uhrzeit in MySQL sind NOW(), CURDATE() und CURTIME().

NOW()

Die NOW()-Funktion gibt das aktuelle Datum und die Uhrzeit im Format YYYY-MM-DD HH:MM:SS zurück. Sie ist nützlich für das Logging und das Zeitstempeln von Datensätzen.

SELECT NOW(); -- Get the current date and time

CURDATE()

Die CURDATE()-Funktion gibt das aktuelle Datum im Format YYYY-MM-DD zurück. Sie enthält keine Uhrzeit, daher eignet sie sich, wenn Sie nur das Datum benötigen.

SELECT CURDATE(); -- Get the current date

CURTIME()

Die CURTIME()-Funktion gibt die aktuelle Uhrzeit im Format HH:MM:SS zurück. Verwenden Sie sie, wenn Sie nur die Uhrzeit ohne das Datum benötigen.

SELECT CURTIME(); -- Get the current time

Functions to Add/Subtract Dates

Um Intervalle zu einem Datum hinzuzufügen oder abzuziehen, verwenden Sie DATE_ADD() und DATE_SUB(). Diese Funktionen erleichtern die Berechnung zukünftiger oder vergangener Daten.

DATE_ADD()

Die DATE_ADD()-Funktion fügt ein angegebenes Intervall zu einem Datum hinzu. Zum Beispiel ist sie nützlich, um das Datum 7 Tage später oder 1 Monat später zu erhalten.

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

Die DATE_SUB()-Funktion zieht ein angegebenes Intervall von einem Datum ab. Verwenden Sie sie, um vergangene Daten zu berechnen.

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

Function to Calculate Date Differences

Um den Unterschied zwischen zwei Daten zu berechnen, ist DATEDIFF() praktisch. Zum Beispiel können Sie berechnen, wie viele Tage seit einem bestimmten Datum vergangen sind oder die Anzahl der Tage zwischen zwei Daten.

DATEDIFF()

Die DATEDIFF()-Funktion gibt den Unterschied zwischen zwei Daten in Tagen zurück. Sie ist nützlich, wenn Sie die Anzahl der Tage von einem Startdatum bis zu einem Enddatum bestätigen möchten.

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Other Useful Date Functions

MySQL bietet zusätzliche hilfreiche Datumsfunktionen.

EXTRACT()

Die EXTRACT()-Funktion extrahiert einen spezifischen Teil (Jahr, Monat, Tag usw.) aus einem Datum. Sie ist nützlich, wenn Sie nur einen Teil eines Datums benötigen.

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

Die DATE_FORMAT()-Funktion zeigt ein Datum in einem angegebenen Format an. Sie ist nützlich, wenn Sie Daten in einem spezifischen Format anzeigen möchten (z. B. japanisches Format).

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. Date Formatting and Conversion Methods

In MySQL können Sie die Anzeige von Daten in ein lesbareres Format ändern oder Datumsstrings in Datentypen umwandeln. Dies ermöglicht es Ihnen, Anzeigeformate flexibel zu steuern und Daten konsistent zu verwalten, auch wenn Eingaben in unterschiedlichen Formaten vorliegen. In diesem Abschnitt erklären wir die Hauptfunktionen, die für die Formatierung und Konvertierung von Daten verwendet werden.

Date Formatting with the DATE_FORMAT() Function

Mit DATE_FORMAT() können Sie DatumsWerte in einem angegebenen Format anzeigen. Dies ist besonders nützlich, wenn Sie ein Anzeigeformat benötigen, das vom Standard abweicht, wie das japanische „YYYY年MM月DD日“-Format.

Format Options

Mit DATE_FORMAT() können Sie Formatoptionen wie die folgenden verwenden:

• %Y : 4-digit year
• %y : 2-digit year
• %m : 2-digit month (01–12)
• %d : 2-digit day (01–31)
• %H : 2-digit hour (00–23)
• %i : 2-digit minute (00–59)
• %s : 2-digit second (00–59)

Example Usage

Zum Beispiel, um das Datum 2023-01-01 als „2023年01月01日“ anzuzeigen, können Sie Folgendes schreiben:

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

Sie können auch ein Datum und eine Uhrzeit mit Schrägstrichen anzeigen, wie „2023/01/01 12:30:45“.

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

Umwandeln von Zeichenketten in Daten mit STR_TO_DATE()

Die Funktion STR_TO_DATE() konvertiert einen Datums‑String in einen MySQL‑Datumstyp. Zum Beispiel ist sie nützlich, wenn Sie einen String wie „2023年01月01日“ als DATE‑Wert behandeln möchten.

Beispielhafte Verwendung

Um den String „2023-01-01“ in einen DATE‑Wert zu konvertieren, schreiben Sie Folgendes:

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

Um einen japanisch‑stilisierten String wie „2023年01月01日“ in ein Datum zu konvertieren, geben Sie das entsprechende Format an:

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

Beispiele für die Konvertierung von Daten aus verschiedenen Formaten

In der Praxis können Eingabeformate für Daten variieren, sodass Sie möglicherweise aus unterschiedlichen Formaten konvertieren müssen. Nachfolgend einige Beispiele.

1. Konvertiere „YYYY/MM/DD“ in einen DATE‑Wert

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE
   

2. Konvertiere „MM-DD-YYYY“ in einen DATE‑Wert

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE
   

Auf diese Weise können Sie Daten, die in unterschiedlichen Formaten eingegeben wurden, konsistent in einem einheitlichen Datumsformat speichern und verwalten.

Unterschiede zwischen DATE_FORMAT und STR_TO_DATE und wann man welche verwendet

• DATE_FORMAT() : Wird verwendet, um das Anzeigeformat vorhandener Datumswerte zu ändern. Da die Änderung nur die Darstellung betrifft, wird das gespeicherte Datum selbst nicht verändert.
• STR_TO_DATE() : Wird verwendet, um einen Datums‑String in einen MySQL DATE‑ oder DATETIME‑Wert zu konvertieren. Dadurch ändert sich der gespeicherte Datentyp, was die Verarbeitung von Daten mit anderen MySQL‑Funktionen und -Abfragen erleichtert.

Zusammenfassung

Durch die Verwendung von DATE_FORMAT() und STR_TO_DATE() können Sie flexibel steuern, wie Datumsdaten angezeigt und gespeichert werden. Das ermöglicht eine flexiblere Handhabung von Benutzer‑ oder Systemeingaben bei gleichzeitig konsistenter Datumsverwaltung. Im nächsten Abschnitt erklären wir Datumsberechnungen und -vergleiche und gehen genauer darauf ein, wie Zeiträume mit Datumsdaten berechnet und verglichen werden können.

6. Datumsberechnungen und Vergleiche

MySQL bietet mehrere praktische Funktionen für Datumsberechnungen und -vergleiche. Diese ermöglichen das Extrahieren von Daten für bestimmte Zeiträume oder das Berechnen von Datumsdifferenzen für Analysen. In diesem Abschnitt stellen wir die wichtigsten Funktionen für Datumsberechnungen und -vergleiche vor und zeigen, wie sie verwendet werden.

Funktion zur Berechnung von Datumsdifferenzen: DATEDIFF()

Die Funktion DATEDIFF() gibt die Differenz zwischen zwei Daten in „Tagen“ zurück. Sie ist nützlich, wenn Sie die verstrichenen Tage zwischen Daten prüfen oder ermitteln möchten, wie viele Tage seit einem vergangenen Ereignis bis heute vergangen sind.

Beispielhafte Verwendung

Um beispielsweise die Anzahl der Tage vom 1. Januar 2023 bis zum 10. Januar 2023 zu berechnen, schreiben Sie Folgendes:

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

In diesem Beispiel beträgt die Differenz vom Start‑ bis zum Enddatum 9 Tage, sodass das Ergebnis 9 ist.

Berechnung von Differenzen nach Einheit mit TIMESTAMPDIFF()

Die Funktion TIMESTAMPDIFF() berechnet die Differenz zwischen zwei Daten oder Datums‑Zeitwerten in einer angegebenen Einheit (Jahr, Monat, Tag, Stunde, Minute, Sekunde). Sie können damit beispielsweise Differenzen in „Monaten“ oder „Stunden“ nach Bedarf ermitteln.

Beispielhafte Verwendung

1. Berechne eine Differenz in Monaten

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12
   

2. Berechne eine Differenz in Stunden

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36
   

Daten vergleichen

Datumsvergleiche werden mit den Standard‑Vergleichsoperatoren von MySQL (<, >, <=, >=, =) durchgeführt. Dadurch können Sie Daten innerhalb eines bestimmten Zeitraums extrahieren oder vergangene/ zukünftige Daten als Bedingungen einbeziehen.

Beispielhafte Verwendung

Um beispielsweise Daten „am oder nach dem 1. Januar 2023“ zu extrahieren, schreiben Sie Folgendes:

SELECT * FROM events WHERE event_date >= '2023-01-01';

Angabe eines Bereichs mit BETWEEN

Der BETWEEN-Operator ermöglicht es Ihnen, einen Datumsbereich anzugeben, um Daten abzurufen. Er ist nützlich, wenn Sie Datensätze extrahieren möchten, die in einen bestimmten Zeitraum fallen.

Example Usage

Zum Beispiel, um Daten vom 1. Januar 2023 bis zum 31. Januar 2023 abzurufen, schreiben Sie Folgendes:

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

Datumsberechnungen mit ADDDATE() und SUBDATE()

Sie können ADDDATE() und SUBDATE() verwenden, um einem Datum eine bestimmte Anzahl von Tagen hinzuzufügen oder davon zu subtrahieren. Diese sind praktisch für die Berechnung zukünftiger oder vergangener Daten.

Example Usage

1. Füge einem Datum 10 Tage hinzu

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11
   

2. Subtrahiere 10 Tage von einem Datum

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22
   

Zusammenfassung

Durch die Verwendung von MySQL-Datumsberechnungs- und Vergleichsfunktionen können Sie Daten für bestimmte Zeiträume effizient extrahieren und Daten nach Zeitbereichen analysieren. Im nächsten Abschnitt werfen wir einen genaueren Blick auf das fortgeschrittenere Thema „Datumsbereichssuchen“.