Richtiges Datensatz-Updatedatum in einer SQL DB

[Hugo Egon Balder]

Hallo Forum!

Ich erstelle in jeder meiner SQL Datenbank Tabellen unabhängig von den anderen Spalten _immer_ die 3 Standardspalten ID, created und modified.

Das "created" wird bei der Erstellung eines neuen Datensatzes automatisch eingefügt. (Typ:'timestamp', Standard:'CURRENT_TIMESTAMP')

Das "modified" (Typ:'datetime') bleibt bei der Erstellung eines neuen Datensatzes leer und bei jeder Veränderung des Datensatzes wird es gemeinsam mit den Änderungen als prepared Statement mittels der SQLi-eigenen Syntax 'VALUES NOW()' eingefügt.

Soweit, sogut. Jetzt habe ich mir überlegt: Was ist, wenn mein in Deutschland ansässiger Provider auf die Idee kommt, seine Serverfarm aus Kostengründen von Deutschland nach Tschimbukistan auszulagern und die Serverzeit auf Lokalzeit Tschimbukistan umgestellt wird? Dann entspricht der Zeitstempel ja nicht der mitteleuropäischen Zeit.

Von diesem Standpunkt aus betrachtet: Ist es da nicht sinnvoller, bei _beiden_ Spalten den Typ 'datetime' zu nehmen, mit php eine gewünschte Zeitzone zu definieren und einen Timestamp zu erstellen und diesen dann an die DB weiterzugeben?

Das wäre zwar _etwas_ längerer Code aber unabhängig von Serverstandort/eingestellter Zeit am Server habe ich _immer_ die "richtige" Zeit, nämlich die anhand der von mir gewünschten Zeitzone.

Wie seht Ihr das? Wie löst Ihr das?

MfG

Hugo Egon Balder

[Fred Furunkelstein 2012]

Wie seht Ihr das? Wie löst Ihr das?

Was hast Du gegen
SELECT UTC_TIMESTAMP();

Fred

[dedlfix]

Tach!

Das "created" wird bei der Erstellung eines neuen Datensatzes automatisch eingefügt. (Typ:'timestamp', Standard:'CURRENT_TIMESTAMP')
Das "modified" (Typ:'datetime')

Warum nimmst du hier nicht ebenfalls den Typ TIMESTAMP? Abgesehen davon, dass er (derzeit) nur bis ins Jahr 2038 reicht, werden dort genauso wie beim created-Feld keine Zeiten vor 1970 abgelegt. Damit eignet er sich genauso gut oder schlecht für das modified-Feld. Das Jahr-2038-Problem ist eins, das MySQL sowieso lösen muss und dessen Lösung du auch für created benötigst. Deshalb würde ich mir darum grad keine Gedanken machen.

bleibt bei der Erstellung eines neuen Datensatzes leer und bei jeder Veränderung des Datensatzes wird es gemeinsam mit den Änderungen als prepared Statement mittels der SQLi-eigenen Syntax 'VALUES NOW()' eingefügt.

SQLi? Das i in mysqli ist ein reine PHP-Bezeichnung. Das ändert nichts am (My)SQL-Dialekt.

Was ist, wenn mein in Deutschland ansässiger Provider auf die Idee kommt, seine Serverfarm aus Kostengründen von Deutschland nach Tschimbukistan auszulagern und die Serverzeit auf Lokalzeit Tschimbukistan umgestellt wird? Dann entspricht der Zeitstempel ja nicht der mitteleuropäischen Zeit.

Dann hättest du mit TIMESTAMP statt DATETIME ein Problem weniger, denn (siehe Handbuch) TIMESTAMP speichert von sich aus in UTC und rechnet zwischen der aktuellen Zeitzoneneinstellung und UTC um. Wie MySQL mit den Zeitzonen umgeht steht in MySQL Server Time Zone Support. Es gibt also eine Per-Connection-Timezone-Einstellung. Diese Wert steht zunächst auf der Server-Zeitzone.

Wenn dein Hoster nun nach Tschimbukistan umzieht und noch dazu die Server-Zeitzonen auf lokal stellt, bekommst du schon mit deinem created-TIMESTAMP ein Problem, denn dein Client ist in dem Fall ebenfalls ein in Tschimbukistan stehender Rechner. Du müsstest also einerseits deinem PHP bekanntgeben, dass es ME(S)Z verwenden soll und nach jedem Verbindungsaufbau zum MySQL-Server ebenfalls die Verbindungs-Zeitzone auf deinen gewünschten Wert setzen. Damit klärst du das created-Problem, über das du noch gar nicht nachgedacht hast, zusammen mit dem modified-Problem, wenn du beides gleich behandelst.

Von diesem Standpunkt aus betrachtet: Ist es da nicht sinnvoller, bei _beiden_ Spalten den Typ 'datetime' zu nehmen, mit php eine gewünschte Zeitzone zu definieren und einen Timestamp zu erstellen und diesen dann an die DB weiterzugeben?

PHP verwendet den Unix-Timestamp. Das ist ein Wert, der die Sekunden seit 1.1.1970 auf UTC-Basis angibt. Das heißt, dass PHP von der aktuell eingestellten Zone nach UTC umrechnet und damit daselbe macht wie ein TIMESTAMP-Feld.

Das wäre zwar _etwas_ längerer Code aber unabhängig von Serverstandort/eingestellter Zeit am Server habe ich _immer_ die "richtige" Zeit, nämlich die anhand der von mir gewünschten Zeitzone.

Egal welchen Weg der beiden Wege du gehst, du hast immer eine standortunabhängige Zeit im Server. Mit dem Integer-Wert des Unix-Timestamps kann jedoch MySQL nichts anfangen, wenn du ihn nicht mit FROM_UNIXTIME() behandelst. Als Integer-Wert eignet er sich also nicht sehr gut, wenn du im DBMS damit Rechnungen und Recherchen anstellen willst.

dedlfix.

[Hugo Egon Balder]

Hallo Dedlfix!

Vielen Dank für Deine ausführliche Antwort! Ich sitze jetzt seit über 3 Stunden vor dem Laptop und lese eine Seite nach der anderen zu dem Thema - leider verwirrt mich das Thema dadurch von Minute zu Minute noch mehr.

Jetzt habe ich mir überlegt: Ich könnte doch sowohl bei CREATED, als auch bei MODIFIED den Typ 'Datetime' verwenden und bei der Speicherung/Änderung eines Datensatzes nehme ich statt dem NOW() im prepared Statement ein UTC_TIMESTAMP(). Dann habe ich systematisch - unabhängig von anderen Einstellungen am Server - _immer_ die UTC Zeit gespeichert.

Ich habe das jetzt gerade getestet. Es funktioniert einwandfrei. Es steht jetzt eine um 2 Stunden frühere Zeit in der DB.

Die Frage ist nur, wenn ich eine so gespeicherte Zeit jetzt mittels einer php Ressource ausgeben möchte - nehmen wir als Beispiel ein Gästebuch oder einen Blog - wie gehe ich denn dann vor, wenn im Endeffekt jene Zeit dastehen soll, die zu diesem Zeitpunkt in Mitteleuropa war?

MfG

Hugo Egon Balder

[dedlfix]

Tach!

Ich sitze jetzt seit über 3 Stunden vor dem Laptop und lese eine Seite nach der anderen zu dem Thema - leider verwirrt mich das Thema dadurch von Minute zu Minute noch mehr.

Es gibt zwei (sinnvolle) Wege. Dass du UTC im DBMS speichern solltest, steht wohl außer Frage. Du überlässt das Zeitzonenhandling dem DMBS, verwendest TIMESTAMPs und teilst beim Verbindungsaufbau immer mit, welche Zeitzone du verwendest. Dann rechnet MySQL die Zeit nach UTC um und wieder zurück. Du kannst aber auch das Zeitzonenhandling komplett selbst übernehmen, dann musst du selbst immer von und nach UTC umrechnen - und darauf achten, dass das DBMS nicht auch noch umrechnet (also TIMESTAMP vermeiden).

Jetzt habe ich mir überlegt: Ich könnte doch sowohl bei CREATED, als auch bei MODIFIED den Typ 'Datetime' verwenden und bei der Speicherung/Änderung eines Datensatzes nehme ich statt dem NOW() im prepared Statement ein UTC_TIMESTAMP(). Dann habe ich systematisch - unabhängig von anderen Einstellungen am Server - _immer_ die UTC Zeit gespeichert.

Jein. DATETIME hat für deinen Anwendungsfall einen viel zu hohen Wertebereich, aber sei's drum. Wenn du UTC_TIMESTAMP() nimmst, muss der Server ebenfalls korrekt konfiguriert sein. UTC_TIMESTAMP() kann nicht aus heiterem Himmel wissen, was die aktuelle Zeit ist, weder für UTC noch für lokal. Du musst dich darauf verlassen, dass der Server richtig konfiguriert ist. Unabhängigkeit vom DBMS bekommst du nur, wenn du DATETIME nimmst, was ja ohne Konvertierung speichert, und alle Zeit-Rechnungen und -Bestimmungen im Client vornimmst.

