Operationen mit Datumsangaben
timestamp
Mit Datumsangaben rechnen
Ausgabe von Datumsangaben formatieren
Die Funktionen year(), dayofmonth(), hour(), second(), dayofyear()
Ermitteln aller Datensätze die vor X Minuten eingetragen wurden
Differenzen zwischen zwei Datumangaben berechnen
Die Funktionen unix_timestamp und from_unixtime
if Bedingung in sql statements

Operationen mit Datumsangaben

nach oben

Operationen mit Datumsangaben braucht man öfters, als man vielleicht denkt. Zum einen hat man das Problem, dass mysql das Datum in einem Format abspeichert, das zumindest für Deutsche ungewöhnlich ist. Will man das Datum in einer Anwendung aufblenden, muss aus Gründen der Lesbarkeit anders formatiert werden. Es kann einen aber auch interessieren, wieviel Tage zwischen dem Datum X und dem Datum Y liegen, oder welches Datum man hat, wenn man zum Datum X die Anzahl Y an Tagen dazuaddiert bzw. abzieht. Dies ist z.B. dann notwendig, wenn man einen online Shop betreibt, die Lieferfrist 14 Tage beträgt und man dem User mitteilen will, wann er die Ware erhält oder in einer Auktion, die in X Tagen abläuft und man den Tag bestimmen will, wann die Auktion beendet ist. Weiter gibt es sehr zahlreiche Anwendungen, wo mitgespeichert werden soll, wann der User irgendetwas getan hat. mysql bietet sehr viele Funktionen zum bearbeiten von Datumsangaben. Wir werden hier nur die wichtigsten erläutern. Wer vorhat im Internet eine Auktion,Chat, Forum, Gästebuch,Shop etc. etc. zu programmieren, tut gut daran, sich mit den Möglichkeiten von mysql zum Bearbeiten von Datumsangaben vertraut zu machen. Unter Umständen programmiert man sonst in einer Programmiersprache Dinge, die unter Nutzung der Möglichkeiten von mysql sehr einfach gelöst werden können.

mysql kennt bei Datumsangaben folgende Typen.

Hinsichtlich der Generierung einer Tabelle mit diesen Typen gibt es erstmal nichts besonderes zu berichten. Es funktionniert nach dem bekannten Schema.

mysql> create table test1(Name char(50),Geburtstag datetime);
Query OK, 0 rows affected (0.11 sec)

mysql> create table test2(Name char(50),Geburtstag date);
Query OK, 0 rows affected (0.00 sec)

