Comunidad de diseño web y desarrollo en internet

Convertir un campo VARCHAR a tipo DATE con SQL en MySQL

Es muy común, por lo menos con mi tipo de suerte, entrar y ver en una base de datos, que han puesto fechas en VARCHAR (cadena de texto). Y sin formato real de fecha!

El otro día me toco una base de datos como 1.6 millones de registros, y el formato DD-MM-YYYY (hh:ii:ss) varchar (40). ¡No tengo idea porque así!

¿Que debía hacer? ¿Usar PHP? Ja! Una base de datos de 1.6 millones de registros me hubiera tomado horas procesarla y no hacer nada con el server.

Es por eso que SQL puro en estos casos es la salvación.

Prepará una copia de la base de datos


Cualquier DBA prudente debe saber que NUNCA HAGAS COSAS EN LA BASE DE DATOS DE PRODUCCIÓN. JAMÁS!. No importa que, ni lo más simple.

Primero debemos hacer los siguientes pasos:

  • Copiamos la tabla que haremos la operación
  • Creamos una nueva columna date en nuestra DB (No hagan las operaciones directas sobre la columna que tiene los datos o se equivocan y volverán a copiar la tabla completa)
  • Comenten la tabla de pruebas, así si alguien entra, saben están realizando una tabla temporal (De hecho, llamenla temp_lo_que_sea)
Protip: Copiar la tabla usando phpMyAdmin es más rápido que usar exportar y luego importar. Eso es algo que tardará años en una tabla de 1.6 millones de registros por ejemplo. Créanme, llorarán del dolor.

Recordatorio: NO TRABAJEN CON LA TABLA DE PRODUCCIÓN !


La solución a las fechas con SQL


Bien ahora tenemos muchos registros, y debemos formatearlos a YYYY-MM-DD, formato date normal en MySQL.

En PHP lo que haríamos es traer el registro, aplicar substr y luego concatenar los resultados (".").

Algo como:

Código :

$date = $row["date"];
$date = substr($date,0,4)."-"substr($date,2,2)."-".substr($date,6,2);

Luego un UPDATE a la tabla en cuestión ¿Verdad?

Con SQL podemos hacerlo directamente usando CONTACT Y SUBSTRING. Son funciones natales de SQL, así que podrán hacer uso directamente desde la consola, o cualquier cosa que ejecute SQL en realidad.

Así que lo que tenemos que hacer es lo mismo:

Código :

UPDATE table_name SET new_date = CONCAT(SUBSTRING(date,1,4),"-",SUBSTRING(date,3,2),"-",SUBSTRING(date,7,2))

Noten que no he limitado ni puesto WHERE, esto es porque editaré la tabla completa.

Función CONCAT de SQL


Esta función es muy fácil, concatena de la siguiente forma:

Código :

CONCAT(VALUE,VALUE2,VALUE3,VALUE4)

Por eso fijense que hicimos:

Código :

CONCAT(SUBSTRING(date,1,4),"-"...

Eso implica el resultado del SUBSTRING y luego el "-".

Función SUBSTRING de SQL


La función SUBSTRING se comporta como siempre, solo que empieza a contar desde 1, don´t know why.

Lo que hará esta función es extraer un valor usando la siguiente estructura:

Código :

SUBSTRING(VALUE,START,COUNT);

Es por ello que si queremos cortar los dos primeros caracteres debemos hacer:

Código :

SUBSTRING(date,1,2);


Implementación


Una vez estamos seguros de la sentencia SQL, hagan una copia fiel de la tabla, y ejecuten en la tabla de producción la sentencia SQL. De esta forma, ahora, tendrán el resultado esperado.

Protip: Si están trabajando en una tabla de producción, podría seriamente afectar a sus compañeros de trabajo editando una columna. Es por eso que es conveniente crear una nueva columna, dónde dejen los resultados y operaciones. Luego de auditar correctamente todo, pueden borrar la otra columna.

¿Sabes SQL? ¿No-SQL? Aprende MySQL, PostgreSQL, MongoDB, Redis y más con el Curso Profesional de Bases de Datos que empieza el martes, en vivo.

Publica tu comentario

o puedes...

¿Estás registrado en Cristalab y quieres
publicar tu URL y avatar?

¿No estás registrado aún pero quieres hacerlo antes de publicar tu comentario?

Registrate