Datum- und Zeitfunktionen in MySQL
Im vorherigen Artikel haben wir die Spaltentypen DATE/DATETIME/TIMESTAMP beschrieben und wie sich damit Zeit- und Datumsangaben abspeichern lassen. Das schöne an diesen Spaltentypen ist, dass MySQL viele Zeit und Datums-Funktionen anbieten die das Arbeiten erleichtern.
Inhaltsverzeichnis
Aktuelle Zeit automatisch für neue Einträge
In vielen Fällen kann es praktisch sein den Zeitpunkt wann ein Datensatz erstellt wurde abzuspeichern. Beispielsweise das Registrierungsdatum oder das Erstelldatum von einem Blogartikel. MySQL bietet dafür für TIMESTAMP und ab MySQL 5.6 auch für DATETIME-Felder den Standardwert CURRENT_TIMESTAMP .
Eure aktuelle MySQL Version könnt ihr mittels folgendem SQL-Query überprüfen:
|
1 |
SHOW VARIABLES LIKE "%version%" |
Damit nun das Datum und die Zeit automatisch mit jedem Eintrag eingespeichert wird, legt beim Anlegen neuer Tabellen mittels phpMyAdmin dazu eine Spalte mit dem Typ TIMESTAMP an und als Standardwert wählt ihr den Eintrag CURRENT_TIMESTAMP.
Sobald ihr nun neue Einträge in dieser Tabelle speichert, wird für das Feld created_at das aktuelle Datum und die aktuelle Zeit abgespeichert.
Timestamp aktualisieren
Neben dem Erstelldatum kann auch das Datum des letzten Updates von Interesse sein, bei einem Blog-Artikel beispielsweise das Datum wann dieser zuletzt bearbeitet wurde. Hier empfiehlt sich eine weitere TIMESTAMP-Spalte anzulegen, beispielsweise mit dem Namen updated_at.
Um diese nun zu aktualisieren, können wir die MySQL-Funktion NOW() verwenden.
|
1 |
UPDATE tabelle SET spalte1 = 'Wert1', spalte2 = 'Wert2', ..., updated_at = NOW() WHERE id = 1 |
Zuerst aktualisieren wir wie gewünscht die normalen Spalten, beispielsweise wir verändern den Vornamen in der User-Tabelle. Ebenfalls aktualisieren wir die updated_at-Spalte und weisen dieser den Wert der Funkion NOW() zu.
Neben dieser manuellen Variante können wir diesen auch automatisieren und MySQL aktualisiert den Timestamp bei jedem Update automatisch. Dazu wählen wir in phpMyAdmin unter Attribute den Wert on update CURRENT_TIMESTAMP.
Sofern wir diese Attribute gesetzt haben, müssen wir uns um die Aktualisierung nicht mehr kümmern. MySQL aktualisert mit jedem Update den Wert des Feldes automatisch.
Hinweis: Möglicherweise gibt MySQL euch folgende Fehlermeldung zurück, wenn ihr versucht eine entsprechende updated_at-Spalte zu erstellen:
|
1 2 |
Error Code : 1293 Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause |
In MySQL vor Version 5.6.5 konnte jede Tabelle nur eine Tmestamp-Spalte haben, die automatisiert eingetragen bzw. aktualisiert wird. Falls ihr also MySQL vor 5.6.5 nutzt, so müsst ihr euch entscheiden ob ihr für created_at oder für updated_at den aktuellen Timestamp als Standardwert nutzen wollt. Nutzt ihr die automatische Vergabe des Timestamps beim Eintrag für die Spalte created_at, so müsst ihr entsprechend mittels dem obigen SQL-Query die Spalte updated_at aktualisieren. Ab MySQL 5.6.5 wurde diese Beschränkung aufgehoben und ihr könnt beliebig viele Timestamp-Spalten mit beliebigen Standard-Werten und on update-Attributen nutzen.
Zurückliegendes Datum berechnen
In vielen Fällen möchten wir alle Datensätze einer gewissen Zeitspanne erhalten, beispielsweise alle Einträge der letzten 30 Tage oder alle Einträge von 2015. Nachfolgend nehmen wir an, dass ihr eine Spalte created_at habt die entweder ein TIMESTAMP oder ein DATETIME ist.
Sofern das Zeitintervall fix ist, dann ist die SQL Abfrage recht einfach. Folgender Query gibt euch alle Einträge die in 2015 erstellt wurden:
|
1 |
SELECT * FROM tabelle WHERE created_at >= '2015-01-01 00:00:00' AND created_at <= '2015-12-31 23:59:49' |
Bei sich verändernden Zeitintervallen, z.B. Einträge der letzten 30 Tage, funktioniert dies nicht mehr. Wir könnten den Zeitpunkt entweder in PHP berechnen oder wir nutzen die MySQL-Funktion DATE_SUB() .
Um die Einträge der letzten 30 Tage zu erhalten nutzen wir folgendes SQL-Query:
|
1 |
SELECT * FROM tabelle WHERE created_at >= DATE_SUB(NOW(),INTERVAL 30 DAY) |
Bei DATE_SUB() gebt ihr als erstes ein Ausgangsdatum inklusive Zeit an. Dies kann entweder ein entsprechend formatiertes Datum sein oder in den meisten Fällen wird man das aktuelle Datum nutzen, welches wir mittels der MySQL-Funktion NOW() erhalten.
Das zweite Argument ist die Zeitspanne die wir mittels DATE_SUB() vom Ausgangsdatum abziehen möchten. In diesem Fall ziehen wir 30 Tage ab. Neben DAY könnt ihr mittels MONTH auch eine Anzahl an Monaten subtrahieren. Die unterschiedliche Anzahl an Tagen pro Monat werden dabei berücksichtigt. Die wichtigsten Zeiteinheiten sind: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR.
Falls ihr ein Datum in der Zukunft berechnen möchtet dann könnt ihr die Funktion DATE_ADD() verwenden. Der folgende SQL-Query gibt z.B. die User-Accounts zurück die zwischen jetzt (NOW()) und in 7 Tagen auslaufen:
|
1 |
SELECT * FROM user_accounts WHERE expire >= NOW() AND expire <= DATE_ADD(NOW(),INTERVAL 7 DAY) |
DAY(), MONTH() und YEAR()
Mittels DATE_SUB() und DATE_ADD() konnten wir einfach Datumsangaben berechnen, die z.B. eine gewisse Anzahl in der Vergangenheit oder in der Zukunft liegen. Für den Fall dass wir aber alle Einträge zu einem gewissen Tag, Monat oder Jahr herausfinden möchten sind diese Funktionen weniger geeignet. Hier bietet MySQL aber die drei Funktion DAY(), MONTH() und YEAR() an.
Angenommen wir möchten alle Einträge erhalten, die im März erstellt wurden. Dies geht mittels folgendem SQL-Query:
|
1 |
SELECT * FROM tabelle WHERE MONTH(created_at) = 3 |
Mittels MONTH(created_at) wird von der Spalte created_at nur die Monatsangabe verwendet, hier erhalten wir also alle Einträge die im März erstellt wurden. Um alle Einträge zwischen 2010 und 2015 zu erhalten, können wir folgendes Query verwenden:
|
1 |
SELECT * FROM tabelle WHERE YEAR(created_at) >= 2010 AND YEAR(created_at) <= 2015 |
Diese Funktionen sind auch sehr nützlich um z.B. Statistiken über eine Tabelle zu erstellen. Möchtet ihr z.B. die Anzahl der neuen Einträge pro Monat erhalten, dann kann dies aus einer Kombination aus COUNT() und GROUP BY (Gefundene Einträge zählen mittels COUNT
Zum Abschluss des Artikels ein Überblick über die wichtigsten Funktion im Umgang mit Datums- und Zeitfelder in MySQL:
| Funktion | Beschreibung |
|---|---|
| SECOND() | Gibt die Sekunden zurück. |
| MINUTE() | Gibt die Minuten zurück. |
| HOUR() | Gibt die Stunden zurück. |
| DAY() | Gibt den Tag zurück. |
| DAYOFWEEK() | Gibt den Wochentag (1=Sonntag, 7=Samstag) zurück. |
| MONTH() | Gibt den Monat (als Zahl) zurück. |
| YEAR() | Gibt das Jahr zurück. |
| NOW() | Gibt das aktuelle Datum und die aktuelle Zeit zurück ('YYYY-MM-DD HH:MM:SS'). |
| DATE_SUB() | Zieht vom ersten Argument eine Zeitspanne ab. |
| DATE_ADD() | Addierst auf das erste Argument eine Zeitspanne. |