mysql> create table test3(Name char(50),eingetragen_am timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> create table test4(Name char(50),wichtiger_Termin time);
Query OK, 0 rows affected (0.00 sec)

mysql> create table test5(Name char(50),Hochzeitstag year);
Query OK, 0 rows affected (0.00 sec)

mysql>

Anders verhält es sich bei der Frage, wie man Daten in die entsprechenden Spalten einliest. Machen wir uns das klar:

Die Eingabe in ein Feld Datetime muss folgendes Format haben. JJJJ-MM-TT StST:MiMi:SeSe.
Beispiel

mysql> insert into test1(Name,Geburtstag) values
-> ('Maria','1969-12-02 13:45-39');
Query OK, 1 row affected (0.05 sec)

mysql> insert into test1(Name,Geburtstag) values
-> ('Maria','19691202134539');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1(Name,Geburtstag) values
-> ('Maria','1969.12.02 13:45:39');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1(Name,Geburtstag) values
-> ('Maria','02-12-1969 13:45:39');
Query OK, 1 row affected (0.05 sec)

mysql>

Wie deutlich zu erkennen, werden alle möglichen Formatierungen bei der Eingabe akzeptiert. Das Resultat sieht so aus.

mysql> select * from test1;

	Name		Geburtstag
	Maria		1969-12-02 13:45:39
	Maria		1969-12-02 13:45:39
	Maria		1969-12-02 13:45:39
	Maria		0000-00-00 00:00:00

4 rows in set (0.06 sec)
mysql>

Das heisst, dass mysql in einem gewissen Umfang versucht, die Formatierung aus der Eingabe in ein korrektes Format zu wandeln. Wenn dies aber nicht möglich ist, erscheint 0000-00-00 00:00:00. mysql speichert also Datumsangaben in einem bestimmten Format ab und nur mit diesem Format funktionnieren die spezifischen Funktionen für Berechnungen mit Datumsangaben.

timestamp

nach oben

timestamp ist in mehrer Hinsicht anders als andere Datumstypen. Erstens gibt der User bei timestamp gar nichts ein. mysql generiert automatisch das aktuelle Datum und fügt es in die Spalte ein.

mysql> insert into test3(Name) values ('Hanna');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;

	Name		eingetragen_am
	Hanna		20030121161335

1 row in set (0.00 sec)
mysql>

Zweitens ist, wie deutlich zu erkennen, das Format anders. Es fehlen die Bindestriche zwischen Jahr, Monat, Tag wie auch das Leerzeichen zwischen Datum und Uhrzeit und die Doppelpunkte. Die Art und Weise, wie die Feinsteuerung der Formatierung vorgenommen wird, ist ebenfalls anders.

Man kann also sehr genau steuern, was man tatsächlich abspeichern will.
Beispiel

mysql> create table timestamp(Name char(50),erfasst_am timestamp(14));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into timestamp(Name) values ('Maria');
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp;

	Name		erfasst_am
	Maria		20030121162355

1 row in set (0.00 sec)
mysql> create table timestamp2(Name char(50),erfasst_am timestamp(8));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into timestamp2(Name) values ('Maria');
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp2;

	Name		erfasst_am
	Maria		20030121

1 row in set (0.05 sec)
mysql>

Wie deutlich zu sehen, wird bei der ersten Tabelle (timestamp) die Zeit miterfasst, denn die Tabelle wurde mit timestamp(14) generiert. Im zweiten Beispiel, timestamp2, wird die die Zeit nicht mit erfasst, die Tabelle wurde mit timestamp(8) erfasst.

Mit Datumsangaben rechnen

nach oben

Wir stehen also wieder vor unserem alten Problem. Um mit Datumsangaben rechnen zu können, brauchen wir erstmal eine Tabelle, die ein paar Datumsangaben hat. Wir generieren also eine Tabelle mit diesen Spalten.

Name
Geburtstag date
Hochzeit date
erstes_Kind datetime
erster_Seitensprung date
eingetragen_am timestamp

Die Tabelle generieren wir direkt von der Kommandoebene aus.

mysql> create table hochzeiten (Name char(50),
-> Geburtstag date,Hochzeit date,erstes_kind datetime,
-> erster_seitensprung date, eingetragen_am timestamp);
Query OK, 0 rows affected (0.00 sec)
mysql>

Auch beim Einspulen von Spieldaten sehen wir uns wieder mit der Tatsache konfrontiert, dass dies mühsam ist. Wir spielen die Daten also wieder aus einem Flatfile direkt in die Datenbank. Diesen Flatfile kann man hier downloaden.

Er hat folgendes Aussehen. Als Delimiter dient hier die doppelte Pipe (||).

Diese Datei spulen wir jetzt in die mysql Datenbank.

mysql> load data local infile 'c:/mysql_handbuch/hochzeiten.txt' into table
-> hochzeiten
-> fields terminated by '||';
Query OK, 10 rows affected (0.00 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 11
mysql>

Will man prüfen, ob alle funktionniert hat, kann man sowas machen.

mysql> select hochzeit from hochzeiten;

10 rows in set (0.00 sec)
mysql>

Nun sind wir soweit, dass wir Operationen mit Datumsangaben durchführen können.

Ausgabe von Datumsangaben formatieren

nach oben

Wie deutlich zu sehen, ist das Datum von einer für Deutsche normalen Formatierung, 05.12.1999, weit entfernt. Mit der Funktion date_format lässt sich die Formatierung allerdings beliebig änderen. Die Funktion date_format bekommt zwei Parameter übergeben, das Datum selbst und Formatierungszeichen. Folgende Formatierungszeichen sind möglich.

Wollen wir also das Datum umformatieren, können wir das mit der Funktion date_format folgendermassen tun.

Beispiel 1

mysql> select hochzeit, date_format(hochzeit,'%d.%m.%Y') as 'Datum formatiert'
-> from hochzeiten;

	hochzeit		Datum formatiert
	1999-05-12		12.05.1999
	1990-04-09		09.04.1990
	2000-09-03		03.09.2000
	1970-09-17		17.09.1970
	1960-09-28		28.09.1960
	1970-07-22		22.07.1970
	1970-09-02		02.09.1970
	1970-05-23		23.05.1970
	1967-03-22		22.03.1967
	1967-07-12		12.07.1967

10 rows in set (0.00 sec)
mysql>

Beispiel 2

mysql> select hochzeit,date_format(hochzeit,'Heute ist %W,der %d %M %Y')
-> from hochzeiten;

	hochzeit		date_format(hochzeit,'Heute ist %W,der %d %M %Y')
	1999-05-12		Heute ist Wednesday,der 12 May 1999
	1990-04-09		Heute ist Monday,der 09 April 1990
	2000-09-03		Heute ist Sunday,der 03 September 2000
	1970-09-17		Heute ist Thursday,der 17 September 1970
	1960-09-28		Heute ist Wednesday,der 28 September 1960
	1970-07-22		Heute ist Wednesday,der 22 July 1970
	1970-09-02		Heute ist Wednesday,der 02 September 1970
	1970-05-23		Heute ist Saturday,der 23 May 1970
	1967-03-22		Heute ist Wednesday,der 22 March 1967
	1967-07-12		Heute ist Wednesday,der 12 July 1967

10 rows in set (0.00 sec)
mysql>

Das ist soweit ganz nett, aber leider auf Englisch, was natürlich uncool ist, aber wohl nicht zu ändern. Wenn eine Programmiersprache z.B. Perl oder PHP dazwischen ist, was im Internetumfeld ja immer der Fall ist, ist es natürlich ziemlich einfach, das Datum ins Deutsche zu übertragen. Man macht dann einfach einen Hash,
der als Index den englischen Namen trägt und als Wert den deutschen. Man ruft dann den Hash auf und übergibt als Index den englischen Namen und erhält so den deutschen Namen. Für manche, häufig benötigte Bestandteile einer Datumsangabe, gibt es auch spezielle Funktionen:year(),dayofmonth,hour(),second() und
dayofyear(). Sieht man mal von dayofyear ab (diese Funktione ermittelt den Tag innerhalb eines Jahres, der zu einem bestimmten Datum gehört, z.B. 03.05.2000 ist dann etwa 66) sind die Funktionen selbsterklärend.

Die Funktionen year(), dayofmonth(), hour(), second(), dayofyear()

nach oben

mysql> select hochzeit,year(hochzeit),dayofmonth(hochzeit),
-> dayofyear(hochzeit) from hochzeiten;

	hochzeit		year(hochzeit)		dayofmonth(hochzeit)		dayofyear(hochzeit)
	1999-05-12		1999		12		132
	1990-04-09		1990		9		99
	2000-09-03		2000		3		247
	1970-09-17		1970		17		260
	1960-09-28		1960		28		272
	1970-07-22		1970		22		203
	1970-09-02		1970		2		245
	1970-05-23		1970		23		143
	1967-03-22		1967		22		81
	1967-07-12		1967		12		193

10 rows in set (0.00 sec)
mysql>

Ermitteln aller Datensätze die vor X Minuten eingetragen wurden

nach oben

Es sind Anwendungen vorstellbar, wo man sich dafür interessiert, wer sich in den letzten drei Stunden eingetragen hat. Denkbar sind aber auch Anwendungen,
wo man sich dafür interessiert, wer sich in den letzten fünf Minuten nicht eingetragen hat. Dafür interessiert man sich zum Beispiel bei einem Chat, weil man dann ja alle die User wieder rausschmeissen muss, die seit fünf Minuten inaktiv sind. Wir brauchen also erstmal die aktuelle Zeit. Um das aktuelle Datum zu ermitteln, bietet uns mysql drei Funktionen.

mysql> select now() as 'aktuelles Datum und Uhrzeit',
-> curdate() as 'aktuelles Datum',
-> curtime() as 'aktuelle Zeit';

	aktuelles Datum und Uhrzeit		aktuelles Datum		aktuelle Zeit
	2003-01-21 23:11:03		2003-01-21		23:11:03

1 row in set (0.05 sec)
mysql>

Um zu zeigen, wer sich in den letzten 10 Minuten eingetragen, bzw. nicht eingetragen hat, benötigt unsere Tabelle hochzeiten noch einen aktuellen Datensatz. Da dies
wiederum ein fehleranfälliger Prozess ist, lösen wir das sql Statement über einen batch Prozess aus,
siehe Daten aus einem Flatfile in Tabellen importieren. Wir öffnen Wordpad und schreiben dieses sql Statement, dass wir dann mit dem Namen hochzeit.sql abspeichern.

use hochzeiten;

insert into hochzeiten (Name,Geburtstag,Hochzeit,erstes_kind,erster_seitensprung)
values ('Julio Iglesias','1945-11-04','1975-08-06',1976-11-23','1976-11-08 12:30:30');

Anschliessend lösen wir es aus.

C:\mysql_handbuch>c:\mysql\bin\mysql.exe <hochzeit.sql

Wir haben jetzt also Einträge, die vor mehreren Stunden erstellt wurden und einen Eintrag, der erst vor kurzem generiert wurde. Wir wollen jetzt ermitteln, welcher Eintrag innerhalb der letzten Stunde erstellt wurde.

mysql> select name,eingetragen_am,now() from hochzeiten where
-> now()<date_add(eingetragen_am,interval 1 hour);

	name		eingetragen_am		now()
	Julio Iglesias		20030122103700		2003-01-22 11:13:36

1 row in set (0.00 sec)
mysql>

Um es etwas übersichtlicher und besser kontrollierbar zu machen, können wir das Datum des Eintrages noch formatieren.

mysql> select name,date_format(eingetragen_am,'%d-%M-%Y %H:%i:%s') as
-> 'Eingetragen am',date_format(now(),'%d-%M-%Y %H:%i:%s') as
-> 'aktuelles Datum' from hochzeiten
-> where now()<date_add(eingetragen_am,interval 2 hour);

	name		eingetragen_am		aktuelles Datum
	Julio Iglesias		22-January-2003 10:37:00		22-January-2003 12:00:29

1 row in set (0.06 sec)
mysql>

Entscheidend ist hier die Funktion date_add(). Diese addiert zu einem bestimmten Datum, welches der Funktion als erster Parameter übergeben wird, einen bestimmten Intervall. Die Funktion date_sub subtrahiert einen bestimmten Intervall. Folgende Intervalle sind möglich.

Beispiel: plus Sekunden

mysql> select date_format(now(),'%d-%M-%Y %H:%i:%s') as 'Aktuelle Zeit',
-> date_format(date_add(now(),interval 300 second),'%d-%M-%Y %H:%i:%s')
-> as 'plus 5 Minuten';

	Aktuelle Zeit		plus 5 Minuten
	22-January-2003 16:10:17		22-January-2003 16:15:17

1 row in set (0.00 sec)
mysql>

Beispiel: plus 2 Tage 5 Stunden

mysql> select date_format(now(),'%d-%M-%Y %H:%i:%s') as 'Aktuelle Zeit',
-> date_format(date_add(now(),interval '2 15' DAY_HOUR),'%d-%M-%Y %H:%i:%s')
-> as 'plus 2 Tage 15 Stunden';

	Aktuelle Zeit		plus 2 Tage 15 Stunden
	22-January-2003 16:26:03		25-January-2003 07:26:03

1 row in set (0.00 sec)
mysql>

Beispiel: plus 4 Tage , 5 Stunden 46 Minuten und 30 Sekunden

mysql> select date_format(now(),'%d-%M-%Y %H:%i:%s') as 'Aktuelle Zeit',
-> date_format(date_add(now(),interval "4 5:46:30" DAY_SECOND),
-> "%d-%M-%Y %H:%i:%s") as 'plus die ganze Zeit';

	Aktuelle Zeit		plus die ganze Zeit
	22-January-2003 17:25:34		26-January-2003 23:12:04

1 row in set (0.00 sec)
mysql>

Differenzen zwischen zwei Datumangaben berechnen

nach oben

Angenommen wir interessieren uns dafür, wie lange es gedauert hat, bis aus einer Ehe ein Kind hervorging. Das ist schwieriger als man zuerst meint. Man muss die Spalten hochzeit und erstes_kind in Sekunden oder Tage umwandeln, von einander subtrahieren und das Ergebnis in Tage zurückkonvertieren.

Die Funktion to_days() ermittelt die Anzahl der Tage, die vom Jahre 0 bis zu dem entsprechenden Datum verflossen sind. Bedingt durch die Tatsache, dass der Greogrianische Kalender erst 1582 eingeführt wurde, ist der Wert falsch. Das ist aber beim Vergleich von zwei Datumsangaben nach 1582 egal, da beide den gleichen Fehler haben. Vergleicht man mit einem Jahr ist das Ergebnis richtig. Dass die Funktion richtig rechnet, zeigt dieses Beispiel.

mysql> select now() as 'aktuelles Datum',date_sub(now(),interval 365 day)
-> as 'Datum vor 365 Tagen',
-> to_days(now())-to_days(date_sub(now(),interval 365 day)) as
-> 'Differenz';

	aktuelles Datum		Datum vor 365 Tagen		Differenz
	2003-01-22 19:38:35		2002-01-22 19:38:35		365

1 row in set (0.00 sec)
mysql>

Zieht man vom heutigen Datum 365 Tage ab, ist die Differenz zwischen dem heutigen Datum und dem Datum, dass sich ergibt, wenn man 365 Tage abzieht, 365 Tage. Nun gut, die Erkenntniss ist nicht gerade überraschend, aber das Beispiel zeigt auch nochmal die Syntax der Funktion to_days().
Die Lösung für unser Problem sieht dann so aus.

mysql> select hochzeit as 'Tag der Hochzeit',
-> erstes_kind as 'Geburt des ersten Kindes',
-> to_days(hochzeit)-to_days(erstes_kind) as 'Differenz'
-> from hochzeiten limit 3;

	Tag der Hochzeit		Geburt des ersten Kindes		Differenz
	1999-05-12		1998-03-11 13:45:30		427
	1990-04-09		1991-06-12 08:16:12		-429
	2000-09-03		1995-07-11 12:45:16		1881

3 rows in set (0.00 sec)
mysql>

Die Funktionen unix_timestamp und from_unixtime

nach oben

Will man mit Sekunden arbeiten, muss man mit der Funktion unix_timestamp() arbeiten. unix_timestamp() ermitteld die Anzahl der Sekunden, die seit dem 01.01.1970 bis zum angegebenen Datum verflossen sind. Besonders praktisch bei dieser Variante ist die Tatsache, dass es auch eine umgekehrte Funktion gibt, die aus den Sekunden wieder ein Datum generiert.

mysql> select date_format(hochzeit,'%d-%M-%Y %H:%i:%s') as 'Hochzeit',
-> unix_timestamp(hochzeit) as 'in Sekunden seit 1.1.1979',
-> from_unixtime(unix_timestamp(hochzeit)) as 'zurückkonvertiert'
-> from hochzeiten limit 2;

	Hochzeit		in Sekunden seit 1.1.1979		zurückkonvertiert
	12-May-1999 00:00:00		926460000		1999-05-12 00:00:00
	09-April-1990 00:00:00		639612000		1990-04-09 00:00:00

2 rows in set (0.55 sec)
mysql>

Wir erhalten hier bei Zeit 00:00:00, weil die Spalte hochzeit vom Type date und nicht vom Typ datetime ist.
Um die Differenz zwischen zwei Datumangaben via Sekunden zu ermitteln, die Sekunden sind ja nicht besonders aussagekräftig, benötigt man dann noch eine Funktion, die Sekunden zurückkonvertiert in Tage oder Jahre. Das Problem ist, dass es eine Funktion, die Sekunden zurückrechnet in Wochen:Tage:Stunden:Minuten:Sekunden nicht gibt. Im Gegensatz zu Monaten oder Jahren, sind diese Werte aber immer gleich, lassen sich also, wenn
auch nur mit einem ziemlichen Aufwand, berechnen. Für unser Beispiel, Berechnung der Differenz zwischen Hochzeit und erstem Kind, sähe das so aus.

mysql> select (unix_timestamp(hochzeit)-unix_timestamp(erstes_kind)) as 'Sek',
-> (unix_timestamp(hochzeit)-unix_timestamp(erstes_kind))/60 as 'Min',
-> (unix_timestamp(hochzeit)-unix_timestamp(erstes_kind))/(60*60) as 'Stu.',

-> (unix_timestamp(hochzeit)-unix_timestamp(erstes_kind))/(60*60*24) as
-> 'Tage',
-> (unix_timestamp(hochzeit)-unix_timestamp(erstes_kind))/(60*60*24*7) as
-> 'Wochen' from hochzeiten limit 2;

	Sek		Min		Stu.		Tage		Wochen
	36839670		613994.50		10233.24		426.39		60.91
	-37095372		-618256.20		-10304.27		-429.34		-61.33

2 rows in set (0.00 sec)
mysql>

Die Ergebnisse weichen von denen, die mit to_days berechnet wurden ab.

Die Ergebnisse aus unix_timestamp sind hierbei präziser, da bei der Methode to_days ja gerundet wird.

Alle Datensätze ermitteln, bei denen vor 1975 geheiratet wurde
Wenn man nur wissen will, wer vor 1975 geheiratet hat, ist das relativ einfach. Schwieriger wäre es, genau zu ermitteln, wie lange diese Leute schon verheiratet sind, weil dann Schaltjahre etc. berüchsichtig werden müssten.

mysql> select Name,hochzeit as 'Hochzeit'
-> from hochzeiten
-> where Year(hochzeit) < 1975;