Geburtstag - welches Format in MySQL?!

[mgutt]

@ jan500

na dann filter mal an hand des timestamps wer alles am 01.01. geburtstag hat

gruß

[Pyramide]

Wenn es um die reine Performance für die Abfrage "wer hat heute Geburtstag" geht, sind drei Felder und ein Index auf Monat+Tag am besten, da MySQL dann einen einfachen A=B Vergleich auf 1 Byte Werte durchführen muss. Das nächstschnellste wäre dann ein DATE Feld, weil MySQL damit direkt rechnen kann (z.B. MONTH() und DAYOFMONTH()). Ausserdem kann man damit auch einfach das Alter errechnen bzw. danach sortieren.

Einige Beispiele dafür liefert auch das MySQL-Handbuch: http://dev.mysql.com/doc/refman/5.1/de/ ... tions.html

[easygo]

Jo den Link hatten wir schon :/ easy

[mgutt]

Na dann bleibe ich doch glatt mal bei den vier Feldern. Wer weiß, vielleicht will ich irgendwann mal wissen, wer alles im August 2000 Geburtstag hat

Danke!

[mgutt]

Toll, jetzt habe ich festgestellt, dass bei 1901 schluss ist:

Anmerkung: Der gültige Bereich eines Timestamp liegt typischerweise zwischen Fri, 13 Dec 1901 20:45:54 GMT und Tue, 19 Jan 2038 03:14:07 GMT. (Das entspricht den minimalen und maximalen Werten für einen vorzeichenbehafteten 32-Bit Integer). Unter Windows-Betriebssytemen ist dieser Bereich auf 01-01-1970 bis 19-01-2038 beschränkt.

Ich hatte schon gedacht es liegt an der Datenbank (INT -> BIGINT), aber mktime() und date() produzieren leider keine korrekten Timestamps mehr.

Gibt es das Problem auch unter PHP5?

Code: Alles auswählen

<?php
// resultiert -14462 bei PHP4
echo(mktime(0, 0, 0, 1, 1, 1900));
?>

Hat jemand eine eigene mktime() Funktion parat, so dass ich mir die Timestamps erstellen könnte?


EDIT:
Ich habe mal mit einer solchen Funktion begonnen:

Code: Alles auswählen

<?php
function mk_time($hour=0, $min=0, $sec=0, $mon=0, $day=0, $year=0)
{
	/*
	if ( $year > 1901 && $year < 2038 )
	{
		return mktime($hour, $min, $sec, $mon, $day, $year);
	}
	*/
	// add one day for a leap year
	$leap = 0;
	if ( ($year % 4 == 0 && $year % 100 != 0) || $year % 400 == 0 )
	{
		$leap = 1;
	}
	$month = array(
		0 => 0,
		1 => 31,
		2 => 59 + $leap,
		3 => 90 + $leap,
		4 => 120 + $leap,
		5 => 151 + $leap,
		6 => 181 + $leap,
		7 => 212 + $leap,
		8 => 243 + $leap,
		9 => 273 + $leap,
		10 => 304 + $leap,
		11 => 334 + $leap
	);
	// leap years
	$leap = 0;
	if ( $year != 1970 )
	{
		if ( $year > 1970 )
		{
			for ($i = $year - 1; 1970 < $i; $i--)
			{
				if ( ($i % 4 == 0 && $i % 100 != 0) || $i % 400 == 0 )
				{
					$leap++;
				}
			}
			// dst
			if ( $year >= 1980 && $year <= 1990 && $mon >= 3 && $mon <= 9 )
			{
				$ymdhms = sprintf('%04d%02d%02d%02d%02d%02d', $year, $mon, $day, $hour, $min, $sec);
				if ( $ymdhms >= 19800406025959 && $ymdhms <= 19800928015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19810329025959 && $ymdhms <= 19810927015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19820328025959 && $ymdhms <= 19820926015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19830327025959 && $ymdhms <= 19830925015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19840325025959 && $ymdhms <= 19840930015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19850331025959 && $ymdhms <= 19850929015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19860330025959 && $ymdhms <= 19860928015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19870329025959 && $ymdhms <= 19870927015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19880327025959 && $ymdhms <= 19880925015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19890326025959 && $ymdhms <= 19890924015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19900325025959 && $ymdhms <= 19900930015959 )
				{
					$hour = $hour - 1;
				}
			}
			else if ( $year >= 1991 && $mon >= 3 && $mon <= 10 )
			{
				$ymdhms = sprintf('%04d%02d%02d%02d%02d%02d', $year, $mon, $day, $hour, $min, $sec);
				if ( $ymdhms >= (($year * 10000000000) + 325025959) && $ymdhms <= (($year * 10000000000) + 1029015959) )
				{
					$hour = $hour - 1;
				}
			}
		}
		else
		{
			for ($i = $year; $i < 1969; $i++)
			{
				if ( ($i % 4 == 0 && $i % 100 != 0) || $i % 400 == 0 )
				{
					$leap--;
				}
			}
			// dst
			if ( $year >= 1916 && $year <= 1918 && $mon >= 4 && $mon <= 9 )
			{
				$ymdhms = sprintf('%04d%02d%02d%02d%02d%02d', $year, $mon, $day, $hour, $min, $sec);
				if ( $ymdhms >= 19160430235959 && $ymdhms <= 19160930235959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19170416025959 && $ymdhms <= 19170917015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19180415025959 && $ymdhms <= 19180916015959 )
				{
					$hour = $hour - 1;
				}
			}
			else if ( $year >= 1940 && $year <= 1942 )
			{
				$ymdhms = sprintf('%04d%02d%02d%02d%02d%02d', $year, $mon, $day, $hour, $min, $sec);
				if ( $ymdhms >= 19400401025959 && $ymdhms <= 19421102015959 )
				{
					$hour = $hour - 1;
				}
			}
			else if ( $year >= 1943 && $year <= 1949 && $mon >= 3 && $mon <= 11 )
			{
				$ymdhms = sprintf('%04d%02d%02d%02d%02d%02d', $year, $mon, $day, $hour, $min, $sec);
				if ( $ymdhms >= 19430329025959 && $ymdhms <= 19431004015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19440403025959 && $ymdhms <= 19441002015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19450402025959 && $ymdhms <= 19451118015959 )
				{
					$hour = $hour - 1;
					if ( $ymdhms >= 19450530235959 && $ymdhms <= 19450922235959 )
					{
						$hour = $hour - 1;
					}
				}
				else if ( $ymdhms >= 19460414025959 && $ymdhms <= 19461007015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19470406025959 && $ymdhms <= 19471005015959 )
				{
					$hour = $hour - 1;
					if ( $ymdhms >= 19470510235959 && $ymdhms <= 19470628235959 )
					{
						$hour = $hour - 1;
					}
				}
				else if ( $ymdhms >= 19480418025959 && $ymdhms <= 19481003015959 )
				{
					$hour = $hour - 1;
				}
				else if ( $ymdhms >= 19490410025959 && $ymdhms <= 19491002015959 )
				{
					$hour = $hour - 1;
				}
			}
		}
	}
	return (($hour - 1) * 3600) + (($min) * 60) + $sec + (($month[ $mon - 1 ] + $day - 1 + $leap) * 86400) + (($year - 1970) * 31536000);
}