Ich habe das jetzt gerade getestet. Es funktioniert einwandfrei. Es steht jetzt eine um 2 Stunden frühere Zeit in der DB.

Dann scheint dein Server derzeit richtig konfiguriert zu sein.

Die Frage ist nur, wenn ich eine so gespeicherte Zeit jetzt mittels einer php Ressource ausgeben möchte - nehmen wir als Beispiel ein Gästebuch oder einen Blog - wie gehe ich denn dann vor, wenn im Endeffekt jene Zeit dastehen soll, die zu diesem Zeitpunkt in Mitteleuropa war?

Na, umrechnen musst du das dann schon selbst. Vom DBMS geliefert musst du den Wert in seine Einzelteile zerlegen, damit gmmktime() füttern (beachte das gm!), und dessen Ergebnis kannst du dann mit date() formatieren (date ohne gm). Für diese Umrechnung muss dein PHP-Server korrekt konfiguriert und mit PHP (d)eine Zeitzone gesetzt worden sein.

Soweit zum theoretischen Teil. Rein praktisch können wir sicherlich davon ausgehen, dass sowohl Datenbank- als auch Webserver in einer Weise konfiguriert worden sind, dass sie zumindest die UTC richtig berechnen können. Das heißt, dass die notwendigen Zeitzonendaten installiert sind. Auch muss deine Befürchtung nicht zutreffen. Wenn der Hoster nach T... umzieht, heißt das noch lange nicht, dass er auch die dortige Zeitzone in seinen Serveren einstellt, wenn er vorwiegend mitteleuropäische Kunden hat. Aber egal, selbst wenn du am Server an der Kommandozeile dir die Zeit ausgeben lässt und da was dortiges lokales angezeigt wird, muss dich das nicht weiter stören, wenn du stets angibst, welche Zeitzone du meinst. Du kannst also durchaus TIMESTAMP nehmen, beim Verbindungsaufbau deine Zeitzone festlegen und dann mit NOW() arbeiten. Intern abgelegt wird dann der zugehörige UTC-Wert. Beim Verbindungsaufbau zum Abfragen hast du wieder deine Zeitzone angegeben und bekommst nun den Wert in dieser Lokalzeit zurück. - Wobei mir gerade auffällt, dass es bei NOW() egal ist, welche Zeitzone eingestellt ist. Wenn es eine andere ist, wir eben von der nach UTC umgerechnet. Nur beim Abfragen musst du deine angeben. Also selbst wenn irgendwer anders ohne explizite Zeitzonenkonfiguration Daten ändert, sollte mit TIMESTAMP alles problemlos als UTC abgelegt werden.

Im Grunde genommen ist es gehüpft wie gesprungen. Wenn du sichergehen willst, musst du selbst die Zeitzoneneinstellungen individuell setzen und dich nicht auf Systemdefaulteinstellungen verlassen. Das heißt also, dass du mindestens unter PHP die Zone konfigurierst. Der einfachte Weg wäre nun der TIMESTAMP-Weg. Bei dem musst du lediglich beim Verbindungsaufbau zum DBMS (und das zwingend nur beim Abfragen) die Zeitzone setzen (neben der Konfiguration für die Zeichenkodierung). Alle Umrechnungen erfolgen im DBMS. Selbst nach deinem Wunsch formatiert kannst du die Datümer abfragen, so dass sie PHP nur noch durchreichen muss. Aufwendiger hingegen ist es, wenn du selbst mit PHP umrechnen musst. Sicherer als die TIMESTAMP-Lösung wird es dadurch auch nicht.

dedlfix.

[Hugo Egon Balder]

Hallo Dedlfix!

Du überlässt das Zeitzonenhandling dem DMBS, verwendest TIMESTAMPs und teilst beim Verbindungsaufbau immer mit, welche Zeitzone du verwendest.

Sowas in der Art habe ich eh schon gesucht, aber nicht gefunden. Mein momentaner Virbindungsaufbau (mit mysqli) sieht so aus:

$db=@new mysql('mysql5.example.com','db_foo','password','db_foo');  
$db->set_charset('utf8');

Was für ein Kommando muss ich da jetzt noch darunter schreiben, um meine gewünschte Zeitzone festzulegen? Und wäre das dann so, dass wenn ein Zeitpunkt als 14:00 UTC gespeichrt ist und ich hier zB. Athen als Zeitzone nehme, dann beim Abruf ein 17:00 ausgegeben wird? Oder hab ich das falsch verstanden?

Du kannst also durchaus TIMESTAMP nehmen, beim Verbindungsaufbau deine Zeitzone festlegen und dann mit NOW() arbeiten. Intern abgelegt wird dann der zugehörige UTC-Wert.

Es heißt dauernd, dass beim Timestamp in UTC gespeichrt wird. Aber wie kann das sein? Meine Timestamps sind immer aus meiner Zeitzone, also momentan mitteleuropäische Sommerzeit. Von einer UTC Speicherung sehe ich da weit und breit nichts. Wenn Timestamps in UTC speichern und ich erstelle um 18:00 Uhr einen neuen Datensatz, dann müsste doch beim Timestamp ein 16:00 Uhr stehen!?

Und noch etwas ist mir in diesem Zusammenhang unklar:

Wenn ich mit phpMyAdmin eine Timestamp Spalte erstelle und weder das Häkchen bei "Current-Timestamp" setze, noch bei den Extras das "auto_increment" einstelle, dann wird _trotzdem_ bei jedem neuen Datensatz automatisch ein Zeotstempel gesetzt. Wieso?

Danke für die Geduld und hoffentlich weitere Hilfe!

MfG

Hugo Egon Balder

[dedlfix]

Tach!

Du überlässt das Zeitzonenhandling dem DMBS, verwendest TIMESTAMPs und teilst beim Verbindungsaufbau immer mit, welche Zeitzone du verwendest.
Was für ein Kommando muss ich da jetzt noch darunter schreiben, um meine gewünschte Zeitzone festzulegen?

