[SOLUCIONADO] TIMEDIFF = NULL -> incorrecto

[7sistemas]

Buenas gente. Tengo la siguiente consulta:

Código SQL:

SELECT personal_asistencia.id_personal AS ID, concat(nombre, ' ', apellido) AS Nombre, 
time_format(SUM(timediff(salida, entrada)), '%h:%i') AS 'Horas totales', 
precio_hora AS 'Precio/Hora', 
round((SUM(timediff(MINUTE, entrada, salida)) * (precio_hora / 60)), 2) AS Pago 
FROM personal_asistencia 
INNER JOIN personal ON personal.id = personal_asistencia.id_personal 
INNER JOIN personal_precio_hora ON personal.id = personal_precio_hora.id_personal 
WHERE entrada >= '2018-02-01' AND salida <= '2018-02-28' AND personal_asistencia.id_personal = 33
 

que devuelve el siguiente resultado:

Código SQL:

+----+-----------------+---------------+-------------+---------+
| ID | Nombre          | Horas totales | Precio/Hora | Pago    |
+----+-----------------+---------------+-------------+---------+
| 33 | Nombre Apellido | NULL          |         100 | 1010.00 |
+----+-----------------+---------------+-------------+---------+
1 ROW IN SET, 1 warning (0.00 sec)
 

[/CODE]

El warning es el siguiente:

Código SQL:

+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect TIME VALUE: '96600.000000' |
+---------+------+------------------------------------------------+
 

El valor (NULL) de horas totales debería ser 10:06. (Cambié el nombre de la persona porque es una persona real).

De acá saca la info:

Código SQL:

+----+-------------+---------------------+---------------------+-----------+-------------+
| id | id_personal | entrada             | salida              | id_estado | comentarios |
+----+-------------+---------------------+---------------------+-----------+-------------+
|  1 |          33 | 2018-02-03 13:18:00 | 2018-02-03 20:06:00 |      NULL |             |
|  3 |          33 | 2018-02-03 20:09:00 | 2018-02-03 20:15:00 |      NULL |             |
| 13 |          33 | 2018-02-11 08:34:00 | 2018-02-11 11:46:00 |      NULL |             |
+----+-------------+---------------------+---------------------+-----------+-------------+
 

Sin embargo, cuando ejecuto la misma consulta para otro usuario, el 27, obtengo un resultado que no es correcto (debería ser 61:15) pero sin warnings. Aún así, en PHP obtengo el resultado correcto.

Código SQL:

+----+------------------------+---------------+-------------+---------+
| ID | Nombre                 | Horas totales | Precio/Hora | Pago    |
+----+------------------------+---------------+-------------+---------+
| 27 | Nombre Apellido        | 01:15         |        75.6 | 4630.50 |
+----+------------------------+---------------+-------------+---------+
 

Gracias de antemano.

[gnzsoloyo]

SI entiendo bien, el tema pasa porque estás calculando la sumatoria de horas de cierto evento administrativo, como por ejemplo las horas trabajandas, y pretendes darle un formato de horas usando un tipo de dato TIME (TIMEDIFF devuelve un TIME). Pero el tipo de dato TIME sólo acepta 24:00 hs, nunca mas. De hecho, no existe BBDD que pueda aceptar mas de 24 hs... Porque eso como tipo de dato HORAS no existe.
Silo que quieres es obtener el tiempo total acumulado, necesitas hacerlo de otra forma:
1) Sumas los segundos totales con una función como TIMESTAMDIFF(unidad, inicio, fin).
2) Creas una funcion almacenada para que tome un valor numérico entero positivo y lo transforme en una cadena de texto con el formato HHH:MM.

O bien directamente haces el cálculo en el codebehind de la aplicación (capa de negocio o controladores), donde algunos lenguajes pueden hacer la tarea de igual forma...

Fuera de eso, si la sumatoria de tiempos en segundos es incorrecta, es posible que tengas un error en la definición de las relaciones, por estar relacionándose registros que no están vinculados, o bien le falta un correcto GROUP BY.

[7sistemas]

Iniciado por gnzsoloyo

Pero el tipo de dato TIME sólo acepta 24:00 hs, nunca mas.

El tipo TIME admite valores desde -838:59:59 hasta 838:59:59. Lo dijiste tan convencido que volví a mirar la documentación de MySQL.

Iniciado por gnzsoloyo

o bien le falta un correcto GROUP BY.

La cláusula GROUP BY para esta consulta es correcta.

Iniciado por gnzsoloyo

O bien directamente haces el cálculo en el codebehind de la aplicación

¿Por qué ponerse a hacer operaciones con fecha y hora en la aplicación si la base de datos lo puede hacer mas rápido?

Finalmente lo solucioné. Gracias igual porque me di cuenta de que estaba usando funciones equivocadas.
Cambié esto:

Código:

date_format(sum(timestampdiff(hour, salida, entrada)), '%H:%i:%s') as 'Horas totales'

por esto:

Código:

time_format(sec_to_time(sum(timestampdiff(second, entrada, salida))), "%H:%i") as 'Horas totales',

Saludos!