Calcular horas trabajadas nocturnas MySQL

[zerts]

Iniciado por jurena

Zerts, a mí no me ofrece los resultados esperados:
23:00:00 04:00:00 05:00:00 2009-06-23 debería dar 05:00:00
23:00:00 02:00:00 03:00:00 2009-06-16 debería dar 03:00:00
19:00:00 22:00:00 03:00:00 2009-06-17 debería dar 03:00:00
Haz una prueba y verás que da 00:00:00 en las dos primeras y la última ni la tiene en cuenta.
Comprueba los resultados con la consulta que te propuse. Sólo tienes que cambiarle el nombre de la tabla al de HorasTrab

Muchas gracias por revisar la consulta y si tienes razón, creo que ahora si

Código sql:

SELECT IdHora, SEC_TO_TIME( SUM( HT ) ) HorasTrabajadas
FROM (
SELECT IdHora, TIME_TO_SEC( SUBTIME( '06:00', HorIni ) ) HT
FROM HorasTrab
WHERE (
HorIni < '06:00'
AND HorFin >= '06:00'
AND HorIni < HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( SUBTIME( HorFin, '18:00' ) ) HT
FROM HorasTrab
WHERE (
HorIni < '18:00'
AND HorFin > '18:00'
AND HorIni < HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( SUBTIME( '06:00', HorFin ) ) HT
FROM HorasTrab
WHERE (
HorIni >'06:00'
AND HorFin <= '06:00'
AND HorIni < HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( ADDTIME( ADDTIME( SUBTIME( '24:00', HorIni ) , '06:00' ) , SUBTIME( HorFin, '18:00' ) ) ) HT
FROM HorasTrab
WHERE (
HorIni > '18:00'
AND HorFin >= '18:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( ADDTIME( SUBTIME( '06:00', HorIni ) , ADDTIME( HorFin, '06:00' ) ) ) HT
FROM HorasTrab
WHERE (
HorIni < '06:00'
AND HorFin <= '06:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( ADDTIME('06:00',HorFin  ) ) HT
FROM HorasTrab
WHERE (
HorIni > '06:00'
AND HorFin <='06:00'
AND HorIni < '18:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( ADDTIME('06:00',SUBTIME( '24:00', HorIni )  ) ) HT
FROM HorasTrab
WHERE (
 HorFin >='06:00'
AND HorIni > '18:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( ADDTIME('06:00',SUBTIME( '24:00', HorIni )  ) ) HT
FROM HorasTrab
WHERE (
 HorFin >='06:00'
AND HorIni > '18:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC(  ADDTIME( SUBTIME( '24:00', HorIni ) , HorFin ) ) HT
FROM HorasTrab
WHERE (
HorIni >= '18:00'
AND HorFin <= '06:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( SUBTIME(HorFin ,HorIni ) ) HT
FROM HorasTrab
WHERE (
HorIni >= '18:00'
AND HorFin <='24:00'
AND HorIni < HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( SUBTIME(HorFin ,HorIni ) ) HT
FROM HorasTrab
WHERE (
HorIni >= '00:00'
AND HorFin <='06:00'
AND HorIni < HorFin
)
UNION SELECT IdHora, TIME_TO_SEC('12:00') HT
FROM HorasTrab
WHERE (
HorIni < '18:00' AND HorIni > '06:00'
AND HorFin <= '18:00' AND HorFin >'06:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC('00:00') HT
FROM HorasTrab
WHERE (
HorIni >= '06:00'
AND HorFin <= '18:00'
OR HorIni=HorFin
)
)T1
GROUP BY IdHora