Soweit ich weiß, geht das mit einem Statement SET time_zone = timezone. Als Wert nimmst du sowas wie 'Europe/Berlin'. Der muss bekannt sein, aber üblicherweise reicht es, wenn du einen der allgemeinen Unix-Timezone-Datenbank-Werte angibst. Sie müssten mit denne übereinstimmen, die auch PHP kennt: [llink:http://www.php.net/manual/en/timezones.php@title=List of Supported Timezones].

Und wäre das dann so, dass wenn ein Zeitpunkt als 14:00 UTC gespeichrt ist und ich hier zB. Athen als Zeitzone nehme, dann beim Abruf ein 17:00 ausgegeben wird?

So ist das, zumindest zur Sommerzeit. So funktioniert der Unix-Timestamp, so funktioniert der TIMESTAMP unter MySQL.

Du kannst also durchaus TIMESTAMP nehmen, beim Verbindungsaufbau deine Zeitzone festlegen und dann mit NOW() arbeiten. Intern abgelegt wird dann der zugehörige UTC-Wert.

Es heißt dauernd, dass beim Timestamp in UTC gespeichrt wird. Aber wie kann das sein? Meine Timestamps sind immer aus meiner Zeitzone, also momentan mitteleuropäische Sommerzeit.

Die aktuelle Zeitzone ist dem System bekannt, wenn du sie ordentlich konfiguriert hast (oder auch der Systemadmin).

Von einer UTC Speicherung sehe ich da weit und breit nichts. Wenn Timestamps in UTC speichern und ich erstelle um 18:00 Uhr einen neuen Datensatz, dann müsste doch beim Timestamp ein 16:00 Uhr stehen!?

Beim gespeicherten Wert. Wenn du dir den aber anschauen willst, bekommst du ihn vermutlich gleich wieder in Lokalzeit umgerechnet.

Du kannst das mit PHP nachvollziehen. Nimm date_default_timezone_set() und stell dir da irgendwas ein. Erzeuge mit mktime() einen Timestamp und schau dir den entstehenden Integer an. Stell nun eine andere Zeitzone ein und gib dieselbe Zeit aber umgerechnet auf diese Zeitzone ein. Der entstehende Integer-Wert muss derselbe sein. Und nun lässt du dir den Integer-Wert mit gmdate() formatiert ausgeben, dann siehst du die Zeit in UTC. Die gm*-Date/Time-Funktionen arbeiten immer auf UTC-Basis.

Wenn ich mit phpMyAdmin eine Timestamp Spalte erstelle und weder das Häkchen bei "Current-Timestamp" setze, noch bei den Extras das "auto_increment" einstelle, dann wird _trotzdem_ bei jedem neuen Datensatz automatisch ein Zeotstempel gesetzt. Wieso?

auto_increment wäre auch ungültig und kann gleich gar nicht ungestraft gesetzt werden. Und nun wird's etwas komplex. Früher gab es den Mechanismus nur für das erste TIMESTAMP-Feld. Heute kann er bei einem beliebigen Feld verwendet werden, aber immer nur bei einem. Üblicherweise nimmt man ja gerade ein TIMESTAMP-Feld, weil man dessen Magie gut verwenden kann. Alles zum diesem Automatismus steht unter Automatic Initialization and Updating for TIMESTAMP. Wenn du beim ersten Feld nichts angibst, setzt MySQL per Default DEFAULT CURRENT_TIMESTAMP und ON UPDATE CURRENT_TIMESTAMP. Bei allen weiteren Felder macht es das nicht mehr, selbst wenn das erste Feld keine Magie konfiguriert hat. Wenn du beim ersten Feld einen von beiden Werte explizit angibst, wird der andere nicht gesetzt. Gibst du DEFAULT 0 an, ist die Magie komplett ausgeschaltet.

In deinem Fall würde ich mir die Magie beim Modified-Feld zunutze machen und dieses auf ON UPDATE CURRENT_TIMESTAMP stellen, das Created (wenn es das erste Feld ist) mit DEFAULT 0 konfigurieren und dann lediglich beim INSERT das Created beachten. Ein UPDATE zieht Modified automatisch nach.

dedlfix.

[Hugo Egon Balder]

Hallo Dedlfix!

Ich habe mich gestern noch bis in die Nacht hineien stundenlang mit dem Thema beschäftigt und sämtliche Variationen immer wieder durchgetestet, ich komme einfach nicht weiter.

Soweit ich weiß, geht das mit einem Statement SET time_zone = timezone.

Das war leider die falsche Syntax. Es kam eine Fehlermeldung. Ich hab dann Gottseidank eine Seite gefunden, wo geschrieben stand, wie ich das richtig zu formulieren habe. Also es wird nach dem $db->set_charset('utf8'); noch zusätzlich ein $db->query("set time_zone='+02:00'"); gesetzt. (Dann kommt keine Fehlermeldung mehr.)

Allerdings hat das leider auch absolut _Null_ Auswirkung auf die Speicherung und die Ausgabe der Timestamps. Egal, ob ich da jetzt als Zeitzone ein 'Europe/Athens', ein 'Europe/Berlin' ... ein '-02:00' oder ein '+04:00' setze - es veränder absolut _nichts_ am Geschehen.

Und wäre das dann so, dass wenn ein Zeitpunkt als 14:00 UTC gespeichrt ist und ich hier zB. Athen als Zeitzone nehme, dann beim Abruf ein 17:00 ausgegeben wird?
So ist das, zumindest zur Sommerzeit. So funktioniert der Unix-Timestamp, so funktioniert der TIMESTAMP unter MySQL.

Leider ist dem nicht so!

In der ersten TS Spalte, das ist die, die automatisch bei der Speicherung eines neuen Datensatzes den momentanen Zeitpunkt speichert, steht _immer_ die aktuelle mitteleuropäische Sommerzeit.

In der zweiten TS Spalte, steht bei Speicherung mit einem NOW() im Query _immer_ die aktuelle mitteleuropäische Sommerzeit und mit einem UTC_TIMESTAMP() im Query _immer_ die UTC Zeit. (Wobei letzteres das Einzige ist, das so geschieht, wie von mir erwartet.)

Und wie gesagt, die gespeicherten und ausgegeben Werte sind _immer_ so wie in den letzten 2 Absätzen beschrieben. Unabhängig davon, ob ich nach dem Verbindungsaufbau eine Zeitzone festlege oder nicht und unabhängig davon, was als Zeitzone festgelegt wird. [Mit Ausgabe meine ich nicht, was ich sehe, wenn ich mir den Tabelleninhalt in phpMyAdmin ansehe - dort steht das selbe übrigens, sondern wirklich das, was ich mir mit php/mysqli über den Browser ausgeben lasse!]

Ich bin also nicht einen Schritt weiter. :-(

Also wenn ich mit einem UTC_TIMESTAMP() im Query erreiche, dass ein aktueller UTC TS gespeichert wird, ist das ja eh gut. Dann stelle ich den Automatismus in der ersten TS Spalte ab, speichere bei beiden Spalten mit UTC_TIMESTAMP() und habe somit sowohl in meiner 'Created', als auch in meiner 'Modified' Spalte immer die UTC Zeit stehen.

Was mir immer noch fehlt ist jetzt die Lösung, wie dieser UTC TS bei Abruf einer Ressource als jene Zeit ausgegeben wird, die zu diesem Zeitpunkt in zB. Berlin oder zB. Athen gewesen ist.

Mir ist natürlich klar, dass ich mir den TS von php zerlegen lassen könnte und vor der Ausgabe manuell eine bestimmte Stundenzahl addiere oder subtrahiere. Nur würde das erstens die Sommerzeit nicht berücksichtigen und zweitens kann ich einfach nicht glauben, dass das der korrekte bzw. simpleste Weg ist. Es _muss_ doch eine Möglichkeit geben, dass mit einer kleinen Code-Veränderung der UTC gespeicherte Zeitpunkt als Zeitpunkt einer Zeitzone seiner Wahl korrekt ausgegeben wird.

Ich weiß echt nicht mehr weiter jetzt.

MfG

Hugo Egon Balder

PS: Was ich natürlich _nicht_ weiß, ist, ob das $db->query("set time_zone='+02:00'"); auch wirklich der richtige Weg zur Festlegung einer Zeitzone ist. Es wird zumindest, wie geschrieben, keine Fehlermeldung oder Warnung ausgegeben.

[dedlfix]

Tach!

Soweit ich weiß, geht das mit einem Statement SET time_zone = timezone.
Das war leider die falsche Syntax.

Nö, wars nicht, aber ...

Es kam eine Fehlermeldung. Ich hab dann Gottseidank eine Seite gefunden, wo geschrieben stand, wie ich das richtig zu formulieren habe.

Eine solche hatte ich auch verlinkt, da stand auch, dass man das als Stunden-Differenz angeben kann.

Also es wird nach dem $db->set_charset('utf8'); noch zusätzlich ein $db->query("set time_zone='+02:00'"); gesetzt. (Dann kommt keine Fehlermeldung mehr.)

SET liefert kein Resultset. Du kannst für dessen Aufruf die Methode exec() nehmen.

Nun zum obigen "aber". MySQL kann entgegen meiner ersten Annahme doch nicht direkt auf die Zeitzonendaten des Systems zugreifen. Ich überlas bisher diesen Satz: "Named time zones can be used only if the time zone information tables in the mysql database have been created and populated." Der steht auch auf obiger Seite und anschließend steht geschrieben, wie man diese Tabelle füllt. Du bist dazu auf den Administrator angewiesen, denn das ist eine Systemtabelle in der Datenbank "mysql". Erwähnt ist auch ein Script, welches die MySQL-Timezone-Tabellen-Daten aus dem Inhalt der System-Timezone-Daten generiert. Und das muss im Prinzip nach jeder Änderung der Zeitzonendaten erfolgen.

Allerdings hat das leider auch absolut _Null_ Auswirkung auf die Speicherung und die Ausgabe der Timestamps. Egal, ob ich da jetzt als Zeitzone ein 'Europe/Athens', ein 'Europe/Berlin' ... ein '-02:00' oder ein '+04:00' setze - es veränder absolut _nichts_ am Geschehen.

Was meinst du mit "Speicherung"? Die Speicherung kannst du nicht direkt anschauen, wenn dir das Binärformat der MySQL-Tabellen-Dateien nicht geläufig ist. Du kannst mit SQL immer nur das sehen, was MySQL dir zu geben bereit ist. Das kann unter Umständen auch bereits in irgendwas umgewandelt sein.

Definiere dein Geschehen. Bei mir hat sowohl mit der +/-0:00-Syntax als auch nach dem Füllen der Timezone-Tabellen mit Zonen-Namen alles bestens funktioniert.

SET time_zone='Europe/Berlin';
INSERT INTO test (t) VALUES ('1970-01-01 03:00:00');
SELECT t, UNIX_TIMESTAMP(t) FROM test;

Ausgabe; 1970-01-01 03:00:00, 7200

SET time_zone='UTC';
SELECT t, UNIX_TIMESTAMP(t) FROM test;

Ausgabe; 1970-01-01 02:00:00, 7200

SET time_zone='Europe/Athens';
SELECT t, UNIX_TIMESTAMP(t) FROM test;

Ausgabe; 1970-01-01 04:00:00, 7200

Der Unix-Timestamp-Wert ändert sich nicht, denn er gibt ja immer die Sekunden in UTC aus (7200 = 2 x 3600 Sekunden = 2 Uhr). Das Handbuch statiert der Funktion UNIX_TIMESTAMP() auch, dass sie auf ein TIMESTAMP-Feld angewendet keine Konvertierung vornimmt - wenn man die Ausgaben anschaut stimmt das augenscheinlich.

Und wäre das dann so, dass wenn ein Zeitpunkt als 14:00 UTC gespeichrt ist und ich hier zB. Athen als Zeitzone nehme, dann beim Abruf ein 17:00 ausgegeben wird?
So ist das, zumindest zur Sommerzeit. So funktioniert der Unix-Timestamp, so funktioniert der TIMESTAMP unter MySQL.
Leider ist dem nicht so!

Dann machst du vielleicht was falsch oder ziehst die falschen Schlüsse.

In der ersten TS Spalte, das ist die, die automatisch bei der Speicherung eines neuen Datensatzes den momentanen Zeitpunkt speichert, steht _immer_ die aktuelle mitteleuropäische Sommerzeit.
In der zweiten TS Spalte, steht bei Speicherung mit einem NOW() im Query _immer_ die aktuelle mitteleuropäische Sommerzeit und mit einem UTC_TIMESTAMP() im Query _immer_ die UTC Zeit. (Wobei letzteres das Einzige ist, das so geschieht, wie von mir erwartet.)

Nach welchem Vorgang und wie hast du das kontrolliert? "Steht" kannst du nicht wissen, wenn du nur SELECT darauf anwendest. Nimm UNIX_TIMESTAMP() zum Anzeigen der Sekunden. Zeige diese Sekunden dann mit einer UTC-Funktion (z.B. PHPs gmdate()) in einem lesbaren Format an.

Und wie gesagt, die gespeicherten und ausgegeben Werte sind _immer_ so wie in den letzten 2 Absätzen beschrieben. Unabhängig davon, ob ich nach dem Verbindungsaufbau eine Zeitzone festlege oder nicht und unabhängig davon, was als Zeitzone festgelegt wird. [Mit Ausgabe meine ich nicht, was ich sehe, wenn ich mir den Tabelleninhalt in phpMyAdmin ansehe - dort steht das selbe übrigens, sondern wirklich das, was ich mir mit php/mysqli über den Browser ausgeben lasse!]

Der PMA macht auch nur ein SELECT und hat einen Verbindungsaufbau, bei dem er ohne weiteres keine Zeitzone setzt. Bei mir sehe ich ohne vorheriges SET im PMA immer die Lokalzeiten. Über den Startbildschirm vom PMA kann man sich die Variablen anzeigen lassen, und sogar bearbeiten (Version 3.5.2.2). Ich sehe bei 'time zone' ein SYSTEM und bei 'system time zone' ein CEST. Bearbeite ich 'time zone' zu UTC, sehe ich beim einfachen Anzeigen nun die UTC-Werte. Man muss das aber nicht dort ändern, sondern kann das angezeigte SQL-Statement bearbeiten und ein 'SET time_zone...;' voranstellen.

Ich bin also nicht einen Schritt weiter. :-(

Du musst es nur richtig machen :-)

PS: Was ich natürlich _nicht_ weiß, ist, ob das $db->query("set time_zone='+02:00'"); auch wirklich der richtige Weg zur Festlegung einer Zeitzone ist. Es wird zumindest, wie geschrieben, keine Fehlermeldung oder Warnung ausgegeben.

Jein. Du hast in dieser Differenzangabe keine Sommerzeit-Information. Nimmst du +2:00 sind Zeiten außerhalb der Sommerzeit fehlerhaft und bei +1:00 die zur Sommerzeit. Du müsstest, um die richtige Differenz zu setzen, also immer vorher wissen, welche Zeit du abfragst - und das geht ja nicht. Du brauchst also die gefüllten Zeitzonentabellen, um das Zeitzonenhandling namensbasiert dem MySQL überlassen zu können. Sonst geht nur UTC-Speicherung in MySQL mit Lokal-Umrechnung in PHP.

dedlfix.

[Hugo Egon Balder]

Hallo Dedlfix!

Ich glaube, Du setzt bei mir um _Welten_ mehr Wissen und Verständnis voraus, als ich tatsächlich bieten kann. Ich bin jetzt an einem Punkt angelangt, wo ich mich _endgültig_ nicht mehr auskenne und resigniere.

Vor 3 Tagen habe ich gefragt: "Was für ein Kommando muss ich nach dem Verbindungsaufbau durch mysqli noch darunter schreiben, um meine gewünschte Zeitzone festzulegen?" Da sagtest Du: "Soweit ich weiß, geht das mit einem Statement 'SET time_zone = timezone'". OK, ich _war_ auf der Seite, dort steht auch nur die Syntax 'mysql> SET time_zone = timezone;'.  Ich bin völliger Anfänger und habe keine Ahnung, wo und wie das jetzt einzubauen ist. Darum habe ich auch extra nach der Syntax gefragt! Direkt zu verwenden, so, wie ich Dich ursprünglich verstanden habe, ist es jedenfalls _nicht_! Denn egal, ob ich die Verbindung so aufbaue…

$db=@new mysql('mysql5.example.com','db_foo','password','db_foo');  
$db->set_charset('utf8');  
mysql> SET time_zone = 'Europe/Athens';

…oder so…

$db=@new mysql('mysql5.example.com','db_foo','password','db_foo');  
$db->set_charset('utf8');  
SET time_zone='Europe/Athens';

…es kommt in beiden Fällen ein "Parse error: syntax error, unexpected T_STRING in…"! Gut, daraufhin habe ich das in verschiedensten Variationen bei Google eingegeben und fand dann eben irgendeine Seite, wo nach dem Verbindungsaufbau ein '$db->query("set time_zone='+02:00'");' stand. Das habe ich dann auch versucht und es kam keine Fehlermeldung mehr. Daraus habe ich geschlossen, dass _das_ offenbar die Syntax ist, in der ich das von Dir gemeinte 'SET time_zone = timezone' verwenden muss.

SET liefert kein Resultset. Du kannst für dessen Aufruf die Methode exec() nehmen.

Aha. Was heißt jetzt "kannst"? Muss ich oder muss ich nicht? Ich kenne mich überhaupt nicht mehr aus. Soll ich jetzt nach dem Verbindungsaufbau nach der utf-8 Festlegung irgendswas mit exec() hinschreiben?

Definiere dein Geschehen.

Das habe ich doch! *seufz* Ich weiß nicht, wie ich meine Problematik noch genauer erklären soll.

Bei mir hat sowohl mit der +/-0:00-Syntax als auch nach dem Füllen der Timezone-Tabellen mit Zonen-Namen alles bestens funktioniert.
SET time_zone='Europe/Berlin';
INSERT INTO test (t) VALUES ('1970-01-01 03:00:00');
SELECT t, UNIX_TIMESTAMP(t) FROM test;

Ausgabe; 1970-01-01 03:00:00, 7200

Und jetzt steige ich völlig aus. Bedeutet das, dass das überhaupt nicht zum Verbindungsaufbau hingehört sondern in den Query? Ich habe das getestet und es kommt natürlich eine Fehlermeldung.

So war der Query vorher - funktionierend und ohne Fehlermeldung:

$query="SELECT id, time, datum, nameFROMtabelleWHEREid = 15";

So, jetzt erweitere ich das wie von Dir hingeschrieben direkt im Query:

$query="SET time_zone='Europe/Athens' SELECT id, time, datum, nameFROM15_testWHEREid = 15";

Und es kommt die Fehlermeldung "Parse error: syntax error, unexpected T_STRING in..."!

SET time_zone='UTC';
SELECT t, UNIX_TIMESTAMP(t) FROM test;

Ausgabe; 1970-01-01 02:00:00, 7200

Abgesehen davon, dass das bei mir nicht funktioniert - verstehe ich das richtig? Du läßt Dir praktisch das, was in der Tabelle steht, 2 mal ausgeben. Einmal als Timestamp (also in der Form '0000-00-00 00:00:00'), so wie es gespeichert ist, und einmal gleich von SQL umgewandelt von Timestamp auf Unix-Timestamp?

Der Unix-Timestamp-Wert ändert sich nicht, denn er gibt ja immer die Sekunden in UTC aus

Der Unix-Zeitstempel gibt mir doch Auskunft über die vergangenen Sekunden seit Donnerstag, den 1. Januar 1970 00:00 Uhr UTC. Aber diese Zeit ist doch für alle gleich, egal, wo man sich auf der Welt befindet. Deshalb ist mir Deine Definition "Sekunden in UTC" nicht ganz klar.

Dann machst du vielleicht was falsch oder ziehst die falschen Schlüsse.

Glaub mir, lieber Dedlfix, ich recherchiere seit Tagen und versuche alles, um Deine Hinweise so zu realisieren, dass ich das mit den Zeitzonen handeln kann!

Der PMA macht auch nur ein SELECT und hat einen Verbindungsaufbau, bei dem er ohne weiteres keine Zeitzone setzt. Bei mir sehe ich ohne vorheriges SET im PMA immer die Lokalzeiten. Über den Startbildschirm vom PMA kann man sich die Variablen anzeigen lassen, und sogar bearbeiten (Version 3.5.2.2). Ich sehe bei 'time zone' ein SYSTEM und bei 'system time zone' ein CEST. Bearbeite ich 'time zone' zu UTC, sehe ich beim einfachen Anzeigen nun die UTC-Werte. Man muss das aber nicht dort ändern, sondern kann das angezeigte SQL-Statement bearbeiten und ein 'SET time_zone...;' voranstellen.

OK, diesen Absatz habe ich jetzt an die 10 Mal gelesen, ich glaube, ich hab jetzt endlich verstanden, wie er gemeint ist. Nur bzgl. dem "sich die Variablen anzeigen lassen": Auf meiner PMA Startseite steht "phpMyAdmin - 2.11.11.3". Das erklärt dann wohl, wieso ich diesen Punkt nirgendwo finden kann.

Du musst es nur richtig machen :-)

Wie gesagt, das versuche ich jetzt seit Tagen!

PS: Was ich natürlich _nicht_ weiß, ist, ob das $db->query("set time_zone='+02:00'"); auch wirklich der richtige Weg zur Festlegung einer Zeitzone ist.
Jein.

Du hast mir doch ein paar Zeilen darüber geschrieben, dass das falsch ist, weil "SET kein Resultset liefert"! Jetzt ist es nur mehr ein jein?

Du brauchst also die gefüllten Zeitzonentabellen, um das Zeitzonenhandling namensbasiert dem MySQL überlassen zu können.

Und wie finde ich heraus, ob das bei mir der Fall ist?

Sonst geht nur UTC-Speicherung in MySQL mit Lokal-Umrechnung in PHP.

Ja aber wenn dem so ist, dann ist doch unser ganzer tagelanger Diskurs völlig umsonst, oder? Wozu dann überhaupt das ganze umständliche Hin-und-her? Ist es da nicht am unkompiziertesten, einen Zeitpunkt als UTC Zeitstempel zu speichern, den ohne irgend eine Umwandlung von der DB ausgeben zu lassen und dann mit php in einen Zeitpunkt einer Zeitzone seiner Wahl umzuwandeln?

Was mich zu meiner letzten Frage führt: Du schreibst etwas weiter oben von der Funktion 'gmdate()'. Aber wenn ich besagten UTC Timestamp in einen Datum-Zeit-Stempel einer bestimmten Zeitzone bringen möchte, dann mache ich das doch so, dass ich den String in die Einzelteile zerlege und dann mit date() und mktime() arbeite. zB:

date_default_timezone_set('Europe/Berlin');  
echo date('c',mktime(1,2,3,4,5,2006));

Oder gibt es einen einfacheren Weg?

Wie gesagt, wenn das alles so umständlich und kompliziert ist, dann bleibe ich dabei, den Automatismus abzuschalten, die Zeitpunkte als UTC-Timestamp zu speichern diesen und vor der Ausgabe für den User mit php in ein lesbares Datum/Zeitpunkt einer gewünschten Zeitzone umzuwandeln.

Mich würden natürlich trotzdem die Antworten/Erklärungen zu meinen oben geschriebenen Unklarheiten interessieren!

Und bei der Gelegenheit möchte ich nochmal dafür bedanken, dass Du Dir da immer so viel Zeit nimmst und die Geduld aufbringst, immer wieder zu antworten. Auch, wenn jemand so unfähig ist wie ich! :-)

