Diferencia de tiempo entre fecha de dos tablas

[zubintro]

Hola hola, tengo un problema para generar una consulta en la cual necesito saber la difencia de horas o minutos en las fechas almacenadas de dos tablas. El problema es que los valores de fecha que seran para restar los extraigo de una consulta.

Por ejemplo, tengo la siguiente consulta con la cual obtengo el ultimo registro de cada grupo de datos

Código MySQL:

SELECT *
FROM   jpv59_obhelpdesk3_messages s1
WHERE  reply_time=(SELECT MAX(s2.reply_time)
FROM jpv59_obhelpdesk3_messages s2
WHERE s1.tid = s2.tid);
 

Los datos que obtengo de esa consulta son los siguientes:

Código:

id	tid*	user_id		email		content		reply_time
66	1	817		[email protected]		ultiumo		04/10/2014 00:53:44
64	2	817		[email protected]		ultimo		04/10/2014 00:00:45
68	3	817		[email protected]		ultimo		04/10/2014 01:13:05
72	4	817		[email protected]		ultimo		04/10/2014 01:15:38

Tengo una segunda tabla con los siguientes datos

Código:

id	staff	consumer_id	consumer_email	id_deparmet	subject		status		date_create
1	817	817		[email protected]		4		tema		closed		03/10/2014 23:51
2	817	817		[email protected]		5		tema		closed		03/10/2014 23:59
3	817	817		[email protected]		4		dasdasd		closed		04/10/2014 01:12
4	817	817		[email protected]		4		hgf		closed		04/10/2014 01:13

Con esto, necesito crear una consulta que me devuelva la diferencia de horas entre los valores de reply_time de la tabla 1 y date_create de la tabla dos con los valores que pertenecen a la tabla 2.

Se que si empleo TIMEDIFF puedo recuperar dicho valor, solo que no se como terminar la consulta combinando dos tablas.

Gracias a todos por adelantado y su tiempo en leer esto. Saludos

[quimfv]

Antes que nada creo que esta será mucho mas eficiente

Código MySQL:

SELECT s1.*
FROM   jpv59_obhelpdesk3_messages s1
            INNER JOIN (SELECT tid,MAX(s2.reply_time)
                                  FROM jpv59_obhelpdesk3_messages
                                 GROUP BY tid) s2
                     ON s1.tid = s2.tid;
 

luego todo a la vez

Código MySQL:

SELECT s1.*,
            s3.date_create
 
////aqui puedes usar las datetime functions para operar con los campos de todas las tablas     
 
FROM   jpv59_obhelpdesk3_messages s1
            INNER JOIN (SELECT tid,MAX(s2.reply_time)
                                  FROM jpv59_obhelpdesk3_messages
                                 GROUP BY tid) s2
                     ON s1.tid = s2.tid
             INNER JOIN tabla2 s3 ON s1.???=s3.???;
 

No se deduce de los nombres de campos cual es el campo comun entre los dos conjuntos de datos, cambia ??? por el nombre del campo comun, o de los campos equivalentes, ON s1.???=s3.???; por ON s1.tid=s3.id; ?

(Recuerda que los campos se separan por coma, y que * no es una buena practica es mejor pedir exclusivamente los campos que necesites)

[zubintro]

Muchas gracias quimfv por tomarte tu tiempo en responder y analizar el asunto. El script que me pasas me dio error porque no encontraba la columna s2.reply_time de la linea 3 pero me abrio el panorama y me ayudo a generar lo siguiente:

Hice una vista con el nombre PRUEBA con el siguiente codigo

Código SQL:

SELECT *
FROM   jpv59_obhelpdesk3_messages s1
WHERE  reply_time=(SELECT MAX(s2.reply_time)
FROM jpv59_obhelpdesk3_messages s2
WHERE s1.tid = s2.tid);
 

Y luego paso la siguiente consulta a la base de datos para actualizar la tabla

Código MySQL:

UPDATE jpv59_obhelpdesk3_tickets j
JOIN prueba p ON j.id = p.tid
SET diferencia = TIMESTAMPDIFF( MINUTE, j.created, p.reply_time)
WHERE status= 'open' or status= 'on-hold
 

así puedo meter en la columna "diferencia" la cantidad de minutos entre una fecha y la otra realizándolo de forma correcta.

Me comentan que las vistas consume muchísima memoria, por lo que me gustaría saber si es cierto ademas de si a alguien se le ocurre una forma mas eficiente de hacerlo en una sola consulta.

Gracias

[quimfv]

Ese select en el WHERE que pones es muy muy ineficiente, usa la que te paso ya te he corregido el error, podias haberlo visto tu mismo.

Código MySQL:

SELECT s1.*,
            s3.date_create
 
////aqui puedes usar las datetime functions para operar con los campos de todas las tablas     
 
FROM   jpv59_obhelpdesk3_messages s1
            INNER JOIN (SELECT tid,MAX(reply_time)
                                  FROM jpv59_obhelpdesk3_messages
                                 GROUP BY tid) s2
                     ON s1.tid = s2.tid
             INNER JOIN tabla2 s3 ON s1.???=s3.???;
 

En cuanto a lo que preguntas de las vistas, es falso. Pero tienen una desventaja no se puede forzar el uso de indices, USE INDEX(), con lo que en algunos casos es mejor usar una query y poder pedir el uso de determinados indices.

Por otro lado, ese campo diferencia es un campo calculado, por lo tanto es un error crearlo, imagina que hay un cambio en los campos que sirven para calcularlo y no se actualiza el campo diferencia, la bbdd quedaria incoherente.

Crea una vista que de ese valor, diferencia, de forma dinámica.

Código MySQL:

CREATE VIEW laDiferencia 
SELECT s1.*,
            s3.date_create,
            TIMESTAMPDIFF( MINUTE, s3.date_create, s2.reply_time) diferencia
FROM   jpv59_obhelpdesk3_messages s1
            INNER JOIN (SELECT tid,MAX(reply_time)
                                  FROM jpv59_obhelpdesk3_messages
                                 GROUP BY tid) s2
                     ON s1.tid = s2.tid
             INNER JOIN tabla2 s3 ON s1.tid=s3.id;
 

(No hagas corta y pega, analiza lo que se te pasa, es la manera de aprender. Ademas como puedes suponer el codigo esta hecho al vuelo, peude contener errores tontos de sintaxis, si lo analizas y lo entiendes podras corregirlos, por ejemplo tabla2 tampoco la va a encontrar, pero es que no se como se llaman todas tus tablas.....)