[SOLUCIONADO] Usar un trigger para cambiar el formato fecha

[jessicamcav13]

Buenos días.

Quisiera saber si lo que voy a plantear a continuación es posible.

Tengo una BD que recoge fichajes de usuarios (recoge tiempos) de esta manera:

30/09/2014 9:00:00

Pero el campo esta en formato string y si pudiera ser posible, no deseo cambiarlo a date.

Necesito cambiarle la máscara cuando hago las inserciones para que la hora se presente así: 09:00:00 y no así: 9:00:00.

Con esta sentencia es posible cambiarle la máscara siendo un string:

Código MySQL:

UPDATE fichajes set tiempo=STR_TO_DATE("30/09/2014 9:00:00", '%d/%m/%Y %H:%i');
 

Solo hice algunos triggers básicos, pero en Oracle, hace 2 años. Estoy bastante verde y no sabría como hacerlo exactamente.

Hice algo así:

Código MySQL:

CREATE TRIGGER fichajes AFTER INSERT ON fichajes
  FOR EACH ROW BEGIN
    DECLARE fecha VARCHAR(50);
    SET fecha =(SELECT fecha from fichajes);
    UPDATE fichajes set fecha=STR_TO_DATE(@fecha, '%d/%m/%Y %H:%i');
   END;
 

No me funciona, pero es que no tengo mucha idea.

A ver si me podéis echar un cable ^^ Gracias.

[quimfv]

Una curiosidad, por que no quieres usar el tipo de datos adecuado a los datos que vas a guardar.

[ccliment]

Hola,

Tal y como dice Quimv, lo más normal es que utilices el tipo de campo adecuado para cada caso. En este caso debería ser timestamp o datetime.

Si aún así lo que quieres hacer es cambiarlo con un trigger te paso el código que creo deberías poner:

Código MySQL:

CREATE TRIGGER fichajes_ai AFTER INSERT ON fichajes
FOR EACH ROW BEGIN
UPDATE fichajes set NEW.fecha=DATE_FORMAT(NEW.fecha, '%d/%m/%Y %H:%i:%s');
END;
 

Si te da error estaría bien siempre que pusieseis el mensaje, y ten cuidado con el delimitador de sentencias porque igual lo deberías cambiar para ejecutarlo y no te tome todas las sentencias por separado.

Saludos.

[jessicamcav13]

Iniciado por quimfv

Una curiosidad, por que no quieres usar el tipo de datos adecuado a los datos que vas a guardar.

Porque necesito que sea tipo varchar para usarlo en una función. A parte, supongo que tendría que cambiarle la máscara igualmente y volvería a necesitar el trigger.

Pues he probado la sentencia y no funciona.

MySQL ha dicho: Documentación

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 ).

Es decir, que esta mal la sentencia? La pruebo a parte, y funciona. Bueno, cambiando DATE_FORMAT por STR_TO_DATE, ya que quiero tomar un valor string y que me lo devuelva date para poder ponerle la máscara (aunque probé con DATE_FORMAT por si acaso).

He cambiado el limitador, y según la teoría sería así no?

Código MySQL:

DELIMITER $$
CREATE TRIGGER fichajes_ai AFTER INSERT ON fichajes
FOR EACH ROW BEGIN
UPDATE fichajes set NEW.fecha=STR_TO_DATE(NEW.fecha, '%d/%m/%Y %H:%i:%s');
END $$
 

Pero da el mismo error.
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$' at line 4.

Gracias y un saludo.

[gnzsoloyo]

Porque necesito que sea tipo varchar para usarlo en una función. A parte, supongo que tendría que cambiarle la máscara igualmente y volvería a necesitar el trigger.