MfG

Hugo Egon Balder

[dedlfix]

Tach!

Vor 3 Tagen habe ich gefragt: "Was für ein Kommando muss ich nach dem Verbindungsaufbau durch mysqli noch darunter schreiben, um meine gewünschte Zeitzone festzulegen?" Da sagtest Du: "Soweit ich weiß, geht das mit einem Statement 'SET time_zone = timezone'".

SET ...  ist ein SQL-Statement wie SELECT/INSERT/UPDATE/DELETE ... Die setzt du ganz normal mit mit der Methode query() ab. In deinem Fall mit $db-> vor den Methodennamen.

OK, ich _war_ auf der Seite, dort steht auch nur die Syntax 'mysql> SET time_zone = timezone;'.

Das 'mysql>' ist der Prompt von einem Programm namens 'mysql'. Das ist eine Art Shell, in der man direct SQL-Statements absetzen kann. Nach dem 'mysql>' steht das eigentliche Statement, das du auch anderweitig (z.B. über query()) absetzen kannst.

Ich bin völliger Anfänger und habe keine Ahnung, wo und wie das jetzt einzubauen ist. Darum habe ich auch extra nach der Syntax gefragt!

Du hattest es in deinem vorigen Posting ja schon ziemlich richtig, als du die Zeitzone mit dem Zahlenwert gesetzt hast. Am Ende muss das so aussehen.

