Wie speichere ich ein UTC-ISO8601-Datum in einer MySQL-Datenbank?

Ich habe Tausende von Daten im folgenden Format:

2011-10-02T23:25:42Z (auch bekannt als ISO 8601 in UTC)

Welchen MySQL-Datentyp sollte ich zum Speichern eines solchen ISO8601-Datums in einer MySQL-Datenbank verwenden? Z.B Datetime, timestamp oder etwas anderes?

Was eignet sich am besten zum Vergleichen (z. B. zum Abrufen von Datensätzen zwischen zwei Daten/Zeiten) und zum Ordnen der Ergebnisse von Abfragen? Was ist, wenn die Datenbank sehr groß ist?

Und was wäre der beste Weg, um den obigen PHP-String für MySQL-Speicher zu konvertieren? (Ich vermute date_default_timezone_set('UTC'); verwendet werden?)

Ich denke, dass Ihre Datum-Uhrzeit-Werte im Typfeld bleiben DATETIME wäre eine Art natürlicher Weg.

Nur aus meiner eigenen Erfahrung mit meiner aktuellen PHP-Anwendung read / write Operationen bezüglich dieser Informationen können problematisch sein.

Eine der möglichen Lösungen (vorausgesetzt, Sie verwenden DATETIME Datentyp) für die ordnungsgemäße Durchführung des gesamten Prozesses könnte der folgende Ansatz sein:

Lesen von DATETIME-Werten für die PHP-Nutzung