Estás confundiendo lo que debes guardar en la base, con lo que debes recuperar por consulta.
Son cosas diferentes.
Puedes perfectamente guardar en forma correcta una fecha como DATE, con lo que te ahorrarás muchñisimas complicaciones a la hora de hacer la consulta, y luego simplemente manejar la salida como cadena de texto con el formato que quieras, sin por eso tener que poner basura en la base.
Y literalmente estoy diciendo basura. Un DATE guardado como VARCHAR es un error de los graves por muchas raoznes, incluyendo eficiencia, optimizacion de consultas, capacidad de almacenamiento y manejo de índices.

Por otro, para hacer un TRIGGER, debes recordar que no puedes invocar una sentencia INSERT/UPDATE/DELETE sobre la misma tabla donde corre el trigger . Sólo puedes operar con el registro entrante, per no con la tabla.

[jessicamcav13]

Tienes razón, pero el problema es que si lo convierto tipo DATETIME o TIMESTAMP (guardo fecha y hora), a la hora de insertar un tiempo, me inserta 0000-00-00 00:00:00 por el formato que tiene DATETIME y TIMESTAMP.

Claro, que debería de poner una máscara en el insert, pero no puedo. Los insert los saco de un archivo sql de un aparato, y la idea es automatizar los insert...por eso debo de cambiar la máscara después de hacer la inserción.

[gnzsoloyo]

Iniciado por jessicamcav13

Tienes razón, pero el problema es que si lo convierto tipo DATETIME o TIMESTAMP (guardo fecha y hora), a la hora de insertar un tiempo, me inserta 0000-00-00 00:00:00 por el formato que tiene DATETIME y TIMESTAMP.

Claro, que debería de poner una máscara en el insert, pero no puedo. Los insert los saco de un archivo sql de un aparato, y la idea es automatizar los insert...por eso debo de cambiar la máscara después de hacer la inserción.

Ok, el error de "0000-00-00 00:00:00 " es normalmente porque entr un dato nulo o que no es de fecha, ni tampoco numérico. De lo contrario te ingresaría algo, aunque fuese una fecha incorrecta.
Esto nos indica que tienes un problema mayor y es que el formato del dato entrante está sucio. Sería mejor verificar por qué el aparato está entregando algo indebido, y en todo caso sería bueno que nos mostraras un ejemplo real de esos datos, a fin de darte un mejor acercamiento. Ten en cuenta que si el dato está mal formateado en el insert, también va a generar erroes de conversion en el TRIGGER, con lo que estarías pasando el problema de un lado a otro, pero no solucionandolo.

Por otrolado, aclarame un punto, una vez que capturas el dato y lo estás por mandar a insertar a la tabla, ¿cómo realizas ese INSERT? ¿Puedes mostrarnos como queda armada la query en ese caso?

Entendamos que si se pudiera resolver el problema en el INSERT, el TRIGGER se volvería INNECESARIO (lo que sería una excelente idea).

[quimfv]

En el INSERT el dato DATETIME debe ir entre apostrofes

'2014-09-30 18:45:00'

por si acaso....

Tu guardas una fecha/hora en un campo DATETIME, podras operar con ella y con DATE_FORMAT() puedes obtener el string que necesites para tu función.

[jessicamcav13]

Hola de nuevo.

Voy a intentar contestar a todas vuestras preguntas y dudas sobre mi tema, a ver si me podéis ayudar a encontrar la solución más óptima.

Dispongo de un aparato que es un reloj de fichaje, es decir, captura la fecha y la hora a la que los empleados salen y entran del trabajo. Y luego quiero mostrar esos datos en una tabla de una página web para calcular su jornada y las horas que debe (de hecho, ya lo hice).

El aparato se conecta a un ordenador a través de un programa llamado Attendance Manager (se encuentra en internet y es gratuito), y desde allí se puede extraer los datos que almacena el reloj, exportándolos en el formato de excell, sql y otros muchos más. Los extraigo como sql y cuando abro el archivo sql, me aparecen tantos inserts como veces haya fichado los empleados, de esta manera:
INSERT INTO fichajes (Name,sTime,VerifyFlag,MachineName,checktype,senso id,workcode,sDate, Pin) VALUES ('Ramón','12/09/2014 11:00:00', 'FP/PW/RF/F',1,'I',1,0,'12/09/2014',1)