$db=@new mysql('mysql5.example.com','db_foo','password','db_foo');  
$db->set_charset('utf8');  
$db->query("SET time_zone='Europe/Athens'");

Besser ist noch, das Ergebnis vom query()-Aufruf auszuwerten, das im Fehlerfall false ist. Ursachen können ja die nicht gefüllte Timezone-Tabelle und Schreibfehler beim Zonen-Namen sein.

SET liefert kein Resultset. Du kannst für dessen Aufruf die Methode exec() nehmen.
Aha. Was heißt jetzt "kannst"?

Mein Fehler, entschuldige bitte. Vergiss das exec(), eine solche Methode gibts bei mysqli nicht. Bei PDO (eine Datenbankabstraktion) kann man zwischen Statements mit und ohne Resultset unterscheiden. Bei mysqli nimmt man in beiden Fällen query().

Definiere dein Geschehen.
Das habe ich doch! *seufz* Ich weiß nicht, wie ich meine Problematik noch genauer erklären soll.

Es war nicht direkt nachvollziehbar. Das wäre gewesen, wenn du genau deinen Versuch und das Ergebnis beschrieben hättest, also die verwendeten Statements und wie du dir das Ergebnis anschaust. (PMA als Anzeigewerkzeug stellt sich im weiteren Verlauf raus.)

Bei mir hat sowohl mit der +/-0:00-Syntax als auch nach dem Füllen der Timezone-Tabellen mit Zonen-Namen alles bestens funktioniert.
SET time_zone='Europe/Berlin';
INSERT INTO test (t) VALUES ('1970-01-01 03:00:00');
SELECT t, UNIX_TIMESTAMP(t) FROM test;

Ausgabe; 1970-01-01 03:00:00, 7200

Und jetzt steige ich völlig aus. Bedeutet das, dass das überhaupt nicht zum Verbindungsaufbau hingehört sondern in den Query? Ich habe das getestet und es kommt natürlich eine Fehlermeldung.

Das waren reineweg die SQL-Statements, wie man sie am mysql-Prompt ausführen kann, oder auch beim PMA in sein "SQL"-Feld einkippen kann. Letzten Endes brauchst du nur einmal die Zeitzone zu setzen, was praktischerweise direkt nach dem Verbindungsaufbau erfolgen sollte. Für Tests kann man jedoch die Zeitzonen auch mal öfter ändern. Allerdings musst du jedes Statement in seinem eigenen query() aufrufen. Das ist eine aus Sicherheitsgründen eingebaute Einschränkung in MySQL, damit man nicht über SQL-Injection ein weiteres Statement an das eigentlich auszuführende angehängt bekommt. Für mehrere Statements muss man mysqli::multi_query() nehmen, aber lass das mal lieber, denn damit ist das Resultset-Handling gleich wieder eine Runde komplizierter. Nimm also pro Statement ein query().

So war der Query vorher - funktionierend und ohne Fehlermeldung:
$query="SELECT id, time, datum, nameFROMtabelleWHEREid = 15";
So, jetzt erweitere ich das wie von Dir hingeschrieben direkt im Query:
$query="SET time_zone='Europe/Athens' SELECT id, time, datum, nameFROM15_testWHEREid = 15";
Und es kommt die Fehlermeldung "Parse error: syntax error, unexpected T_STRING in..."!

Da fehlt mindestens das Semikolon als Trennung zwischen den zwei Statements, und dann gehts trotzdem nicht, wie grad gesagt.

SET time_zone='UTC';
SELECT t, UNIX_TIMESTAMP(t) FROM test;

Ausgabe; 1970-01-01 02:00:00, 7200

Abgesehen davon, dass das bei mir nicht funktioniert - verstehe ich das richtig? Du läßt Dir praktisch das, was in der Tabelle steht, 2 mal ausgeben. Einmal als Timestamp (also in der Form '0000-00-00 00:00:00'), so wie es gespeichert ist, und einmal gleich von SQL umgewandelt von Timestamp auf Unix-Timestamp?

Wie es gespeichert ist, spielt im Prinzip keine Rolle. Aber da ein TIMESTAMP 4 Byte belegt, kann man davon ausgehen, dass er intern ebenfalls wie ein Unix-Timestamp als Sekunden angebender Integer abgelegt ist. Wenn er "normal" abgefragt wird, dann wird der Wert ja formatiert und unter Berücksichtigung der Zeitzone ausgegeben. Dabei findet also die Umwandlung statt. Die Funktion UNIX_TIMESTAMP(t) liefert hingegen den intern abgelegten Integer-Wert.

Der Unix-Timestamp-Wert ändert sich nicht, denn er gibt ja immer die Sekunden in UTC aus
Der Unix-Zeitstempel gibt mir doch Auskunft über die vergangenen Sekunden seit Donnerstag, den 1. Januar 1970 00:00 Uhr UTC. Aber diese Zeit ist doch für alle gleich, egal, wo man sich auf der Welt befindet. Deshalb ist mir Deine Definition "Sekunden in UTC" nicht ganz klar.

Genau das wollte ich mit der Verkürzung auf "Sekunden in UTC" aussagen.