1. Erwerben DATETIME Felder aus Ihrer Datenbank umwandeln in der Abfrage zur Zeichenfolgendarstellung in Form von '2011-10-02T23:25:42Z' durch die Nutzung DATE_FORMAT MySQL-Funktion mit '%Y-%m-%dT%H:%i:%sZ' Formatierungszeichenfolge (Dokumente am DATE_FORMAT)
2. Lesen Sie den abgerufenen Spaltenwert in diesem speziellen Format und konvertieren Sie ihn in PHP von einer Zeichenfolge in eine echte Datums-Zeit-Darstellung, die für PHP gültig ist (z DateTime Klassenobjekte und DateTime::createFromFormat statische Methode angegeben 'Y-m-d\TH:i:s\Z' Formatierungszeichenfolge (T und Z werden maskiert, um sie nicht als Formatierungsanweisungen zu behandeln) (docs für die Methode).
3. Verwenden Sie konvertierte Werte als echte Datum-Uhrzeit-Werte mit der gesamten anwendbaren Logik, wie echte Datumsvergleiche (keine Textvergleiche) usw.

Schreiben von PHP-Datum und Uhrzeit in die MySQL-Datenbank

1. Konvertieren Sie zB PHP DateTime Klassenobjekt zu unserer ISO 8601 im UTC-Format Zeichenfolgendarstellung verwenden DateTime Klassenobjekt format Methode mit der gleichen wie zuvor 'Y-m-d\TH:i:s\Z' Formatierungszeichenfolge (Dokumentation).
2. Ausführen INSERT / UPDATE Operation mit Datenbankinformationen unter Verwendung einer solchen vorbereiteten Zeichenfolge als Parameter für die MySQL-Funktion STR_TO_DATE (mit '%Y-%m-%dT%H:%i:%sZ' Formatierungszeichenfolge), die sie in eine echte Datenbank umwandelt DATETIME Wert (Dokumente am STR_TO_DATE).

Beispielcode in PHP

Nachfolgend finden Sie ein Entwurfsbeispiel für einen solchen Ansatz unter Verwendung von PDO-Objekten:

$db = new PDO('mysql:host=localhost;dbname=my_db;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    // run the query aquring 1 example row with DATETIME data 
    // converted with MySQL DATE_FORMAT function to its string representation 
    // in the chosen format (in our case: ISO 8601 / UTC)
    $stmt = $db->query("SELECT DATE_FORMAT(dt_column, '%Y-%m-%dT%H:%i:%sZ') AS formatted_dt_col"
                        ." FROM your_table LIMIT 1"); 

    if($stmt !== FALSE) {
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        // convert the acquired string representation from DB 
        // (i.e. '2011-10-02T23:25:42Z' )
        // to PHP DateTime object which has all the logic of date-time manipulation:    
        $dateTimeObject = DateTime::createFromFormat('Y-m-d\TH:i:s\Z', $row['formatted_dt_col']);

        // the following should print i.e. 2011-10-02T23:25:42Z
        echo $dateTimeObject->format('Y-m-d\TH:i:s\Z');  

        // now let's write PHP DateTime class object '$dateTimeObject' 
        // back to the database
        $stmtInsertDT = $db->prepare("INSERT INTO your_table(dt_column) " 
                             . " VALUES ( STR_TO_DATE(:par_formatted_dt_column, '%Y-%m-%dT%H:%i:%sZ') )");

        $dtAsTextForInsert = $dateTimeObject->format('Y-m-d\TH:i:s\Z');

        // convert '$dateTimeObject' to its ISO 8601 / UTC text represantation
        // in order to be able to put in in the query using PDO text parameter
        $stmtInsertDT->bindParam(':par_formatted_dt_column', $dtAsTextForInsert, PDO::PARAM_STR);

        $stmtInsertDT->execute();

        // So the real insert query being perform would be i.e.:
        /*
           INSERT INTO your_table(dt_column) 
           VALUES ( STR_TO_DATE('2011-10-02T23:25:42Z', '%Y-%m-%dT%H:%i:%sZ') )
        */
    }
}
catch(\PDOException $pexc) {
 // serve PDOException
}
catch(\Exception $exc) {
// in case of no-PDOException, serve general exception
}

Dieser Ansatz hat mir sehr beim Betrieb von Datums- und Uhrzeitwerten zwischen PHP und der MySQL-Datenbank geholfen.

Ich hoffe, es könnte auch für Sie hilfreich sein.

Sie können verwenden DateTime Datentyp zum Speichern von Datum und Uhrzeit.

Verwenden CAST Funktion, um solche Strings in mysql zu werfen DateTime Typ.

Hier ist ein Beispiel:

CAST("2011-10-02T23:25:42Z" AS DATETIME)

Dies wird Ihnen geben 2011-10-02 23:25:42.

Ich hoffe, das wird Ihnen helfen.

Sie können das Datum einfach mit konvertieren strtotime Die Funktion von php :

date_default_timezone_set('UTC');
$date="2011-10-02T23:25:42Z";//(aka ISO 8601 in UTC)
$time = strtotime($date); //time is now equals to the timestamp
$converted = date('l, F jS Y \a\t g:ia', $time); //convert to date if you prefer, credit to Marc B for the parameters

Jetzt würden Sie einfach Ihr Datum einfügen MySQL verwenden timestamp oder datetime je nachdem, welches am besten zu Ihren Bedürfnissen passt. Hier die wichtigsten Dinge, die Sie über beide Arten wissen sollten.

Zeitstempel

• Bereich von ‘1970-01-01 00:00:01’ UTC bis ‘2038-01-09 03:14:07’ UTC
• Beeinflusst von der Zeitzoneneinstellung.
• 4 Byte Speicher
• ermöglichen on update current_timestamp auf Spalten für alle Versionen.
• Index ist viel schneller
• NULL ist kein möglicher Standardwert
• Die Werte werden von der aktuellen Zeitzone in umgerechnet UTC zur Aufbewahrung und wieder zurück konvertiert UTC in die aktuelle Zeitzone zum Abrufen.

Terminzeit

• Bereich von ‘1000-01-01 00:00:00’ bis ‘9999-12-31 23:59:59’
• Konstant (Zeitzone wird nicht beeinflusst)
• 8 Byte Speicher
• Update auf Spalten nur ab Version zulassen 5.6.5

Was eignet sich am besten zum Vergleichen (z. B. zum Abrufen von Datensätzen zwischen zwei Daten/Zeiten) und zum Ordnen der Ergebnisse von Abfragen? Was ist, wenn die Datenbank sehr groß ist?

Gemäß den vorherigen Punkten, die ich angegeben habe, sollten Sie dann verwenden timestamp für eine sehr große Datenbank, da der Speicher kleiner und der Index schneller ist, wodurch Sie eine bessere Leistung zum Vergleich erhalten. Aber du SICHERSTELLEN MUSS Ihr Datum wird den Grenzen des entsprechen timestamp Ich habe es bereits erwähnt, sonst haben Sie keine Wahl und müssen es verwenden datetime.

Dokumentation für strtotime : http://php.net/manual/en/function.strtotime.php

Und bitte, im Interesse von SO’s Antwortenden, die sich jeden Tag wiederholen, das nicht zu benutzen mysql* VERALTET Funktionen, bitte verwenden PDO oder mysqli* wann du deine Einsätze machen wirst.

http://php.net/manual/en/book.pdo.php

http://php.net/manual/en/book.mysqli.php

Sie können keine Daten im rohen UTC ISO8601-Format speichern (mit 2011-10-02T23:25:42Z Darstellung) und speichern Sie alle SQL DATETIME-Funktionen.

Aber Sie sollten wissen, dass MySQL ( bzgl http://dev.mysql.com/doc/refman/5.5/en/datetime.html ) Uhrzeit/Datum immer in UTC speichern. Sie können auch die Zeitzone für Ihre Verbindung ändern
http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Also, wenn Sie in PHP ausführen

date_default_timezone_set('UTC');

und in MySQL

SET time_zone = +00:00

sicher, dass PHP und MySQL UTC verwenden würden.

Danach können Sie alle Datenbankzeichenfolgen in DateTime konvertieren, ohne sich um Zeitzonenkonflikte kümmern zu müssen.

Um eine beliebige PHP-DateTime (ohne ihre interne Zeitzone zu übernehmen) in eine MySQL-Datetime-Zeichenfolge zu konvertieren, sollten Sie die Zeitzone des DateTime-Objekts auf UTC setzen.

$datetime->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s');

Verwenden Sie Ihre Datetime auf meinem System, das PDT ist:

SELECT CONVERT_TZ(str_to_date('2011-10-02T23:25:42Z','%Y-%m-%dT%H:%i:%sZ'),'+00:00','SYSTEM') from dual;

2011-10-02 16:25:42

Wenn Ihre Datumszeit einen Bruchteil einer Mikrosekunde hat; Fügen Sie das .%f vor dem Z wie folgt ein:

SELECT CONVERT_TZ(str_to_date('2011-10-02T23:25:42.123456Z','%Y-%m-%dT%H:%i:%s.%fZ'),'+00:00','SYSTEM') from dual;

2011-10-02 16:25:42.123456

Hier sind die Punkte, warum es besser ist, datetime zu verwenden.

1. Mit datetime können Sie Datumsmanipulationen auf MySQL-Seite vornehmen – wie z. B. Tag, Monat subtrahieren
2. Sie können Daten sortieren.
3. Wenn die DB riesig ist, nimmt varchar mehr Platz auf der Festplatte ein