for ($i = 0; $i <= 100; $i++)
{
	$hour = rand(0, 23);
	$min = rand(0, 59);
	$sec = rand(0, 59);
	$mon = rand(1, 12);
	$day = rand(1, 31);
	$year = rand(1800, 2200);
	$mk_time = mk_time($hour, $min, $sec, $mon, $day, $year);
	$mktime = mktime($hour, $min, $sec, $mon, $day, $year);
	$diff = $mktime - $mk_time;
	if ( $diff < 100000 && $diff > -100000 && $mk_time != $mktime )
	{
		echo('<b>Falsch!</b><br />');
		echo('mk_time: ' . $mk_time . '<br />');
		echo('mktime: ' . $mktime . '<br />');
		echo('Differenz: ' . $diff . '<br />');
		echo('Stunden: ' . $hour . '<br />');
		echo('Minuten: ' . $min . '<br />');
		echo('Sekunden: ' . $sec . '<br />');
		echo('Monate: ' . $mon . '<br />');
		echo('Tage: ' . $day . '<br />');
		echo('Jahre: ' . $year . '<br /><br />');
	}
}
?>

Das Problem ist dabei die Sommerzeitumstellung. mktime() scheint sich beim Server die Sommerzeitwerte zur Berechnung heranzuziehen.

D.h. im Fall einer Sommerzeit stimmen die Werte daher nicht.

Für die Berechnung des Geburtstages wäre das nicht weiter wichtig, aber ich wollte die Funktion "fehlerfrei" umsetzen.

Die Daten von 1916-1918, 1940-1949, 1980 - 1990 habe ich wie man sieht bereits berücksichtigt. Jetzt fehlt und eine Formel für die einheitliche Regelung seit 1991.

Danach dürfte meine mk_time() mit der mktime() identisch sein, nur dass sie keine Grenze in den Jahreszahlen kennt.

Gruß

[S2B]

Na dann bleibe ich doch glatt mal bei den vier Feldern. Wer weiß, vielleicht will ich irgendwann mal wissen, wer alles im August 2000 Geburtstag hat

Wenn du ein DATE-Feld verwenden würdest, dürfte das doch kein Problem sein. Ich verstehe irgendwie nicht ganz, warum du den Geburtstag unbedingt doppelt speichern willst, wenn es doch auch einfach geht (bzw. man die entsprechenden Werte sowieso aus den anderen berechnen kann, egal bei welcher Lösung). Auch ist es bei der Speicherung von Geburtstagen doch wirklich sinnlos, das ganze in Sekunden vor/nach 1.1.1970 abzuspeichern, oder?

[mgutt]

Es geht nur um die Geschwindigkeit.

D.h. für die Ausgabe unabhängig von der Nutzereinstellung (ymd, dmy, usw.) ist der Timestamp der schnellste.

Aber für die Erstellung des Dropdownmenüs beim Editieren ist dagegen die Einzelfeldlösung die schnellere.