Dann machst du vielleicht was falsch oder ziehst die falschen Schlüsse.
Glaub mir, lieber Dedlfix, ich recherchiere seit Tagen und versuche alles, um Deine Hinweise so zu realisieren, dass ich das mit den Zeitzonen handeln kann!

Mit Glauben kann ich dir schlecht helfen. Ich muss auch nachvollziehen können, wo deine Probleme und Verständnisschwierigkeiten liegen. Sonst kann ich mehr oder weniger nur pauschal antworten und bin in meinen Erklärungen drei Schritte voraus. Aber wir bekommen das schon hin. Letztlich ist die Lösung einfach, nur die Fallstricke müssen bekannt sein und wie man sie umgeht.

Der PMA macht auch nur ein SELECT und hat einen Verbindungsaufbau, bei dem er ohne weiteres keine Zeitzone setzt. Bei mir sehe ich ohne vorheriges SET im PMA immer die Lokalzeiten. Über den Startbildschirm vom PMA kann man sich die Variablen anzeigen lassen, und sogar bearbeiten (Version 3.5.2.2). Ich sehe bei 'time zone' ein SYSTEM und bei 'system time zone' ein CEST. Bearbeite ich 'time zone' zu UTC, sehe ich beim einfachen Anzeigen nun die UTC-Werte. Man muss das aber nicht dort ändern, sondern kann das angezeigte SQL-Statement bearbeiten und ein 'SET time_zone...;' voranstellen.

OK, diesen Absatz habe ich jetzt an die 10 Mal gelesen, ich glaube, ich hab jetzt endlich verstanden, wie er gemeint ist. Nur bzgl. dem "sich die Variablen anzeigen lassen": Auf meiner PMA Startseite steht "phpMyAdmin - 2.11.11.3". Das erklärt dann wohl, wieso ich diesen Punkt nirgendwo finden kann.

Beim PMA kann man bestimmte Menüpunkte auch wegkonfigurieren. Das ist oft so geschehen, wenn du eine vom Hoster gestellte Version verwendest. Bei der 2er Version waren die Systemvariablen über die Hauptseite zu erreichen. Da gab es einen eigenen Menüpunkt irgendwo neben solchen wie "Datenbanken" und "Rechte". Ob man sie auch dauerhaft für die aktuelle PMA-Sitzung ändern könnte, weiß ich nicht, aber zumindest ansehen konnte man sich die Variablen.

Eigentlich wollte ich damit nur sagen, dass der PMA keine Zeitzone setzt und damit alles gemäß der Zone von MySQL bekommt, die im DBMS eingestellt ist. Wenn du die Variablen nicht findst, kannst du die Default-Zeitzone auch über SELECT @@global.time_zone, @@session.time_zone abfragen, sowohl im PMA als auch wenn du ein derartiges Statement absetzt. Beide Werte sollten gleich sein und vermutlich 'SYSTEM' ergeben, und SELECT @@global.system_time_zone, @@session.system_time_zone zeigt vermutlich 'CEST' an.

PS: Was ich natürlich _nicht_ weiß, ist, ob das $db->query("set time_zone='+02:00'"); auch wirklich der richtige Weg zur Festlegung einer Zeitzone ist.
Jein.
Du hast mir doch ein paar Zeilen darüber geschrieben, dass das falsch ist, weil "SET kein Resultset liefert"! Jetzt ist es nur mehr ein jein?

Diese Antwort bezog sich rein auf die Zeitzonenangabe als Zahlenwert. In ihr bezog ich mich nicht auf die Art und Weise des Aufrufs. Mit dem Zahlenwert bekommst du nur zeitweise richtige Ergebnisse, deshalb das Jein.

Du brauchst also die gefüllten Zeitzonentabellen, um das Zeitzonenhandling namensbasiert dem MySQL überlassen zu können.
Und wie finde ich heraus, ob das bei mir der Fall ist?

Vermutlich sind bei dir die entsprechenden Tabellen nicht gefüllt, denn ein Setzen nach Namen misslingt ja, eins nach Zahlen klappt. So verhielt es auch auch bei mir.

Sonst geht nur UTC-Speicherung in MySQL mit Lokal-Umrechnung in PHP.
Ja aber wenn dem so ist, dann ist doch unser ganzer tagelanger Diskurs völlig umsonst, oder?

Ja, wenn du deinen Hoster nicht dazu bringen kannst, die Tabellen zu füllen, dann geht der Weg leider nicht.

Wozu dann überhaupt das ganze umständliche Hin-und-her?

Nun, ich hatte am Anfang der Diskussion auch noch nicht alles notwendige Detailwissen parat. Und manchmal können durchaus auch die Problemlösungsversuche in Sackgassen enden.

Ist es da nicht am unkompiziertesten, einen Zeitpunkt als UTC Zeitstempel zu speichern, den ohne irgend eine Umwandlung von der DB ausgeben zu lassen und dann mit php in einen Zeitpunkt einer Zeitzone seiner Wahl umzuwandeln?

Am unkompliziertesten ist, wenn der Hoster deine Zeitzone als Default konfiguriert hat und man keine Umkonfiguration nach anderenorts befürchten muss. Aber auch in dem Fall muss noch die Geschichte mit der Magie der TIMESTAMP-Felder beachtet werden.

Danach kommt der Fall, dass der Server eine andere Zeitzone hat. Dann wird zusätzlich das Setzen der Zeitzone nach dem Verbindungsaufbau benötigt. Und die gefüllten Zeitzonentabellen. Wenn der Hoster schon nach Billigland umzieht, dann soll er die gefälligst füllen, denn das kostet außer einem ganz kleinem Bisschen Arbeit nichts.

Die "meiste" Arbeit macht es, wenn du UTC-Sekunden als Integer (sprich: Unix-Timestamps) speicherst und selbst umrechnest. Das hält sich aber auch in überschaubaren Grenzen. Schöner fände ich, die Möglichkeiten MySQLs nutzen zu können.

Was mich zu meiner letzten Frage führt: Du schreibst etwas weiter oben von der Funktion 'gmdate()'. Aber wenn ich besagten UTC Timestamp in einen Datum-Zeit-Stempel einer bestimmten Zeitzone bringen möchte, dann mache ich das doch so, dass ich den String in die Einzelteile zerlege und dann mit date() und mktime() arbeite. zB:

gmdate() formatiert genauso wie date() einen Unix-Timestamp in ein gewünschtes Format, nur dass date() gemäß der konfigurierten Zeitzone ausgibt und gmdate() gemäß UTC. mktime() brauchst du in dem Fall nicht. Das nimmst du nur, wenn du einen Zeitpunkt in Einzelteilen rumliegen hast und davon den Unix-Zeitstempel haben willst. Oder auch gmmktime(), wenn der Zeitpunkt in UTC vorliegt.

dedlfix.

[Hugo Egon Balder]

Hallo Dedlfix!

So, ich glaub, ich lass es jetzt sein und werfe die Flinte ins Korn. Ich bekomme nämlich langsam Hassanfälle bei der Beschäftigung mit dem Thema. Ich kann es drehen und wenden wie ich will ... ich lese und teste jetzt seit bald 1 Woche und bin um _nichts_ weiter als vor ein paar Tagen.

Also abgesehen von vielen anderen Dingen, die ich nicht verstehe ... erkläre mir bitte zumindest Folgendes:

mktime() brauchst du in dem Fall nicht.

Na das zeig mir mal bitte!

Zur Wiederholung mein derzeitiger Stand:

Ich habe eine Spalte vom Typ 'Datetime'. Bei der Erstellung eines neuen Datensatzes kommt mittels php/mysqli das Value "UTC_TIMESTAMP()" in diese Spalte. Das heißt, wenn ich heute, am 24. 10. 2012 um 16:30 Uhr Nachmittags einen Datensatz erstelle, dann steht in dieser Spalte ein "2012-10-24 14:30:00", also wie erwartet die UTC Zeit zu diesem Zeitpunkt. So weit, so gut.

_Ohne_ den ganzen Umwandlungs-Schnickschnack, also wenn ich die gespeicherten UTC-Zeitpunkte 1:1 ausgeben möchte, mache ich das so:

$anfrage="SELECT `id`,`datum`,`name` FROM `tabelle`";  
$ergebnis=$db->query($anfrage);  
while($zeile=$ergebnis->fetch_object())  
  {  
    echo"<p>ID: "$zeile->id."<br />Name: ".$zeile->name."<br />Datum: ".$zeile->datum."</p>\n";  
  }

Jetzt brauche ich ein SELECT-Query und eine php-Verarbeitung, damit beim Enduser wieder ein 16:30 Uhr ausgegeben wird, wenn ich in php die Zeitzone per 'date_default_timezone_set('Europe/Berlin');' setze.

Und wenn ich statt dessen in der Ressource, die sich um die Ausgabe kümmert, ein 'date_default_timezone_set('Europe/Athens');' setze, muss natürlich ein 17:30 ausgegeben werden.

