Werbung einblenden Werbung ausblenden


Home / Tutorials / mysql Handbuch / Manipulationen mit Datumsangaben


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

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.
Typ Format Bedeutung
1 DATETIME '0000-00-00 00:00:00' speichert Datum und Uhrzeit
2 DATE '0000-00-00' speichert nur Datum
3 TIMESTAMP 00000000000000 (length depends on display size) Automatische Generierung des Datums. Das Format weicht ab von den anderen Formaten.
4 TIME '00:00:00' speichert nur die Uhrzeit
5 YEAR 0000 speichert nur das Jahr

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

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.

TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY

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

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 (||).

Erich Mühsam || 1979-12-24 || 1999-05-12 || 1998-03-11 13:45:30 || 1998-04-12
Gottfried Keller || 1955-11-12 || 1990-04-09 || 1991-06-12 08:16:12 || 1991-05-22
Gottfried Benn || 1965-08-09 || 2000-09-03 || 1995-07-11 12:45:16 || 1999-12-02
Else Lasker Schüler || 1958-06-20 || 1970-09-17 || 1971-05-07 15:15:23 || 1980-05-12
Erika Mann || 1940-07-11 || 1960-09-28 || 1991-03-22 14:13:55 || 1959-05-18
Jane Austen || 1956-02-23 || 1970-07-22 || 1992-08-21 12:33:56 || 1971-11-02
Marcel Proust || 1944-01-29 || 1970-09-02 || 1971-11-18 13:44:34 || 1967-09-22
Emile Zola || 1953-12-11 || 1970-05-23 || 1969-12-22 16:56:30 || 1975-01-12
Heinrich Heine || 1957-08-08 || 1967-03-22 || 1957-07-21 18:57:31 || 1980-03-12
Edgar Kornemann || 1957-08-08 || 1967-07-12 || 1980-11-01 19:02:01 || 1980-03-12

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;

Name Geburtstag Hochzeit erstes_kind erster_seitensprung
Erich Mühsam 1979-12-24 1999-05-12 1998-03-11 13:45:30 1998-04-12
Gottfried Keller 1955-11-12 1990-04-09 1991-06-12 08:16:12 1991-05-22
Gottfried Benn 1965-08-09 2000-09-03 1995-07-11 12:45:16 1999-12-02
Else Lasker Schüler 1958-06-20 1970-09-17 1971-05-07 15:15:23 1980-05-12
Erika Mann 1940-07-11 1960-09-28 1991-03-22 14:13:55 1959-05-18
Jane Austen 1956-02-23 1970-07-22 1992-08-21 12:33:56 1971-11-02
Marcel Proust 1944-01-29 1970-09-02 1971-11-18 13:44:34 1967-09-22
Emile Zola 1953-12-11 1970-05-23 1969-12-22 16:56:30 1975-01-12
Heinrich Heine 1957-08-08 1967-03-22 1957-07-21 18:57:31 1980-03-12
Edgar Kornemann 1957-08-08 1967-07-12 1980-11-01 19:02:01 1980-03-12

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

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.

%M Name des Monats ausgeschrieben (January..December)
%W Wochentag ausgeschrieben (Sunday..Saturday)
%D Tag im Monat mit Suffix (1st, 2nd, 3rd, etc.)
%Y Das Jahr vierstellig %Y (2003) oder zweistellig %y (03)
%a Abkürzung Wochentag (Sun..Sat)
%d Nummerische Angabe des Tages innerhalb des Monats, zweistellig (00..31)
%e Nummerische Angabe des Tages innerhalb des Monats, einstellig (0..31)
%m Nummerische Angabe des Monats, zweistellig (01..12)
%c Nummerische Angabe des Monats, einstellig (1..12)
%b Abgekürzt Monatsname (Jan..Dec)
%j Angabe des Tages innerhalb des Jahres (001..366)
%H Angabe der Stunde, zweistellig, Tag mit 24 Stunden (00..23)
%k Angabe der Stunde, einstellig,Tag mit 24 Stunden (0..23)
%i Minuten (00..59)
%r Zeit, 2 X 12 Stunden (hh:mm:ss [AP]M)
%T Zeit , 24 Stunden (hh:mm:ss)
%S Sekunden (00..59)
%s Seconds (00..59)

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()


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

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.