Puedo ponerle el nombre de la tabla en el reloj, para que me lo ponga en el insert.

A mi las que me interesan son sTime,sDate y Pin que es como si fuera la ID del usuario. Como veis, no puedo tener clave primaria... Lo malo es que genera esta tabla y lo mejor sería que generara 3, 1 con las fechas, otra con los tiempos y una renacida...pero no es el caso, tengo que adaptarme al reloj.

Los inserto en mysql y previamente he creado la tabla fichajes y he creado las tablas con los mismos nombres que me dice el insert (y por tanto, elijo el formato en el que quiero que estén)


De momento los inserts los hago yo manualmente, pero quiero automatizarlo en un futuro, con un .bat para que me lo inserte en el servidor y leerlo yo después en php (es un tema a parte y no sé si podré hacerlo, pero al caso...).
Lo mejor sería que sTime tuviera formato DATETIME, pero al hacer el insert, inserta el 0000-00-00 00:00:00, porque el formato DATETIME es YYYY-MM-DD HH:MM:SS y los datos de sTime están en dd/mm/yyyy h:mm:ss. Por eso lo puse tipo varchar...Podría ponerle una máscara, pero entonces ya no sería automático, por eso pensé en la posibilidad del TRIGGER, para que me cambiara el formato después de insertar los datos en la tabla.

Pero me encuentro con un problema a la hora de mostrarlos en una tabla, por orden, ya que al ser string, lee caracter por caracter y entonces cuando en un día hay por ejemplo 2 fichajes a las 9 y a las 14, el 9 será mayor que el 1 de las 14, entonces poner las 9 detrás de las 14...(No sé si me explico...)

Aqui un ejemplo:

Al tener ese 9:00:00 luego también me calcula mal la jornada(H.Jornada) y las horas que debe (Saldo)...
He mirado si el programa que extrae los datos del reloj tiene alguna opción para cambiar el formato en el que me exporta la tabla sTime pero no me hace caso...Lo he hecho de maneras distintas y no me sale en el formato que yo le he indicado...

Y ahora me pregunto, ¿qué debo hacer para que la hora me aparezca correctamente?

Gracias por vuestro tiempo.

[gnzsoloyo]

Mira, la solución más simple de implementar que yo le veo es que no insertes los datos directamente en la tabla de consulta, sino que uses una tabla intermedia donde, donde el campo se mantenga como VARCHAR. Luego, por medio de un SP, procesas el contenido formateando los datos correctamente e insertando los marcados como DATETIME en su tabla final.
Todo ese proceso puede ser automatizable, y t evitarás en forma definitiva problemas con los datos de fecha, sin necesidad de andar haciendo cosas imposibles.
En cuanto a ponerlo como trigger, hay un problema para tu idea: Un trigger tiene como datos de entrada / salida un registro virtual (digamos que está en un estado transitorio en memoria), pero ese registro virtual contiene los mismos campos que las columnas de la tabla en la que corre el trigger, y por consecuencia sus tipos de datos se corresponden a la tabla.
Esto significa que no puedes crear un campo DATETIME, donde quieras que la conversión de "dd/mm/aaaa" genere un "aaaa-mm-dd", porque al momento de ejecutarse el dato se habrá corrupto de la misma forma.
¿Se entiende el problema?
Entonces tienes pocas alternativas:
1) Parchar todas y cada una de las consultas que realizas a la tabla que hoy usas, para insertarle en cada llamada de esa columna un STR_TO_DATE(), ya sea en el SELECT o en el WHERE, y en cualquier operación realizada en SQL. Esto se pagará con performance.
2) Crear una tabla transaccional donde deposites el resultado de los inserts y luego proceses para volcar los datos a la tabla final, realizando las conversiones necesarias. Esto no te afectará mucho la performance porque el volcado se puede hacer masivo y con eso obtendrías alta performance.

¿Qué camino eliges?

[jessicamcav13]