Ich weiß nicht mehr, welche Kombination aus MYSQL Zeitfunktion und php Zeitfunktion ich jetzt noch _nicht_ ausprobiert habe. Ich bekomme entweder Fehlermeldungen oder Zeiten, die alles andere sind als die erwarteten. (Nochmal: Ich brauche keine Umwandlung nach UTC mehr, weil der gespeicherte DATETIME-Zeitstempel _ist_ schon der UTC-Zeitpunkt! Es geht als nicht um die Umrechnung _nach_ UTC sondern um die Umrechnung _von_ UTC nach einer definierten Zeitzone!)

Deshalb _bitte_ ich Dich jetzt, wenn das so einfach ist, wie Du sagst - _bitte_ verrate mir hier in der original Syntax für das SELECT Query und wie ich die Ausgabe der DB in die richtige Zeit bringe.

Ich kann nicht mehr. :-(

MfG

Hugo Egon Balder

[dedlfix]

Tach!

Also abgesehen von vielen anderen Dingen, die ich nicht verstehe ... erkläre mir bitte zumindest Folgendes:

mktime() brauchst du in dem Fall nicht.
Na das zeig mir mal bitte!

Kann sein, dass ich das fehlgelesen hatte. Mit "UTC Timestamp" meintest du vermutlich ein formatiertes Datum, ich hab das als Unix-Timestamp gelesen. Auf letzteren kannst du date()/gmdate() direkt anwenden, das formatierte Datum muss natürlich erst wieder in einen Timestamp gebracht werden, sei es kleingehackt und mit (gm)mktime() oder geparst mit strtotime().

_Ohne_ den ganzen Umwandlungs-Schnickschnack, also wenn ich die gespeicherten UTC-Zeitpunkte 1:1 ausgeben möchte, mache ich das so:
[Anzeige im Default-Format von MySQL]
Jetzt brauche ich ein SELECT-Query und eine php-Verarbeitung, damit beim Enduser wieder ein 16:30 Uhr ausgegeben wird, wenn ich in php die Zeitzone per 'date_default_timezone_set('Europe/Berlin');' setze.

Für PHP braucht es einen Unix-Timestamp. Den bekommen wir auf zwei Wegen. Der erst ist, ihn von MySQL erzeugen zu lassen, der zweite ist, PHP zu beauftragen.

MySQL kennt ja UNIX_TIMESTAMP(), was wir nehmen können. Allerdings liegt nun der DATETIME-Wert in UTC vor und nicht in irgendeiner Lokalzeit. UNIX_TIMESTAMP() jedoch beachtet die Lokalzeit, also muss unbedingt die Zeitzone auf UTC stehen, beziehungsweise auf einer Differenz von +0:00 oder -0:00 (ohne Vorzeichen geht es nicht), was du mit dem bereits bekannten SET time_zone='+0:00' hinbekommst. In das abfragende SELECT-Statement baust du nun UNIX_TIMESTAMP(dein_datetime_feld) ein. (Ausführlich: SELECT id,UNIX_TIMESTAMP(datum),name FROM tabelle)

Jetzt hast du in PHP einen schönen Integerwert vorliegen. Egal welche Zeitzone eingestellt ist, mit gmdate() kannst du in dir als UTC-Zeit ausgeben lassen, mit date() in der aktuellen Zeitzone.

Für den zweiten Weg hast du eine funktionierende Lösung in deiner 20:51-Antwort stehen. Dabei musst du immer vor dem Parsen von einem oder mehreren formatierten DATETIME-Werten die Zeitzone auf UTC stellen und zum Ausgeben auf Lokalzeit. Das ist umständlicher, als einmalig am Script-Start die lokale Zone zu setzen. Ich würde deshalb den ersten Weg nehmen, denn dann braucht es nur einmalig SET time_zone='+0:00' nach dem Verbindungsaufbau und einmal date_default_timezone_set(...) am Scriptanfang;

Ich weiß nicht mehr, welche Kombination aus MYSQL Zeitfunktion und php Zeitfunktion ich jetzt noch _nicht_ ausprobiert habe. Ich bekomme entweder Fehlermeldungen oder Zeiten, die alles andere sind als die erwarteten.

Ich nehme an, du hast versucht, den formatierten DATETIME-Wert selbständig zu zerkleinern und an mktime() zu verfüttern. Das ergibt nur dann den korrekten Unix-Timestamp, wenn die PHP-Zeitzone auf UTC steht. Oder du nimmst gmmktime(), dann kann sie stehen wie sie will. Ausgeben geht mit date()/gmdate() wie schon beschrieben.

dedlfix.

[Hugo Egon Balder]

Hallo Dedlfix!

das formatierte Datum muss natürlich erst wieder in einen Timestamp gebracht werden, sei es kleingehackt und mit (gm)mktime() oder geparst mit strtotime().

Right! Wie Du in meiner Lösung (Posting vom 24.10.2012, 20:51 Uhr) siehst, habe ich strtotime() verwendet.

UNIX_TIMESTAMP() jedoch beachtet die Lokalzeit, also muss unbedingt die Zeitzone auf UTC stehen

Auch hier - genau so habe ich es im Endeffekt eh gelöst.

was du mit dem bereits bekannten SET time_zone='+0:00' hinbekommst.

Also mit dem 'SET time_zone' bin ich bis jetzt nicht klar gekommen. Aber das hat sich auf Grund meiner bestens funktionierenden Lösung eh erübrigt.

Für den zweiten Weg hast du eine funktionierende Lösung in deiner 20:51-Antwort stehen. Dabei musst du immer vor dem Parsen von einem oder mehreren formatierten DATETIME-Werten die Zeitzone auf UTC stellen und zum Ausgeben auf Lokalzeit. Das ist umständlicher, als einmalig am Script-Start die lokale Zone zu setzen. Ich würde deshalb den ersten Weg nehmen, denn dann braucht es nur einmalig SET time_zone='+0:00' nach dem Verbindungsaufbau und einmal date_default_timezone_set(...) am Scriptanfang;

Das klingt zwar gut, aber ich bekomme es einfach nicht hin. Sämtliche Versuche, das so zu lösen, wie Du es als zweiten Weg erklärst, enden bei mir in Fehlermeldungen oder Zeiten, die nicht dem entsprechen, was ich erwarte. (Abgesehen davon, dass ich noch immer nicht verstanden habe, an welcher Stelle genau und in welcher Syntax ich dieses 'SET time_zone' verwenden soll.)

Also nachdem ich den 2. Weg nicht zustande bringe ... ist es sehr schlimm, wenn ich den ersten Weg gehe?

MfG

Hugo Egon Balder

[Hugo Egon Balder]

Nachtrag zu meinem Posting vom 26.10.2012, 07:00 Uhr:

Hallo Dedlfix!

Ich dachte mir jetzt, ich versuche es ein letztes Mal mit dem Weg, den Du als einfacher preist.

In der Datenbank habe ich einen UTC-Zeitpunkt stehen: '2012-10-24 08:43:38'. Das heißt, wenn ich jetzt diesen Zeitpunkt für die mitteleuropäische Zeitzone ausgeben möchte, sollte da ein '2012-10-24 10:43:38' rauskommen. Ich mache jetzt alles genau so, wie von Dir geschrieben:

Mein Verbindungsaufbau:

$db=@new mysql('mysql5.example.com','db_foo','password','db_foo');  
$db->set_charset('utf8');  
// Wie von Dir geschrieben - nach dem Verbindungsaufbau wird UTC eingestellt:  
$db->query("SET time_zone='UTC'");

So hole ich die Daten:

// Wie von Dir geschrieben - im Script wird Lokalzeit eingestellt:  
date_default_timezone_set('Europe/Berlin');  
include("db_connect.php");  
$query="SELECT `id`, UNIX_TIMESTAMP(`created`) AS `stempel`,`name`,`surname` FROM `tabelle` WHERE `id`=1";  
$result=$db->query($query);  
while($line=$result->fetch_object())  
  {  
    $name=htmlspecialchars($line->name)." ".htmlspecialchars($line->surname);  
    $unix=$line->stempel;  
    $datum=date('Y-m-d H:i:s',$unix);  
    echo"<h3>".$name." ist Mitglied seit: ".$datum.".</h3>\n";  
  }  
$result->close();  
include("db_disconnect.php");

Ausgegeben werden sollte jetzt: "Max Mustermann ist Mitglied seit: 2012-10-24 10:43:38."!

Das geschieht aber nicht. "Ausgegeben wird: Max Mustermann ist Mitglied seit: 2012-10-24 08:43:38."!

Also genau jedne Zeit, die eh von Anfang an als UTC Zeitpunkt gespeichert war.

Wie gesagt, ich bekomme es einfach nicht hin, diesen "einfacheren Weg"... :-(

MfG

Hugo Egon Balder

[dedlfix]

Tach!

$db->query("SET time_zone='UTC'");

Ich dachte, die Zeitzone als Namen anzugeben funktioniert wegen der nicht gefüllten Timezone-Tabellen nicht. Der Rückgabewert von $db->query() müsste dir das in Form eines false bestätigen. Deswegen sagte ich, dass du für diesen Weg nicht 'UTC' sondern '+0:00' verwenden solltest. Bei UTC kannst du im Gegensatz zu den Lokalzeiten mit Sommerzeitumschaltung die Zone als Zahl setzen, weil diese immer gleich ist.

In der Datenbank habe ich einen UTC-Zeitpunkt stehen: '2012-10-24 08:43:38'.
Ausgegeben werden sollte jetzt: "Max Mustermann ist Mitglied seit: 2012-10-24 10:43:38."!
Das geschieht aber nicht. "Ausgegeben wird: Max Mustermann ist Mitglied seit: 2012-10-24 08:43:38."!

Wenn das Umstellen der MySQL-Zeit auf die UTC nicht klappt, dann wird diese Zeit als Lokalzeit interpretiert und UNIX_TIMESTAMP() erzeugt den Timestamp von 6:43 UTC. Den rechnest du mit PHPs Lokalzeit dann wieder zwei Stunden hoch.

dedlfix.

[Hugo Egon Balder]

Hallo Dedlfix!

Deswegen sagte ich, dass du für diesen Weg nicht 'UTC' sondern '+0:00' verwenden solltest.

Das hat mein Hirn beim Lesen wohl als unwichtig eingestuft, weil das "soll" ursprünglich ein "kann" war. Und _genau das_ war jetzt die Lösung!

Bei UTC kannst du im Gegensatz zu den Lokalzeiten mit Sommerzeitumschaltung die Zone als Zahl setzen, weil diese immer gleich ist.

Gute Info!

Wenn das Umstellen der MySQL-Zeit auf die UTC nicht klappt, dann wird diese Zeit als Lokalzeit interpretiert und UNIX_TIMESTAMP() erzeugt den Timestamp von 6:43 UTC. Den rechnest du mit PHPs Lokalzeit dann wieder zwei Stunden hoch.

Das erklärt natürlich, wieso es _so aussieht_, als wäre nichts mit dem gespeicherten Zeitpunkt geschehen.

Danke, lieber Dedlfix, für 1 Woche Geduld mit mir! Ich habe mich bemüht, dem SELF Prinzip folgend so viel als möglich nachzuschlagen, auszuprobieren und Deine Ratschläge zu befolgen. Und nach 7 Tagen hat sich das nun endlich bezahlt gemacht und ich habe eine Antwort/Lösung für meinen ursprünglichen Frageansatz. Danke für Deine Hilfe!

Mfg

Hugo Egon Balder

Und sollte irgendwann jemand nach dieser Lösung suchen und diesen Thread im Archiv finden, hier eine kurze Zusammenfassung:

Unabhängig von der eingestellten Lokalzeit des Servers sollten Timestamps standardisiert _immer_ in UTC gespeichert werden.

Mit anderen Worten: Sämtliche Zeitstempel in der Datenbank sind _immer_ UTC Zeitangaben. *)

Damit die Datenbank das auch weiß, wird es ihr gleich nach dem Verbindungsaufbau gemeinsam mit der Kodierungsinformation mitgeteilt. Die Verbindung zur DB mit mysqli sieht also so aus:

$db=@new mysql('mysql5.example.com','db_foo','password','db_foo');  
$db->set_charset('utf8');  
// Auch wenn die Zeitzonen-Namen-Tabelle des Servers nicht gefüllt ist - '+0:00' versteht er in jedem Fall als UTC:  
$db->query("SET time_zone='+0:00'");

Werden nun die gewünschten UTC-Timestamps per mysqli mit einem INSERT eingefügt, dann ist es _in diesem Fall_ egal, ob man als VALUE ein NOW() oder ein UTC_TIMESTAMP() verwendet, da durch die vorhergehende Festlegung auf UTC beide Werte gleich sind.

Bei der Ausgabe des Zeitstempels lässt man sich den Timestamp gleich direkt von SQL in einen Unix-Timestamp umwandeln:

SELECT UNIX_TIMESTAMP(spaltenname_des_timestamps) AS unixstempel [...]

Nachdem der als "unixstempel" ankommende Wert wird nun in der Variablen $unix gespeichert (Hier nur zur besseren Übersicht! Diesen Zwischenschritt kann man natürlich sparen und das Abfrageresult direkt weiterverarbeiten!) und muss nur noch von php in eine vorher definierte Zeitzone umgerechnet werden:

date_default_timezone_set('Europe/Berlin');  
// Dann kommt der Wert aus der DB.  
// Und dann die Verarbeitung:  
$datum=date('Y-m-d H:i:s',$unix);

Das bedeutet nun:

Wird um 15 Uhr mitteleuropäischer Sommerzeit ein Timestamp gespeichert, dann landet er als 13 Uhr in der DB. Stellt man bei der ausgebenden php-Ressource die Zeitzone auf 'Europe/Berlin', dann wird der Zeitpunkt wieder als 15 Uhr ausgegeben.

Der große Vorteil: Abgesehen davon, dass die gespeicherten Zeiten immer standardisiert UTC sind und man somit nie überlegen muss, auf welche Zeitzone etwas bezogen ist, könnte man nun für zB. die griechische Ausgabe der selben Website nur durch das einfache Setzen eines date_default_timezone_set('Europe/Berlin'); für die Besucher die selben Zeitpunkte in griechischer Zeitzone anzeigen.

*) Einzige Voraussetzung: Der Server muss natürlich den UTC-Bezug richtig konfiguriert haben!

