php - carbon parse format

¿Cómo almaceno una fecha UTC ISO8601 en una base de datos MySQL?

Question

Tengo miles de fechas en el siguiente formato:

2011-10-02T23:25:42Z (también conocido como ISO 8601 en UTC)

¿Qué tipo de datos de MySQL debo usar para almacenar esa fecha ISO8601 en una base de datos MySQL? Por ejemplo, fecha, timestamp o algo más?

¿Cuál es la mejor comparación (por ejemplo, obtener registros entre dos fechas / horas) y ordenar los resultados de las consultas? ¿Qué pasa si la base de datos es muy grande?

¿Y cuál sería la mejor manera de convertir la cadena de PHP anterior para el almacenamiento en MySQL? (Supongo que se date_default_timezone_set(''UTC''); )

Answer 1

Aquí están los puntos por los que es mejor usar datetime.

1. Con datetime podrá realizar manipulaciones de fechas en el lado mysql, como restar día, mes
2. Podrás ordenar los datos.
3. Si DB es enorme - varchar tiene más lugar en HDD

Answer 2

Creo que mantener sus valores de fecha y hora en el campo de tipo DATETIME sería una forma natural.

Desde mi propia experiencia con mi aplicación PHP actual, solo las operaciones de read / write relacionadas con esta información pueden ser problemáticas.

Una de las posibles soluciones (suponiendo que utilice el tipo de datos DATETIME ) para realizar correctamente todo el proceso podría ser el siguiente enfoque:

Leyendo los valores de DATETIME para el uso de PHP

1. Adquiera campos DATETIME de su base de datos convirtiéndolos en la consulta para representar una cadena en la forma ''2011-10-02T23:25:42Z'' usando la función MySQL DATE_FORMAT con ''%Y-%m-%dT%H:%i:%sZ'' formato ''%Y-%m-%dT%H:%i:%sZ'' ( documentos en DATE_FORMAT )
2. Lea el valor de la columna recuperada en este formato específico y conviértalo en PHP de cadena a representación real de fecha y hora válida para PHP (como los objetos de clase DateTime::createFromFormat y DateTime::createFromFormat método estático dado ''Ymd/TH:i:s/Z'' cadena ( T y Z se escapan para evitar tratarlas como directivas de formato) ( documentos para el método ).
3. Utilice los valores convertidos como valores de fecha y hora reales con toda la lógica aplicable, como comparaciones de fechas reales (no comparaciones de texto), etc.

Escritura de fecha y hora de PHP en la base de datos MySQL

1. Convierta, por ejemplo, el objeto de clase PHP DateTime a nuestro ISO 8601 en la representación de cadena de formato UTC utilizando el método de format del objeto de clase DateTime con el mismo método que antes de ''Ymd/TH:i:s/Z'' ( documentation ).
2. Realice la operación INSERT / UPDATE en la información de la base de datos utilizando dicha cadena preparada como un parámetro para la función STR_TO_DATE de MySQL (con ''%Y-%m-%dT%H:%i:%sZ'' cadena de formato) que la convierte en el valor DATETIME base de datos real ( docs en STR_TO_DATE ).

Código de ejemplo en PHP

A continuación, encontrará un ejemplo de borrador de dicho enfoque utilizando objetos DOP:

$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
}

Este enfoque me ayudó mucho en la operación de valores de fecha y hora entre PHP y la base de datos MySQL.

Espero que pueda ser útil para usted también.

Answer 3

No puede almacenar la fecha en formato UTC ISO8601 sin formato (con representación 2011-10-02T23:25:42Z ) y guardar toda la funcionalidad DATETIME de SQL.

Pero debe saber que MySQL (con respecto a http://dev.mysql.com/doc/refman/5.5/en/datetime.html ) siempre almacena la fecha / hora en UTC. También puede modificar la zona horaria para su conexión http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Entonces, si ejecutas en PHP

date_default_timezone_set(''UTC'');

y en MySQL

SET time_zone = +00:00

Seguro que PHP y MySQL usarían UTC.

Después de eso, puede convertir todas las cadenas de base de datos a DateTime sin preocuparse por la falta de coincidencia de la zona horaria.

Para convertir cualquier DateTime de PHP (sin tener en cuenta su zona horaria interna) a la cadena de fecha y hora de MySQL, debe establecer la zona horaria del objeto DateTime en UTC.

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

Answer 4

Puede usar el tipo de datos DateTime para almacenar la fecha y la hora.

Utilice la función CAST para convertir dichas cadenas en el tipo DateTime mysql.

Aquí hay un ejemplo:

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

Esto te dará 2011-10-02 23:25:42 .

Espero que esto te ayudará.

Answer 5

Puedes convertir fácilmente la fecha usando la función strtotime de 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

Ahora simplemente debe insertar su fecha en MySQL usando la timestamp o la datetime y datetime dependiendo de cuál se ajuste más a sus necesidades. Aquí las cosas más importantes que debes saber sobre ambos tipos.

Marca de tiempo

• Rango de ''1970-01-01 00:00:01'' UTC a ''2038-01-09 03:14:07'' UTC
• Afectado por la configuración de zona horaria.
• Almacenamiento de 4 bytes
• permitir la on update current_timestamp en columnas para todas las versiones.
• El índice es mucho más rápido
• NULL no es un valor predeterminado posible
• Los valores se convierten de la zona horaria actual a UTC para su almacenamiento, y se vuelven a convertir de UTC a la zona horaria actual para su recuperación.

Fecha y hora

• Rango de ''1000-01-01 00:00:00'' a ''9999-12-31 23:59:59''
• Constante (la zona horaria no afectará)
• Almacenamiento de 8 bytes
• permitir actualización en columnas solo a partir de la versión 5.6.5

¿Cuál es la mejor comparación (por ejemplo, obtener registros entre dos fechas / horas) y ordenar los resultados de las consultas? ¿Qué pasa si la base de datos es muy grande?

De acuerdo con los puntos anteriores que indiqué, entonces debería usar la timestamp de timestamp para una base de datos muy grande ya que el almacenamiento es más pequeño y el índice más rápido, lo que le dará un mejor rendimiento para la comparación. Sin embargo, DEBE ASEGURARSE de que su fecha se ajuste a los límites de la timestamp de timestamp que mencioné anteriormente, de lo contrario, no tendrá otra opción y deberá usar datetime .

Documentación para strtotime : http://php.net/manual/en/function.strtotime.php

Y, por favor, para el respondedor de SO que repite todos los días para no usar las funciones DEPRECATED mysql* , use PDO o mysqli* cuando realice las inserciones.

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

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