diferencia de tiempo entre una fila y la anterior

[saba01]

Hola,

hace días que me peleo con una consulta y pese ha haber encontrado muchos ejemplos no acabo de adaptarlos a mi código.

la consulta es esta

Código SQL:

SELECT
        id,
        date_format(DATA, '%d/%m/%Y %H:%i') AS data1,
        temperatura,
        TIMEDIFF((SELECT DATA FROM bancal1 WHERE id = '1277'),(SELECT DATA FROM bancal1 WHERE id = 1276)) AS dife
        FROM bancal1 WHERE DATA > CURDATE() ORDER BY DATA DESC
 

quisiera cambiar los valores fijos 1277 y 1276 por id y id-1 para que cada linea me de la diferencia de tiempo con la anterior.

la descripción de la tabla

Código MySQL:

mysql> desc bancal1;
+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL              | auto_increment |
| data        | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| temperatura | decimal(5,3) | YES  |     | NULL              |                |
+-------------+--------------+------+-----+-------------------+----------------+
 

y la tabla

Código MySQL:

+----+---------------------+-------------+
| id | data                | temperatura |
+----+---------------------+-------------+
|  1 | 2014-01-17 12:08:21 |      22.400 |
|  2 | 2014-01-17 16:39:32 |      15.100 |
|  3 | 2014-01-17 16:42:34 |      15.300 |
|  4 | 2014-01-17 16:45:36 |      15.000 |
|  5 | 2014-01-17 16:51:39 |      14.600 |
|  6 | 2014-01-17 16:54:41 |      14.200 |
 

Editado:Código de programación no permitido den foroes de BBDD. Leer las normas del foro por favor.

[Djoaq]

Iniciado por saba01

quisiera cambiar los valores fijos 1277 y 1276 por id y id-1 para que cada linea me de la diferencia de tiempo con la anterior.

No entiendo bien que quieres decir!

Podrías explicar...

Saludos!

[leonardo_josue]

Hola Saba01:

al igual que Djoaq, tengo algunos problemas para entender qué es lo que estás tratando de obtener... creo que deberías de haber comenzado por decirnos qué es lo que esperas como resultado, de esta manera podríamos proponerte una solución distinta a lo que tienes...

Según lo que creo, lo que pretendes hacer es comparar cada registro con su registro anterior (es decir, el 2 contra el 1, el 3 contra el 2, el 4 contra el 3 y así sucesivamente)... en el caso del id = 1, este no lo podrías comparar contra ninguno, pues es el primero de la lista. Si estoy en lo correcto, esto lo puedes hacer de muchas maneras, una de las más simples sería con un LEFT JOIN, es decir, algo como esto:

Código MySQL:

mysql> SELECT * FROM bancal1;
+------+---------------------+-------------+
| id   | fecha               | temperatura |
+------+---------------------+-------------+
|    1 | 2014-01-17 12:08:21 |      22.400 |
|    2 | 2014-01-17 16:39:32 |      15.100 |
|    3 | 2014-01-17 16:42:34 |      15.300 |
|    4 | 2014-01-17 16:45:36 |      15.000 |
|    5 | 2014-01-17 16:51:39 |      14.600 |
|    6 | 2014-01-17 16:54:41 |      14.200 |
+------+---------------------+-------------+
6 rows in set (0.00 sec)
 
mysql> SELECT * FROM bancal1 T1
    -> LEFT JOIN bancal1 T2 ON T1.id - 1 = T2.id
    -> ORDER BY T1.id;
+------+---------------------+-------------+------+---------------------+-------------+
| id   | fecha               | temperatura | id   | fecha               | temperatura |
+------+---------------------+-------------+------+---------------------+-------------+
|    1 | 2014-01-17 12:08:21 |      22.400 | NULL | NULL                |        NULL |
|    2 | 2014-01-17 16:39:32 |      15.100 |    1 | 2014-01-17 12:08:21 |      22.400 |
|    3 | 2014-01-17 16:42:34 |      15.300 |    2 | 2014-01-17 16:39:32 |      15.100 |
|    4 | 2014-01-17 16:45:36 |      15.000 |    3 | 2014-01-17 16:42:34 |      15.300 |
|    5 | 2014-01-17 16:51:39 |      14.600 |    4 | 2014-01-17 16:45:36 |      15.000 |
|    6 | 2014-01-17 16:54:41 |      14.200 |    5 | 2014-01-17 16:51:39 |      14.600 |
+------+---------------------+-------------+------+---------------------+-------------+
6 rows in set (0.00 sec)
 

Una vez con esto tú puedes hacer los cálculos que deseas (según lo que pones en tu consulta, creo que lo que quieres obtener es el tiempo transcurrido desde la última medición), si esto es correcto entonces simplemente harías lo siguiente:

Código MySQL:

mysql> SELECT T1.*, TIMEDIFF(T1.fecha, t2.fecha) diferencia
    -> FROM bancal1 T1
    -> LEFT JOIN bancal1 T2 ON T1.id - 1 = T2.id
    -> ORDER BY T1.id;
+------+---------------------+-------------+------------+
| id   | fecha               | temperatura | diferencia |
+------+---------------------+-------------+------------+
|    1 | 2014-01-17 12:08:21 |      22.400 | NULL       |
|    2 | 2014-01-17 16:39:32 |      15.100 | 04:31:11   |
|    3 | 2014-01-17 16:42:34 |      15.300 | 00:03:02   |
|    4 | 2014-01-17 16:45:36 |      15.000 | 00:03:02   |
|    5 | 2014-01-17 16:51:39 |      14.600 | 00:06:03   |
|    6 | 2014-01-17 16:54:41 |      14.200 | 00:03:02   |
+------+---------------------+-------------+------------+
6 rows in set (0.00 sec)
 

Dale un vistazo para ver si es lo que necesitas.

Saludos
Leo

[jurena]

Solo veo un problema, que los identificadores no permitan esa consulta para todos los registros. Basta con que se haya borrado algún registro y falte un número en la secuencia. Eso producirá datos erróneos. Y si fuera el caso, habría que ver el modo de añadir numeraciones a todos los registros mediante variables y hacerlo dos veces sobre la misma tabla, una empezando una numeración desde cero y otra desde uno. Luego haríamos el mismo tiempo de LEFT JOIN ... ON... que nos ha propuesto Leonardo.

saludos