[Hugo Egon Balder]

Hallo Dedlfix!

Ergänzung zu meiner Antwort von 19:27 Uhr:

Mir hat das jetzt keine Ruhe gelassen und ich habe eie weitere Stunde herumprobiert. Mit Hilfe einer zufällig gefundenen Seite, wo ich eine Funktion zu meiner Problematik entdeckt habe, löse ich mein Problem jetzt so:

Es bleibt dabei, dass CREATED und MODIFIED Spalten vom Typ 'Datetime' sind (damit auch innerhalb von SQL Querys Datums- und Zeitberechnungen durchgeführt werden) und mit einem 'UTC_TIMESTAMP()' gefüllt werden, so dass dort der Zeitpunkt in UTC steht.

Und hier jetzt mein php Quelltext mit einer überdimensionalen Schritt-für-Schritt Kommentierung, damit Du siehst, was ich mir dabei gedacht habe bzw. wie ich das zu verstehen denke:

<?php  
  // Aus der DB wird ein Datetime-Zeitpunkt in UTC kommen.  
  // Deshalb wird die Zeitzpne jetzt auf UTC gestellt:  
  date_default_timezone_set('UTC');  
  // Jetzt kommt der Datetime-Zeitpunkt in UTC aus der DB: (Hier einfachkeitshalber direkt definiert.)  
  $utc_datetime="2012-10-24 12:51:43";  
  // Dieser UTC Zeitpunkt wird jetzt umgewandelt in einen UNIX Timestamp:  
  $unix_timestamp=strtotime($utc_datetime);  
  // Nun wird für den php Server auf jene Zeitzone umgestellt, in der man ausgeben möchte. In diesem Fall für Deutschland:  
  date_default_timezone_set('Europe/Berlin');  
  // Der vorher generierte UNIX Timestamp wird nun wieder in ein lesbares Datum umgewandelt.  
  // Weil der PHP Server aber weiß, dass der UNIX Timestamp ein UTC-Zeitpunkt war und wir zwischenzeitlich ja  
  // auf die mitteleuropäische Zeit gewechselt haben, wird der PHP Server diesen Zeitzonenunterschied automatisch korrigieren:  
  // Mit anderen Worten eine Umwandlung eines Zeitpunktes in der UTC Zone (Format: UNIX Timestamp) umgerechnet in den selben Zeitpunkt in Mitteleuropa (Format: Datum).  
  $datum=date('Y-m-d H:i:s',$unix_timestamp);  
  // Zur Kontrolle werden jetzt der Ausgangszeitpunkt und der umgerechnete Zeitpunkt kontrolliert:  
  // Die Ausgangszeit war "2012-10-24 12:51:43":  
  var_dump($utc_datetime);  
  echo"<br /><br />";  
  // Zu diesem Zeitpunkt war in Deutschland: "2012-10-24 14:51:43":  
  var_dump($datum);  
?>

Das funktioniert _perfekt_ und ist genau das, was ich brauche! Es ist so simpel, berücksichtigt Sommer/Winterzeit und ein kurzes Austauschen der Zeitzone nach zB. 'Europe/Athens' würde auch sofort die korrekten Zeitpunkte für eine griechische Ausgabe bringen.

Diese Lösung kommt jetzt ohne irgend eine SQL eigene Umwandlung aus. Ich verwende weder das von mir ursprünglich vorgeschlagene 'mktime()', noch das von Dir vorgeschlagene 'gmdate()'. Ich habe auch bis jetzt keine Lösung gefunden, wie das mit 'gmdate()' funktionieren soll.

Jetzt würde ich natürlich gerne wissen, was _Du_ zu dieser Lösung sagst. Und wie Du das mit 'gmdate()' gelöst hättest.

Bzgl. der anderen Dinge, die Du geschrieben hast, werde ich demnächst noch was nachfragen.

MfG

Hugo Egon Balder