Auf Grund der Anzahl der Abfrage schätze ich, dass der Timestamp wichtiger ist als die Einzelfelder.

Gehen wir mal von der Lösung aus, dass ich mit einem DATE-Feld arbeite und ich in einer Liste die jeweiligen Personen mit ihrem Geburtstag in verschiedenen Formaten ausgeben möchte.

Der Vorteil von DATE liegt ja auf der Hand:

Der Typ DATE erlaubt die Benutzung eines Datums ohne Zeitangabe. MySQL ruft DATE-Werte im Format 'YYYY-MM-DD' ab und zeigt sie auch so an. Der unterstützte Bereich liegt zwischen '1000-01-01' und '9999-12-31'.

Bisher brauche ich dazu aber nur:

Code: Alles auswählen

echo(date($format, $timestamp));

Bei einem DATE Feld könnte ich in der MySQL Abfrage das machen:

Code: Alles auswählen

UNIX_TIMESTAMP(datum) AS `timestamp`

Das fällt aber leider flach, weil UNIX_TIMESTAMP() die gleichen Grenzen kennt wie mktime().

Also könnte ich das Datum in der Abfrage formatieren:

Code: Alles auswählen

DATE_FORMAT(datum, $format) AS `formatiertes_datum`

Alternativ könnte ich in PHP versuchen das Datum zu formatieren mit:

Code: Alles auswählen

date($format, strtotime($datum));

Wobei strtotime die Formate unterstützt:

yyyy-mm-dd
yyyymmdd

Daher zurück auf meine Ausgangsfrage. Welche Variante wäre die schnellste bzw. wer sollte dann in Deinen Augen die Formatierung machen: MySQL oder PHP?

Meine Vermutung wäre jetzt PHP um die Datenbankabfragen möglichst kurz zu halten.

Wenn ich jetzt nur das DATE Feld benutze (YYYY-MM-DD), dann müsste ich noch wissen wie ich auf die Einzelfelder verzichten kann. Sollte ich im Falle, dass das Feld editiert werden soll, also wenn ich mein Dropdownmenü erstelle, die Daten so auslesen:

Code: Alles auswählen

YEAR(datum) AS `jahr`, MONTH(datum) AS `monat`, DAYOFMONTH(datum) AS `tag`

Ich denke das wäre dann tatsächlich eine brauchbare Lösung

Gruß

[S2B]

Bei der Verwendung einer DATE-Spalte wird dir gar nichts anderes übrig bleiben, als das Datumsformat von der Datenbank zurückgeben zu lassen, da strtotime() genauso mit Timestamps arbeitet wie jede PHP-Datumsfunktion. Ich meine aber gelesen zu haben, dass man die Datumsformatierung ohnehin über die Datenbank machen sollte, also wäre das eine gute Lösung für dein Problem.

[Miriam]

Zum Thema Schnelligkeit: Meiner Meinung nach sollte die DB die "Arbeit" übernehmen und (fast) fertige Antworten liefern. PHP ist dafür zulangsam.... (relativ)

[mgutt]

Gut

So sieht nun meine Abfrage aus:

Code: Alles auswählen

SELECT id, name,
CONCAT( DATE_FORMAT( datum, '%e. ' ), 
CASE DATE_FORMAT( datum, '%M' ) 
WHEN 'January'
THEN 'Januar'
WHEN 'February'
THEN 'Februar'
WHEN 'March'
THEN 'März'
WHEN 'April'
THEN 'April'
WHEN 'May'
THEN 'Mai'
WHEN 'June'
THEN 'Juni'
WHEN 'July'
THEN 'Juli'
WHEN 'August'
THEN 'August'
WHEN 'September'
THEN 'September'
WHEN 'October'
THEN 'Oktober'
WHEN 'November'
THEN 'November'
WHEN 'December'
THEN 'Dezember'
END , DATE_FORMAT( datum, ' %Y' ) ) AS formatiertes_datum
FROM personen

Ich hoffe das ist nicht zu "schlecht", dass ich drei mal DATE_FORMAT() aufrufen muss. Alternativ müsste ich sonst mit DAYOFMONTH(), MONTHNAME() und YEAR() arbeiten und den Monat dann replacen. Die Frage ist da eigentlich nur, ob drei mal die gleiche Funktion inkl. CASE Switch schneller ist, als drei verschiedene Funktion und das Arrayreplacing.

Gruß
Marc

EDIT:
Ok ich habe mich nun für diese Variante entschieden, die finde ich besser

Code: Alles auswählen

SELECT id, name, DAYOFMONTH( datum ) AS tag, MONTHNAME( datum  ) AS monat, YEAR( datum  ) AS jahr
FROM personen

Und dann erstelle ich das Datum mit der richtigen Übersetzung wie folgt:

Code: Alles auswählen

$formatiertes_datum = $row['tag'] . '. ' . $lang['datetime'][ $row['monat'] ] . ' ' . $row['jahr'];

Ich muss mir dann nur noch etwas überlegen wie ich bequem andere Datumsformate ausgeben lassen könnte.