curdate() Ermittelt das aktuelle Datum (Jahr-Monat-Tag)
curtime() Ermittelt die aktuelle Zeit (Stunde:Minute:Sekunde)
now() Ermittelt Datum und Zeit (Jahr-Monat-Tag Stunde:Minute:Sekunde

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.

Angabe des Intervalls Was ermittelt wird Wie man es angibt
SECOND Sekunden SECONDS (z.B. 33)
MINUTE Minuten MINUTES (z.B. 16)
HOUR Stunden HOURS (z.B. 5)
DAY Tage DAYS (z.B. 8)
MONTH Monate MONTHS (z.B. 9)
YEAR Yahre YEARS (z.B. 7)
MINUTE_SECOND Minuten und Sekunden "MINUTES:SECONDS" (z.B. 30:12)
HOUR_MINUTE Stunden und Minuten "HOURS:MINUTES"(z.B. 5:44)
DAY_HOUR Tage und Stunden DAYS HOURS (z.B. 5 24)
YEAR_MONTH Jahre und Monate "YEARS-MONTHS" (z.B. 4-8)
HOUR_SECOND Stunden und Monate "HOURS:MINUTES:SECONDS"(z.B. 5:55:30)
DAY_MINUTE Tage,Stunden,Minuten "DAYS HOURS:MINUTES" (z.B. 4 23:46)
DAY_SECOND Tage,Stunden,Minuten,Sekunden "DAYS HOURS:MINUTES:SECONDS" (z.B. 7 19:44:30)

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

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.

Konvertieren eines Datums in Tage

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

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.

Datensatz Ergebnis aus Berechnung mit to_days() Berechnung aus Ergebnis mit unix_timestamp()
1 427 426.39
2 -429 -429.34

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;

Name Hochzeit
Else Lasker Schüler 1970-09-17
Erika Mann 1960-09-28
Jane Austen 1970-07-22
Marcel Proust 1970-09-02
Emile Zola 1970-05-23
Heinrich Heine 1967-03-22
Edgar Kornemann 1967-07-12

7 rows in set (0.00 sec)
mysql>

Alle Datensätze der Leute ermitteln, die zwischen 1965 und 1970 geheiratet haben

mysql> select Name,hochzeit as 'Hochzeit'
-> from hochzeiten
-> where year(hochzeit) between 1965 and 1972;

Name Hochzeit
Else Lasker Schüler 1970-09-17
Jane Austen 1970-07-22
Marcel Proust 1970-09-02
Emile Zola 1970-05-23
Heinrich Heine 1967-03-22
Edgar Kornemann 1967-07-12

6 rows in set (0.06 sec)
mysql>

Alle Datensätze der Leute ermitteln, die bei Ihrer Hochzeit älter als 30 Jahre waren

mysql> select Name,Geburtstag from hochzeiten
-> where year(hochzeit)-year(geburtstag)>30;

Name Geburtstag
Gottfried Keller 1955-11-12
Gottfried Benn 1965-08-09

2 rows in set (0.06 sec)
mysql>

Alle Leute ermitteln, die heute älter als 40 sind

mysql> select Name,Geburtstag from hochzeiten
-> where year(now()) - year(geburtstag)>40;

Name Geburtstag
Gottfried Keller 1955-11-12
Else Lasker Schüler 1958-06-20
Erika Mann 1940-07-11
Jane Austen 1956-02-23
Marcel Proust 1944-01-29
Emile Zola 1953-12-11
Heinrich Heine 1957-08-08
Julio Iglesias 1945-11-04

8 rows in set (0.00 sec)
mysql>

if Bedingung in sql statements

Ermitteln, wie alt die Leute genau sind, die heute älter als vierzig sind
Hierfür verwenden wir eine if Bedingung. Die Syntax ist.
if ([Bedingung], wenn true tu dies, wenn false tu jenes)

Wir können also nach der if Bedingungen zwei, durch ein Komma getrennte Anweisungen formulieren, von denen die erste ausgeführt wird, wenn die Bedingung wahr ist und die zweite, wenn sie nicht wahr ist.

mysql> select
-> if(month(now())>month(geburtstag),
-> year(now())-year(geburtstag),
-> year(now())-year(geburtstag)-1)
-> as 'Alter'
-> from hochzeiten;

lAlter
23
47
37
44
62
46
58
49
45
36
57

11 rows in set (0.00 sec)
mysql>

Der Fall ist also komplizierter als man meint. Ist jemand im November geboren und der aktuelle Monat ist Februar, dann ist das Jahr noch nicht voll. Folglich muss
es abgezogen werden. Würde man es nicht tun, hätte man sowas.

mysql> select
-> name as 'Name',
-> date_format(geburtstag,'%d-%M-%Y') as 'Geburtstag',
-> date_format(now(),'%d:%M:%Y') as 'aktuelles Datum',
-> year(now())-year(geburtstag) as 'Alter'
-> from hochzeiten limit 2;

Name Geburtstag aktuelles Datum Alter
Erich Mühsam 24-December-1979 23:January:2003 24
Gottfried Keller 12-November-1955 23:January:2003 48

2 rows in set (0.00 sec)
mysql>

Wie deutlich zu erkennen, wird Erich Mühsam erst im Dezember des Jahres 2003 vierundzwanzig. Es muss also ein Jahr abgezogen werden. Wer es ganz genau
braucht, der müsste auch Tage, Stunden, Minute, Sekunden berücksichtigen.

vorhergehendes Kapitel