Me gusta la segunda opción, pero no sé cómo hacer para que sea completamente automático.

[quimfv]

Otra solución posible, si Attendance Manager no es configurable, es explorar la salida en formato excel que dices que puede generar, si es una salida csv es importable directamente con LOAD DATA...

En cuanto a automatizar la segunda opción que te propone @gnzsoloyo cualquier script sea interno o externo capaz de ejecutar consultas te lo va a solucionar

Código MySQL:

INSERT INTO tablaDefinitiva 
   SELECT Name, 
                STR_TO_DATE(sTime, '%d/%m/%Y %h:%i:%s') ssTime,
                VerifyFlag,
                MachineName,
                checktype,
                senso id, 
                workcode,
                STR_TO_DATE(sDate, '%d/%m/%Y') ssDate, 
                Pin
   FROM tablaTransaccional;
 

[gnzsoloyo]

Básicamente el volcado masivo que yo mencionaba es precisamente lo que te plantea @quimfv.
Respecto al LOAD DATA, lo que hay que tener en cuenta es que no se puede ejecutar en un SP, por lo que tendrías que lanzarlo en forma programática, como hoy haces los inserts. Puede ser un buen método para agregar a lo que te propongo.

La idea es que generes una tabla transaccional mas o menos como esta:

Código MySQL:

CREATE TABLE fichajesTemp 
    (NAME VARCHAR(75),
    sTime VARCHAR(15),
    VerifyFlag VARCHAR(50),
    MachineName SMALLINT UNSIGNED,
    checktype VARCHAR(1),
    sensorid SMALLINT UNSIGNED,
    workcode SMALLINT UNSIGNED,
    sDate VARCHAR(15), 
    Pin SMALLINT UNSIGNED);
 

A esa le haces el vocado con el LOAD DATA, y quedarían los datos necesarios ya en ella.
Luego, suponiendo que la tabla final tiene este formato:

Código MySQL:

CREATE TABLE fichajes 
    (NAME VARCHAR(75),
    sTime TIME,
    VerifyFlag VARCHAR(50),
    MachineName SMALLINT UNSIGNED,
    checktype VARCHAR(1),
    sensorid SMALLINT UNSIGNED,
    workcode SMALLINT UNSIGNED,
    sDate DATE, 
    Pin SMALLINT UNSIGNED);
 

necesitarías luego un EVENT cuyo cuerpo de código tenga algo como esto:

Código MySQL:

LOCK TABLE fichajesTemp;    
INSERT INTO FICHAJES
SELECT Name, 
   TIME(STR_TO_DATE(sTime, '%d/%m/%Y %T')) ssTime,
   VerifyFlag,
   MachineName,
   checktype,
   senso id, 
   workcode,
   DATE(STR_TO_DATE(sTime, '%d/%m/%Y %h:%i:%s')) ssDate, 
   Pin
FROM fichajesTemp;
TRUNCATE fichajesTemp;
UNLOCK TABLE fichajesTemp;
 

Esto lo estoy imaginando, no hagas un Copy+Paste porque hay que hacer pruebas aún...

El EVENT lo planteo para que la carga final pueda programarse para ser ejecutada en clos ciclos de tiempo que quieras. Por su lado el llenado de la tabla transaccional dependerá de cómo lo programes tu. Pero usualmente ocnviene hacerlo por medio de un servicio que corra en background, para evitarte complicaciones.

[jessicamcav13]

Hola chicos.

Gracias por ayudarme, pero he encontrado una solución más sencilla. Uso PHP como lenguaje servidor, así que, usé la función date_format() para convertir cada elemento del array, que creo del resultado de la consulta, en tipo date y luego sort() para ordenar los elementos del array.

Quizás no sea lo más correcto, pero me sirve.

Lo bueno es que aprendí cosas, pues tengo conocimientos básicos de SQL y no tenía ni idea de lo que era un LOAD DATA. Sin duda, algún día me servirá de ayuda.

Muchísimas gracias, de verdad :D